Bij middelgrote en grote organisaties wordt de headcount in aparte systemen bijgehouden. Daartoe worden de gegevens veelal in een bepaalde structuur vastgelegd, die getransformeerd dient te worden in een tabel op basis waarvan draaitabellen en -grafieken kunnen worden afgeleid. Met behulp van Power Query kunt u deze stappen vastleggen en bij vernieuwen van de gegevensbron wordt de draaitabel en -grafiek automatisch bijgewerkt. Een voorbeeld.

Uitgangspunt

Het bronbestand is een Excel-bestand bestaande uit 3 bladen, te weten 2020, 2021, en 2022 met de volgende structuur:

De eerste 3 rijen worden gebruikt om de totalen (fte en employees) van alle filialen te sommeren. Vanaf rij 8 en verder ziet u de opstelling per filiaal en per maand.

Het doel is nu een allesomvattende tabel te maken van alle jaren/maanden met als indeling:

• StoreNbr
• Store
• Date
• FTE
• Employees

Transformeren met Power Query

Voer de volgende stappen uit:

• Vanuit het Lint kiest u: Gegevens > Gegevens ophalen en transformeren > Gegevens ophalen > Uit bestand > Uit Excel-werkmap (Data > Get Data & Transform > Get Data > From File > From Workbook). U selecteert het gewenste bestand en klikt op Importeren (Load).
• Uit het dialoogvenster kiest u item 2020 en klikt u op Gegevens transformeren (Launch Query Editor).
• U belandt nu in de Power Query-omgeving. Aan de rechterkant heeft Power Query een aantal transformatiestappen uitgevoerd.

• U verwijdert de laatste drie stappen, zodat alleen nog de stap Bron overblijft.  Het beeldscherm ziet er als volgt uit:

• U klikt in de kolom Data op de dubbele pijl. Uit het dialoogvenster deselecteert u de laatste optie: Oorspronkelijke kolomnaam gebruiken als voorvoegsel. Daarna klikt u op de OK-knop.
• De tabel wordt nu uitgevouwen en bevat alle gegevens uit de drie tabellen (2020-2021-2022).
• U verwijdert de eerste 5 rijen via het Lint: Start > Minder rijen > Rijen verwijderen > Bovenste rijen verwijderen > Aantal rijen: 5 > OK (Home > Reduce Rows > Remove Rows > Remove Top Rows > Number of rows: 5 > OK).
• U kiest uit het Lint: Start > Transformeren > De eerste rij als veldnamen gebruiken (Home > Transform > Use Top Row As Headers).
• Selecteer de header Store en door op de filter te klikken kunt u de lege waarden deselecteren.
• Daarna gaat u de veldnamen aanpassen, zodat u de volgende namen verkrijgt: Jaar, WinkelNr, Winkelnaam, FTE-1, EMP-1, FTE-2, EMP-2, FTE-3, EMP-3, FTE-4, EMP-4, FTE-5, EMP-5, FTE-6, EMP-6, FTE-7, EMP-7, FTE-8, EMP-8, FTE-9, EMP-9, FTE-10, EMP-10, FTE-11, EMP-11, FTE-12, EMP-12. De cijfers verwijzen naar het maandnummer.
• U markeert de kolommen Jaar-WinkelNr en Winkelnaam en klikt daarna met de rechtermuisknop. Uit het verkorte menu kiest u: Draaitabel opheffen voor andere kolommen. Het resultaat leidt tot een gekantelde tabel.

• Klik op de kolom Kenmerk en uit het Lint kiest u: Start > Transformeren > Kolom splitsen > Op scheidingsteken > Aangepast > – > Splitsen op > Het meest linkse scheidingsteken > OK (Home > Split Columns > By Delimiter > Custom > _ > Split By > Left-most delimiter > OK).

• De kolom Kenmerk is nu gesplitst in twee kolommen. De eerste kolom kunt u herbenoemen in Dataset en de tweede kolom in Maand.
• De kolom Waarde verandert u in Aantal en u geeft als opmaak decimaal op.
• U voegt een kolom toe via: Kolom toevoegen > Algemeen > Aangepaste kolom > Nieuwe kolomnaam > Datum > Aangepaste kolomformule > #date([Jaar],[Maand],1) > OK. (Add Column > Custom Column > New Name > “Datum” > Custom Column Formula > #date([Jaar],[Maand],1) > OK).

• Als laatste transformatie verwijdert u de kolom Maand.

Ten slotte sluit u de transformaties af via Start > Sluiten > Sluiten en laden > Sluiten en laden naar > Alleen verbinding maken > OK (Home > Close & Load > Close & Load To > Only Create Connection).

Een draaitabel en draaigrafiek maken

Klik met de rechtermuisknop op de Query in het Query-paneel en uit het verkorte menu kiest u: Laden naar > Draaitabelrapport > Nieuw blad > OK (Load To > PivotTable report > New Sheet > OK).

Maak de volgende indeling:

In combinatie met een draaigrafiek ziet het rapport er als volgt uit:

Zodra de brongegevens zijn aangepast, kunt u het rapport bijwerken door uit het Lint te kiezen: Gegevens > Query’s en verbindingen > Alles vernieuwen (Data > Queries & Connections > Refresh All).

Auteur: Tony De Jonker

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

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

Volg CMWeb op LinkedIn!