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

Uitgangspunt

De kosten per project worden als volgt 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 daardoor verloren: het is dan niet meer mogelijk om cel B1 te variëren als startpunt voor de daaropvolgende perioden.

Werken met dynamische bereiknamen

U 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 voert u in: DataSet

Bij het onderdeel Verwijst naar voert u de volgende formule in:

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

Daarna klikt u op de OK-knop.

Nu kunt u de bereiknaam controleren door uit het menu te kiezen: Formules > Gedefinieerde namen > Namen beheren, waarop het volgende dialoogvenster verschijnt.

Klik met de muis op de formule in het onderdeel Verwijst naar. U zult zien dat de dataset wordt omkaderd door een gestippelde rechthoek ten teken dat de dataset betrekking heeft op het gemarkeerde bereik.

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

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

• Start-cel: dit is de cel linksboven in de dataset
• Aantal rijen: geeft aan hoeveel rijen u 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 u 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 telt u het aantal cellen dat gevuld is in kolom A.
• Breedte: geeft aan hoe breed de dataset is. Met behulp van de functie AANTALARG telt u het aantal cellen dat gevuld is in rij 1.

Transformeren met Power Query

Nu gaat u Power Query aan het werk zetten om de dataset te transformeren. Daartoe kiest u uit het menu: Gegevens > Gegevens ophalen en transformeren > Gegevens ophalen > Uit andere bronnen > Lege query.

Vervolgens belandt u in de Power Query-omgeving. In de formulebalk voert u 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 dubbele pijl bij Context. Deselecteer de laatste optie (Oorspronkelijke kolomnaam gebruiken als voorvoegsel) en klik op OK.

Vervolgens ziet u het volgende venster:

Daarna doorloopt u 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 kiest u Verwijderen.
• Selecteer de kolom Project en klik met de rechtermuisknop. Uit het verkorte menu kiest u: 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 kiest u: 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 kunt u dan gebruiken voor verdere analyse of voor het maken van een draaitabel.

Auteur: Tony De Jonker

Dit artikel is verschenen in cm: 2022, afl. 6.

Hier leest u meer Excel tips en trucs door Tony De Jonker

Volg CMWeb op LinkedIn!