Kolommen dynamisch samenvoegen in een Power Query-tabel

In deze zelfstudie onderzoeken we een interessante vraag die op het is gesteld over het dynamisch samenvoegen van een wisselend aantal kolommen in een Power Query-tabel. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

Dit scenario gaat over export van onbewerkte gegevens waardoor de gegevens overlopen naar een onbekend aantal aangrenzende kolommen. Als u naar het voorbeeld kijkt, ziet u dat de beschrijving is overgelopen naar kolom 4 en dat beschrijving 2 is opgesplitst in twee aangrenzende kolommen. Maar dat zal niet altijd het geval zijn. In de volgende cyclus kan dit aantal kolommen veranderen.

Laten we, voordat we ingaan op de oplossing, de M-code bekijken die Power Query genereert wanneer u kolommen samenvoegt.

Inhoudsopgave

• Power Query-tabel M-code
• Een veranderend aantal kolommen samenvoegen in een Power Query-tabel
• Conclusie

Power Query-tabel M-code

Er zijn een aantal manieren om kolommen samen te voegen. Een daarvan is om zowel de beschrijvingskolom te selecteren als de Shift-toets of Control-toets ingedrukt te houden en vervolgens ook kolom 4 te selecteren. Klik vervolgens met de rechtermuisknop en kies Kolommen samenvoegen .

Of selecteer kolommen samenvoegen op het tabblad Transformeren . En dit dialoogvenster verschijnt, waarin u de scheidingsteken kunt selecteren.

Kies Tab in het pop-upvenster en u kunt een nieuwe kolomnaam invoeren. In dit geval noemde ik het Beschrijving.

In de formulebalk zien we de M-code die deze transformatiestap heeft gemaakt.

Als de formulebalk niet zichtbaar is op uw scherm, gaat u naar het tabblad Weergave en schakelt u deze in.

De functie die Power Query gebruikt om kolommen samen te voegen, is dus Table.CombinedColumns . De eerste parameter die nodig is, is een tabel. Die tabel wordt geretourneerd door de vorige stap in onze code, dus in de toegepaste stappen ziet u dat dit Source wordt genoemd .

Vervolgens heeft het de kolomnamen in een lijst hard gecodeerd. Hier zie je lijstinitialisaties, en tussen in-tekstwaarden, de kolomnaambeschrijving en de kolomnaam kolom 4. Vervolgens wordt een andere M-functie aangeroepen om de tekstwaarden in die kolommen te combineren.

En tot slot geeft het de nieuwe kolomnaam door als tekst, zodat we die kunnen aanpassen.

Dus als we willen dat deze functie een veranderend aantal kolommen dynamisch samenvoegt, moeten we die tweede parameterwaarde wijzigen die nu een lijst met hardgecodeerde kolomnamen bevat.

Een veranderend aantal kolommen samenvoegen in een Power Query-tabel

Ik ga terug naar mijn query met onbewerkte gegevens en als we de kolomnamen nader bekijken, zien we dat elk van de overloopkolommen anoniem is. Ze hebben geen eigennaam, maar ze beginnen allemaal met de tekstkolom, gevolgd door een cijfer. Eens kijken of we dat kunnen gebruiken. Eerst maak ik een referentie door hier met de rechtermuisknop te klikken en Referentie te selecteren .

En om de kolomnamen in de formulebalk te krijgen, kan ik Table.ColumnNames toevoegen . Deze functie retourneert een lijst met alle kolomnamen uit die tabel. Laten we er weer een tabel van maken door op Naar tabel te klikken .

Klik in dit pop-upvenster op OK.

Vervolgens selecteer ik op het tabblad Kolom toevoegen Opmaak en vervolgens Bijsnijden .

Dit is geen transformatie die ik wil uitvoeren, maar het genereert wel het grootste deel van de M-code voor mij. Ik hoef alleen die trimfunctie te vervangen door mijn eigen logica .

Dus in de formulebalk, in plaats van Text.Trim , kunnen we zeggen if Text.StartsWith , en dan verwijzen naar onze Kolom 1 , en ik zal dat meteen kopiëren. Het wil die tekst waarnaar we zoeken, dus dat wordt Column. Dus als het begint met een tekstkolom, dan willen we "null" - voor al het andere willen we wat er in Kolom 1 staat. We kunnen die kolom ook een andere naam geven, dus laten we het binnen de formulebalk, in plaats van Trim, GroupColumn noemen .

Nu hoeven we ze alleen maar op deze waarden in te vullen. Dus ik klik met de rechtermuisknop op mijn koptekst, selecteer Vullen en omlaag .

Dus de volgende keer dat er gegevens binnenkomen en dat aantal kolommen is veranderd, pakt deze groep het automatisch op.

Laten we deze query hernoemen, ik noem het kolomgroepen.

Laten we ervoor zorgen dat het laden ervan is uitgeschakeld, omdat dit slechts een ondersteunende vraag is.

Ik kan naar deze ondersteunende vraag verwijzen en ik zal deze later hernoemen. Laten we dat voor nu even laten. Dus als je het je herinnert, zijn we begonnen met het samenvoegen van twee kolommen en dat creëerde een hardgecodeerde lijst met de kolomnamen , maar nu kunnen we filteren op Beschrijving in onze GroupColumn .

Dus als we dat filter doen op de Beschrijving in Kolom 1, krijgen we de kolommen die aan die criteria voldoen.

Dit is echter een tabel en geen lijst. We moeten eruit halen wat er in die eerste kolom overblijft, omdat we die kolommen moeten opnemen in onze samenvoegbewerking. Om dat te doen, kunnen we met de rechtermuisknop op de kop van kolom 1 klikken en Drill Down selecteren .

En nu krijgen we een lijst. Dus laten we deze query een juiste naam geven ( ListDescr ) en laten we controleren of deze is uitgeschakeld voor laden.

Nu kunnen we teruggaan naar onze resultaatquery en dat tweede argument hier vervangen door onze dynamische lijst. Dus hier kunnen we verwijzen naar onze ListDescr .

Laten we dit ook herhalen voor beschrijving 2. Ik ga naar mijn ColumnGroups-query. Ik maak nog een referentie en selecteer de kolommen die ik nodig heb. Vervolgens filter ik Beschrijving 2, waarmee deze drie kolommen worden geretourneerd. Ik klik met de rechtermuisknop op mijn koptekst en selecteer Drill Down en hernoem deze query ook ( ListDescr2 ).

Ik ga dan terug naar mijn resultaatquery, selecteer kolom Beschrijving2 en druk op shift of control om ook de volgende kolom te selecteren (kolom2). Ik klik met de rechtermuisknop op de kop en kies Kolommen samenvoegen .

In het pop-upvenster kies ik Tab als scheidingsteken en noem ik dit Beschrijving2.

Ik verander gewoon de kolomnaam hier op de formulebalk. Ik zal die hardgecodeerde lijst ook wijzigen van de lijst die we zojuist hebben gemaakt. En nu, zoals u kunt zien, heeft dit ook kolom 8 opgepikt die we eerder hadden uitgesloten.

Query's samenvoegen in LuckyTemplates
LuckyTemplates-queryparameters: tabellen optimaliseren
Datumtabelkolommen in LuckyTemplates sorteren

Conclusie

In deze blog heb ik je laten zien hoe je M-codes in Power Query kunt gebruiken om kolommen dynamisch samen te voegen. Het is niet moeilijk zolang u bekend bent met hoe de Power Query werkt en welke M-codes u moet gebruiken.

Ik hoop dat je genoten hebt van deze. Bekijk de onderstaande links voor meer gerelateerde inhoud.

Al het beste!

Melissa