Tekstdraaitabel m.b.v. PowerQuery

 Eén van de veel gelezen blogs hier is die over de tekstdraaitabel. Maar met de nieuwe mogelijkheden van PowerQuery is het zelfs mogelijk om zonder hulpkolommen een nog betere draaitabel te maken waar zelfs meerdere waarden met dezelfde rij- en kolomwaarden correct worden vermeld.

Let op! Momenteel, juli 2022, is PowerQuery in de Mac-versie van Excel waarschijnlijk nog niet volledig genoeg om onderstaande te bewerkstelligen. Het kan dus zijn dat dit nu nog alleen geld voor de Windows-versie van Excel.

Sinds de komst van PowerQuery en PowerPivot zijn er veel mogelijkheden aan Excel toegevoegd om grote bestanden te kunnen ophalen, te bewerken en klaar te zetten voor verdere bewerking. 
En het mooie is dat alle stappen die worden gedaan, worden opgeslagen zodat bij nieuwe data alleen maar de gegevens vernieuwd hoeven te worden en Excel zelf alle stappen herhaalt die zijn vastgelegd.

Aan de hand van bovenstaand voorbeeld wordt hieronder uitgelegd hoe je een drie-koloms-tabel met tekst om kunt zetten naar een tekst-draaitabel.

Let op (1)!
Het is geen echte draaitabel zoals met getallen waarbij op de getallen geklikt kan worden om de details op te vragen. Maar het lijkt op een draaitabel.

Let op(2)!
De uitleg gaat over de tabel uit het voorbeeld. Als dit voor andere doeleinden wordt gebruikt, hou er dan rekening mee dat in de Power-Query-code andere titelnamen gebruikt moeten worden.

De werkwijze:

1) Maak een drie-koloms-tabel
Maak een tabel van drie kolommen waarbij de teksten uit de eerste kolom straks in de draaitabel in de eerste kolom komen te staan en de teksten uit de tweede kolom als eerste rij boven de kolommen komt te staan. Vul in de derde kolom de tekst die straks op de kruising moet komen te staan van de rijen en kolommen.

2) Maak van de gegevens een tabel
PowerQuery werkt niet met losse bereiken maar met tabellen. Selecteer een willekeurige ingevulde cel van de drie-koloms-tabel en kies uit het Invoegen-lint voor Tabel.

Er wordt een tabel aangemaakt en als een cel in de tabel is geselecteerd, dan zal er een extra lint met de naam "Tabelontwerp" zichtbaar zijn. Als deze actief is, is direct ook te zien dat de tabel een naam heeft gekregen.

Klik in het Gegevens-lint op de button Van tabel/bereik

Hiermee wordt het PowerQueryscherm geopend.

3) PowerQuery-overzicht

Naast dat op dit scherm bovenaan een lint en aan de linkerkant een kolom met de geïmporteerde tabellen staat is er op het scherm zoals hierboven te zien is, een formulebalk, een resultaat-tabel en aan de rechterkant de naam van de query en toegepaste stappen te zien. PowerQuery laat in de eerste stap zien waar de gegevens vandaan komen. In de tweede stap heeft PowerQuery zelf bepaald wat de inhoud van de kolommen is en in dit geval blijken dit tekst-kolommen te zijn. Daar hoeft niets aan gewijzigd te worden.

4) Samenvoegen rijen met dezelfde vak- en klas-waarden.

Direct het moeilijkste gedeelte van dit blog: het samenvoegen van rijen.
In het voorbeeld is te zien dat het vak duits aan klas B4 door twee docenten worden gegeven namelijk door Gerard (rij 7) en Willem (rij 10). Met een foefje gaan we deze namen achter elkaar zetten.

Selecteer de kolommen vak en klas en klik in het Start-lint op de knop Groeperen op. Het volgende scherm wordt zichtbaar:

Bovenaan staan de twee kolommen die geselecteerd waren. Onderaan in het vak Nieuwe kolomnaam is de tekst "Namen" ingevuld en bij Bewerking is gekozen voor Som en bij Kolom voor de kolom docent. De som van een tekst zal in een fout resulteren maar we gebruiken dit als begin van een formule die we straks aanpassen.

5) Aanpassen formule

Op de website ExcelOffTheGrid van Mark Proctor heb ik het volgende trucje gevonden om rijen samen te voegen. Nadat hierboven de tekst gegroepeerd is, kan de formule worden aangepast van List.Sum naar Text.Combine en moet het type aangepast worden.
Dus de formule in de formulebalk:
= Table.Group(#"Type gewijzigd", {"vak", "klas"}, {{"Namen", each List.Sum([docent]), type nullable text}})
moet gewijzigd worden naar:
= Table.Group(#"Type gewijzigd", {"vak", "klas"}, {{"Namen", each Text.Combine([docent],", "), type text}})

Daarna zijn de fouten omgezet naar tekst en staan de twee namen achter elkaar gescheiden door een komma met een spatie zoals in de formule hierboven was aangegeven.

6) Omzetten naar draaitabel
Selecteer de kolom klas en klik in het Transformeren-lint op de knop Draaikolom waarmee het Draaikolom-scherm wordt geopend.

Kies in het vak Waardenkolom de kolom Namen en klik op de optie Geavanceerde opties. Kies bij Functie voor aggregatiewaarde de optie Niet samenvoegen. Daarmee worden de namen niet opgeteld maar apart vermeld. En hiermee wordt de draaitabel in PowerQuery zichtbaar.

7) Overzetten naar werkblad

Door op het Start-lint op het pijltje te klikken bij de eerste button Sluiten en laden kan er gekozen worden voor hoe de query opgeslagen moet worden. Het volgende menu verschijnt:

Door te kiezen voor de optie Tabel kan worden aangegeven waar de tabel getoond moet worden. Dat kan op hetzelfde werkblad zijn maar ook op een ander werkblad.

8) Vernieuwen
De gegevens in de tabel kunnen worden gewijzigd. De tabel kan worden aangevuld door op de eerste lege rij onder de tabel nieuwe gegevens in te voeren.
Daarna kan via het Gegevens-lint op de button Alles vernieuwen geklikt worden waarmee de draaitabel wordt ge-update.

Helaas is het mij niet gelukt om automatisch de kolombreedte van de draaitabel vast te houden.