In het kader van de begroting en de maandelijkse aanpassing daarvan dienen projectgegevens te worden voorzien van tekstuele toelichting. De vastlegging geschiedt via Excel-tabellen. De brongegevens van de projecten komen uit een ander systeem waarin de toelichting niet wordt bijgehouden. Dat betekent dat er ook nieuwe projecten kunnen worden toegevoegd. De uitdaging is de ingevoerde toelichting te behouden na het inladen van de nieuwe dataset. Hierbij komt nogal wat handwerk kijken als de regels gaan verschuiven door de introductie van nieuwe projecten. Met Power Query kunt u dit probleem oplossen.
De basistabel met projectgegevens kopieert en plakt u in een Excel-tabel.
Via het Lint kiest u Start > Stijlen > Opmaken als tabel (Home > Styles > Format as Table). Daarmee is de dataset nu geconverteerd naar een Excel-tabel. U kunt een naam geven aan de tabel via Tabelontwerp > Eigenschappen > Tabelnaam > “DataProjecten” (Design > Properties Table Name > “dataProjecten”).
Transformeren tabel naar Power Query
U gaat de tabel nu overbrengen naar Power Query. Daartoe plaatst u de cursor ergens in de tabel. U kiest uit het Lint: Gegevens > Gegevens ophalen en transformeren > Van tabel/bereik (Data > Get & Transform Data > From Table/Range). U belandt daarna in de Power Query-omgeving.
U maakt dan een afgeleide dataset die dienst zal doen als invulformulier. Klik daartoe met de rechtermuisknop op de query Dataprojecten aan de linkerkant van het scherm. Uit het verkorte menu kiest u: Verwijzing.
Er ontstaat een kopie van de eerste dataset genaamd DataProjecten (2). Klik dan met de rechtermuisknop op de query Dataprojecten aan de linkerkant van het scherm. Uit het verkorte menu kiest u: Naam wijzigen. Als nieuwe naam vult u in: DataProjectenManueel.
U voegt een nieuwe kolom toe via: Kolom toevoegen > Aangepaste kolom (Add Column > Custom). Daarop verschijnt een dialoogvenster dat u als volgt kunt invoeren, waarna u op OK klikt.
Kies dan uit het Lint: Start > Sluiten en laden > Sluiten en laden naar (Home > Close & Load > Close & Load To…). Uit het dialoogvenster selecteert u: Alleen verbinding maken > OK (Only Create Connection > OK).
Aan de rechterkant ziet u de query’s.
Klik met de rechtermuisknop op de query DataProjectenManueel en uit het verkorte menu kiest u: Laden naar. Uit het dialoogvenster kiest u: Tabel > Nieuw werkblad > OK (Table > New workbook > OK).
Er verschijnt een nieuw werkblad met de volgende indeling:
In de kolom Toelichting kunt u handmatig de diverse toelichtingen invoeren.
Deze tabel laadt u vervolgens weer in Power Query. Daartoe plaatst u de cursor ergens in de tabel. U kiest uit het Lint: Gegevens > Gegevens ophalen en transformeren > Van tabel/bereik (Data > Get & Transform Data > From Table/Range). U belandt daarna in de Power Query-omgeving.
Er ontstaat een kopie van DataProjectenManueel genaamd DataProjectenManueel (2). Klik dan met de rechtermuisknop op de query DataprojectenManueel (2) aan de linkerkant van het scherm. Uit het verkorte menu kiest u: Naam wijzigen. Als nieuwe naam vult u in: DataProjectenIngevuld.
Vervolgens klikt u op de query DataProjecten aan de linkerkant. U gaat nu de kolom Toelichting uit DataProjectenIngevuld samenvoegen met DataProjecten. Kies daartoe uit het Lint: Start > Combineren > Query’s samenvoegen > Query’s samenvoegen (Home > Merge Queries > Merge Queries). Selecteer als tweede tabel DataProjectenIngevuld en klik op de kolommen ProjectID in beide tabellen gevolgd door Enter.
Aan de rechterkant ontstaat een nieuwe kolom DataProjectenIngevuld. Klik op de dubbele pijl, deselecteer alle vinkjes behalve die voor de kolom Toelichting. Klik dan op OK.
De query DataProjecten ziet er nu als volgt uit:
Selecteer nu Query DataProjectenManueel en markeer de kolom Toelichting. Aan de rechterkant verwijdert u de tweede stap.
Kies dan Sluiten en laden > Sluiten en laden naar > Alleen verbinding (Home > Close & Load > Close & Load To… > Only Create Connection > OK).
Nu kunt u in het blad DataProjectenManueel de teksten voor de Toelichting invullen. Zodra de tabel Data wordt gewijzigd, dient u uit het Lint te kiezen: Gegevens > Query’s en verbindingen > Alles vernieuwen (Data > Queries & Connections > Refresh All), waarop de nieuwe gegevens ook worden weergegeven in DataProjectenManueel met behoud van de ingevoerde toelichtingen. Door deze zelfverwijzing in Power Query heeft u dat tot stand gebracht.
cm:Excel
Dé nieuwsbrief voor de financial die meer wil weten over Excel. In de maandelijkse nieuwsbrief staat informatie over nieuwe toepassingen, handigheidjes, tips en trucs en opleidingen.Schrijf u vandaag nog in voor onze gratis Excel nieuwsbrief!
Auteur: Tony De Jonker
Dit artikel is verschenen in cm: 2022, afl. 7.
Hier leest u meer Excel tips en trucs door Tony De Jonker
