Met behulp van de Power Query-editor in Excel kunt u snel rapporten bouwen. Hier vindt u een voorbeeld uit de praktijk hoe u Power Query kunt inzetten om een datamodel te structureren voor rapportages.
Uitgangspunt
We hebben de volgende kruistabel:
Met Power Query kunt u deze tabel omzetten naar een databasestructuur. Kies uit het lint Gegevens en in het vakje Gegevens ophalen en transformeren voor Van tabel/bereik. (Data > Get & Transform Data > From Table/Range.)Dat opent een nieuw venster met de Power Query-editor. Dan markeert u met de muis en de Shift-toets de kolommen jan tot en met dec. Kies dan Transformeren >Draaitabel opheffen voor kolommen (Transform > Columns > Unpivot columns) bij Alle kolommen. Het venster ziet er dan als volgt uit:
Selecteer dan Bestand > Sluiten en laden (Home > Close & Load). De geconverteerde cijfers komen nu in Excel.
U kunt cel C1 – Attribute vervangen door maand en cel D1 – Value door bedrag.
Gegevens bewerken met een draaitabel
De cijfers kunt u via Invoegen > Draaitabel (Insert > PivotTable) verder bewerken. Selecteer Element en bedrag. U kunt de regels verplaatsen zodat de elementen in een logische volgorde verschijnen.
Dan kunt u rijlabels vervangen door omschrijving en Som van bedrag door Euro.
U verwijdert het Eindtotaal via Hulpmiddelen voor Draaitabellen > Ontwerpen> Eindtotalen > Uitgeschakeld voor rijen en kolommen (PivotTable Tools > Design > Grand Totals >Off for Rows and Columns).
We kunnen vervolgens een subtotaal maken voor het berekenen van de brutomarge. Plaats de cursor op de header Rijlabels (omschrijving) van de draaitabel en selecteer Hulpmiddelen voor Draaitabellen > Analyseren > Berekenen > Velden, items en sets > Berekend item (PivotTable Tools > Analyze > Calculations > Fields, Items, & Sets > Calculated Field). Vul het dialoogvenster als volgt in:
Klik op Toevoegen (Add) en OK. U zult zien dat het item Brutomarge in de draaitabel onderaan verschijnt. Deze verplaatst u naar boven zodat deze gepositioneerd wordt na het item Kostprijs verkopen.
Op soortgelijke wijze kunnen we het item Resultaat toevoegen. Het berekend Item Resultaat bevat de volgende formule: =Brutomarge -Verkoopkosten -’Admin kosten’ -’Fin kosten’ -Belastingen. Na opmaak van getallen en het toevoegen van lijnen ziet de resultatenrekening er als volgt uit:
Auteur: Tony de Jonker
Een eerdere versie van dit artikel met een oudere Power Query voor Excel pre-2016 vindt u hier. Bovenstaande artikel is bijgewerkt naar Excel 365, versie april 2021 en voorzien van de juiste labels in de Engelse en Nederlandse versie van het modernere Excel.
Geef een antwoord