Pivot, pivot, pivot, of hoe je Excel-bestanden temt

Veel organisaties kopen een duur rapportageplatform en verwachten dat elke berekening in Excel daarmee verdwijnt. Dat gaat niet gebeuren. Excel zit in onze werkcultuur, en een cultuur verander je niet door mensen een andere tool op te leggen.

Wat wel kan, is toegroeien naar een situatie waarin je het platform vaker gebruikt dan het rekenblad. Power BI maakt die stap kleiner dan de meeste alternatieven, en ik vermoed dat het uiterlijk daar iets mee te maken heeft. Het lijkt genoeg op Excel om niet te schrikken.

De situatie

Op projecten waar de bron een Excel-bestand is, komt dat bestand vaak van buiten. Vraag je een derde partij om de structuur aan te passen, dan wil die dat niet doen voor één klant, of het kost geld. Dus doe je het met wat je krijgt.

Voor dit stuk deed ik alsof ik niets aan het bestand mocht wijzigen, en probeerde ik alles dynamisch te maken in Power Query. De data ging over inschrijvingen van personenwagens, nieuw en tweedehands, per jaar, uitgesplitst per regio en brandstoftype.

Kijk eerst naar het bestand

Dat is altijd de eerste stap. In dit geval een werkboek met meerdere tabbladen, waarvan er één een handgemaakte matrix bevatte. De kopregels stonden in de bovenste rijen, de indeling zat zowel in de rijen als in de kolommen, en die kolommen droegen twee dingen tegelijk: het jaar en het verkooptype.

Wat ik eruit wou halen, was een platte tabel met vijf kolommen:

Regio | Brandstof | Verkooptype | Jaar | Waarde

Benoem je hindernissen voor je begint

• Geen echte kopregels, dus er moeten bovenaan een aantal rijen weg voor de tabel bruikbaar wordt.
• Lege waarden overal, want kenmerken worden niet herhaald per rij. Daar is de opvulfunctie voor.
• Twee kenmerken in de kopregels, jaar en verkooptype tegelijk. Dat vraagt een omweg.

Door die drie eerst op te schrijven, weet je vooraf of Power Query het aankan. Dat scheelt een halve dag proberen.

De bewerkingen

Lege rijen eruit. Via Rijen verwijderen op het tabblad Start.

Naar beneden opvullen. Zonder dat kan je geen enkele waarde aan een regio, jaar of brandstoftype koppelen. De functie staat op het tabblad Transformeren, onder Willekeurige kolom.

Transponeren. Een deel van de lege waarden hing aan een kenmerk in de kopregels en niet in de rijen. Daarom draaide ik de tabel een kwartslag, vulde opnieuw naar beneden op, en draaide terug. Dezelfde vorm als bij de start, alleen nu volledig ingevuld.

Twee kenmerken samen in één sleutel. Hier zit de kern. Ik wou ontpivoteren op regio én brandstof samen, en dat kan Power Query niet op twee kenmerken tegelijk. Dus plakte ik ze aan elkaar met een scheidingsteken dat verder nergens voorkomt:

= Table.AddColumn(#"Transposed Table1", "Key Column", each [Column1]&"|"&[Column2])

Daarna transponeren, de tabel omkeren omdat de labels onderaan stonden, de kopregels promoveren zodat elke kop een combinatie van regio en brandstof werd, en ontpivoteren op verkooptype en jaar. Dat levert één rij per verkooptype, jaar, regio en brandstof.

Opruimen. De samengeplakte kolom terug splitsen op datzelfde teken, en de overtollige waarden uit het brandstofveld halen:

= Table.TransformColumns(#"Changed Type2", {{"Attribute.2", each Text.BeforeDelimiter(_, "_"), type text}})

Elke bewerking die je hier zet, blijft werken bij het volgende bestand. Dat is het hele punt.

Wat je eroverheen kan leggen

Met die platte tabel is bouwen een kwestie van slepen. Voor deze data werkten een gestapelde staaf op honderd procent, een lijndiagram over de jaren, en een decompositieboom om per regio af te dalen.

Schrijf een Excel-bron dus niet af omdat ze rommelig oogt. Zorg wel dat elke bewerking dynamisch blijft, zodat de volgende levering van dezelfde partij er zonder handwerk doorloopt. Moet je bij elke nieuwe versie opnieuw klikken, dan heb je het rekenblad enkel verplaatst.

Power BI●Power Query●Excel