Hoe je met Power Query transformaties uitvoert met behulp van dynamische bereiknamen

Power Query is een krachtig hulpmiddel om transformaties uit te voeren. Als je binnen een werkmap de betreffende datasets wenst te transformeren, dienen ze opgemaakt te zijn als Excel-tabellen. Hieraan kleven enige bezwaren. In dit artikel zie je hoe je deze hindernis kunt oplossen.

Uitgangspunt

We hebben de volgende indeling waarin de kosten per project worden vastgelegd. 

Cel B1 is de ankercel waarin de startdatum wordt ingevoerd.  De cellen C1 tot en met M1 bevatten een formule die gebaseerd is op de voorgaande periode. Markeer daartoe C1 tot en M1 en geef de volgende formule in: =DATUM(JAAR(B1);MAAND(B1)+1;1) gevolgd door Control-Enter. 

De dataset kan naderhand uitgebreid worden met nieuwe projecten en nieuwe perioden. Als we de dataset met Power Query willen bewerken, doet zich het verschijnsel voor dat Excel de dataset automatisch omzet in een Excel-tabel.  Als gevolg hiervan worden de formules in de kopteksten omgezet naar waarden en de datums worden geconverteerd naar teksten. De ingebouwde dynamiek gaat doordoor verloren: het is dan niet meer mogelijk om cel B1 te variëren als startpunt voor de daaropvolgende perioden. 

Werken met dynamische bereiknamen 

Je kunt een dynamische bereiknaam maken met behulp van de functie VERSCHUIVING. Kies daartoe uit het menu de optie: Formules à Gedefinieerde namen à Naam definiëren. Het volgende dialoogvenster verschijnt: 

Als Naam voer je in: DataSet 

Bij het onderdeel Verwijst naar voer je de volgende formule in:  

=VERSCHUIVING(Blad1!$A$1;0;0;AANTALARG(Blad1!$A:$A);AANTALARG(Blad1!$1:$1)) 

Daarna klik je op de OK-knop.  

Nu kun je de bereiknaam controleren door uit het menu te kiezen: Formules à Gedefinieerde namen à Namen beheren, waarop het volgende dialoogvenster verschijnt. Klik met de muis in op de formule in het onderdeel Verwijst naar. Je zult zien dat de dataset wordt omkaderd door een gestippelde rechthoek ten teken dat de dataset betrekking heeft op het gemarkeerde bereik. 

Als je nieuwe projecten en/of nieuwe perioden gaat toevoegen, zul je zien dat via deze optie het bereik automatisch wordt uitgebreid. 

De VERSCHUIVING-functie levert een dynamisch bereik op en bestaat uit de volgende argumenten: 

• Start-cel: dit is de cel linksboven in de dataset 

• Aantal rijen: geeft aan hoeveel rijen je naar beneden (positief getal) of naar boven (negatief getal) wenst te gaan. In ons geval blijven we in dezelfde cel. De waarde is daarom 0. 

• Aantal kolommen: geeft aan hoeveel kolommen je naar rechts (positief getal) of naar links (negatief getal) wenst te gaan. In ons geval blijven we in dezelfde cel. De waarde is daarom 0. 

• Hoogte: geeft aan hoe hoog de dataset is. Met behulp van de functie AANTALARG tel je het aantal cellen dat gevuld is in kolom A. 

• Breedte: geeft aan hoe breed de dataset is. Met behulp van de functie AANTALARG tel je het aantal cellen dat gevuld is in rij 1. 

Transformeren met Power Query 

Nu ga je Power Query aan het werk zetten om de dataset te transformeren. Daartoe kies je uit het menu: Gegevens à Gegevens ophalen en transformeren à Gegevens ophalen à Uit andere bronnen à Lege query. 

Vervolgens beland je in de Power Query omgeving. In de formulebalk voer je de volgende formule in: = Excel.CurrentWorkbook()  gevolgd door Enter. 

Let op: de formule is tekstgevoelig en dient met hoofdletters en kleine letters te worden ingevoerd. 

Vervolgens ziet het venster er als volgt uit: 

Zodra u met de muis op Table klikt, ziet u onderaan het venster de indeling van de dataset. 

Power Query herkent niet automatisch de koptekst. Vandaar dat bovenaan Column1 tot en met Column12 wordt weergegeven. 

Klik op de dubbel pijl bij Context. Daarop wordt het volgende venster vertoond: 

Deselecteer de laatste optie (Oorspronkelijke kolomnaam gebruiken als voorvoegsel) en klik op OK. 

Vervolgens zie je het volgende venster: 

Daarna doorloop je de volgende transformaties: 

• Kies uit het menu: Start à Transformeren à De eerste rij als veldnamen gebruiken. 

• Selecteer de laatste kolom genaamd Dataset, klik op rechtermuisknop en uit het verkorte menu kies je Verwijderen. 

• Selecteer de kolom Project en klik met de rechtermuisknop. Uit het verkorte menu kies je: Draaitabel opheffen voor andere kolommen. 

• Klik op de filter in de kolom Kenmerk en deselecteer Dataset. 

• Dubbelklik op de koptekst Kenmerk en verander deze in Datum. 

• Markeer de kolom Datum en uit het menu kies je: Transformeren à Kolom Datum en tijd à Datum à Parseren. 

• Dubbelklik op de koptekst Waarde en verander deze in Kosten. 

• Kies uit het menu: Start à Sluiten en laden à Sluiten en laden, waarop Excel de volgende tabel aanmaakt. 

Deze getransformeerde tabel kun je dan gebruiken voor verder analyse of voor het maken van een draaitabel. 

Foto: ANP/ Hollandse Hoogte/ Helene van Rijn.

Lees ook: Hoe je makkelijker kunt zoeken in Excel, dankzij een nieuwe functionaliteit

Analyseer, presenteer en rapporteer efficiënter en effectiever met Excel

Werk je regelmatig met Excel? Ben je daarbij veel energie kwijt aan het opstellen van analyses en presentaties? Ontdek in de cursus Excel voor Financials mogelijkheden die jou direct veel voordeel opleveren. Win tijd, verbeter je analyses en presenteer effectiever!

Meer informatie | Meld je direct aan