Meerdere kolommen dynamisch ongedaan maken in LuckyTemplates

In deze zelfstudie gaan we terug naar een onderwerp dat ik eerder heb besproken over het ongedaan maken van meerdere kolommen in Power Query met behulp van List.Zip. Een lid van LuckyTemplates vroeg op het hoe je die methode dynamisch kunt maken. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

Ik heb dit geavanceerd genoemd omdat we een aantal M-functies gaan schrijven en sommige daarvan zijn misschien zelfs nieuw voor je.

Met dat gezegd, laten we naar Power Query gaan.

Inhoudsopgave

• Gegevens in tabelvorm
• Code wijzigen om meerdere kolommen dynamisch ongedaan te maken
• Meerdere kolommen dynamisch ongedaan maken: aangepaste kolomlogica toevoegen
• Conclusie

Gegevens in tabelvorm

Zoals u kunt zien, zijn deze gegevens al omgezet in een geschikt tabletformaat dat geschikt is voor analyse. Dat proces, hoewel erg belangrijk, wordt hier niet behandeld, maar we zullen elke stap kort bespreken.

In de bronstap zien we een constante herhaling van de waardeparen, die de uren en contacten zijn voor elk van de hierboven genoemde attributen.

In de toegepaste stappen kunt u de rest van de stappen zien. We hebben enkele bovenste rijen verwijderd, de lege kolommen verwijderd, onze tekstwaarden opgeschoond en gevuld. Vervolgens hebben we onze gegevens getransponeerd, het lege record verwijderd, kolom één en twee samengevoegd, onze tekstwaarden opnieuw opgeschoond en onze gegevens terug getransponeerd.

We hebben een aangepaste stap gemaakt, die we later in meer detail zullen onderzoeken, waarden uit onze lijst hebben gehaald en uiteindelijk het type hebben gewijzigd.

Laten we de hele code bekijken. Waar we hier in geïnteresseerd zijn, is de List.Zip- functie. Korte samenvatting, er is een enkele lijst als parameter nodig en retourneert vervolgens een lijst. Hierdoor zien we dat deze enkele lijst in dit geval drie afzonderlijke lijsten bevat.

Als we de eerste lijst bekijken, bevat deze hardgecodeerde attribuutnamen, alleen tekstwaarden die zijn ingevoerd. De tweede lijst bevat hardgecodeerde kolomverwijzingen voor de uurwaarden. De definitieve lijst bevat hardgecodeerde kolomverwijzingen voor de contactwaarden.

List.Zip neemt vervolgens een element uit elk van deze drie lijsten en combineert ze op basis van hun positie in een nieuwe lijst. Elk eerste element uit die lijsten wordt bijvoorbeeld gecombineerd tot een nieuwe lijst. Hetzelfde gebeurt voor alle waarden die als tweede in die lijst worden vermeld, enzovoort.

Hardgecodeerde waarden kunnen van belang zijn als het onwaarschijnlijk is dat de attributen in de loop van de tijd veranderen. Dus als de volgende keer niet alle items aanwezig zijn of er nieuwe items zijn, kun je in de problemen komen.

Code wijzigen om meerdere kolommen dynamisch ongedaan te maken

Hoe maken we dit dynamisch?

Het eerste dat ik doe, is de code doorlopen om te zien of er een plaats is waar we die attribuutwaarden kunnen oppikken zonder een aparte stap te hoeven ontwerpen om dat te bereiken. Dus we gaan een stap terug door de code en deze RemoveNull- stap is perfect.

U kunt zien dat alle attribuutwaarden hier in kolom één worden vermeld. We kunnen de gebruikersinterface gebruiken om ze uit te pakken.

Dus klik met de rechtermuisknop op die kop en selecteer onderaan Toevoegen als nieuwe query .

We krijgen deze lijst en dat is goed nieuws, want we willen die hardgecodeerde lijst die we eerder zagen vervangen door de dynamische. Het enige wat we moeten doen is de waarden opschonen.

Laten we beginnen met het verwijderen van die nullen. Dus in de formulebalk voegen we een List.RemoveNulls toe . Zoals je nu kunt zien, worden alle items herhaald en dat komt door de waardeparen. Het staat een keer vermeld voor de uren.

En nogmaals, voor de contacten, om die duplicaten te verwijderen, kunnen we een toevoegen List.Distinct . Nu hebben we deze logica gemaakt en hoeven we deze alleen maar te kopiëren en terug te plakken in onze voorbeeldquery.

Open in de voorbeeldquery de geavanceerde editor en maak een nieuwe variabelenaam aan. Laten we het noemen, AttributeList . Vervolgens plakken we de code en plaatsen we een komma.

Laten we nu teruggaan naar onze aangepaste stap. Binnen List.Zip kunnen we die eerste lijst vervangen door onze variabelenaam (AttributeList).

Laten we nu de tweede lijst eens nader bekijken. We weten dat het verwijst naar kolomnamen, en elk van die kolomnamen begint met hetzelfde attribuut dat we al in die lijst hebben, gevolgd door een spatie en de tekst "uren".

We hoeven alleen maar een achtervoegsel aan elk item toe te voegen. Dus laten we een stap teruggaan en een aangepaste kolom toevoegen om enige logica te creëren.

Meerdere kolommen dynamisch ongedaan maken: aangepaste kolomlogica toevoegen

Het is slechts een tijdelijke kolom, dus we hoeven het geen goede naam te geven. We zullen het verwijderen zodra we klaar zijn.

Om de waarden in een lijst te wijzigen, kunnen we de functie List.Transform gebruiken . We moeten de waarden in onze AttributeList transformeren , dus die ga ik erin plakken.

Hier in de koppen zien we dat de tekstwaarden beginnen met een hoofdletter en de rest is een kleine letter. Ons attribuut bevat alleen een woord met een hoofdletter, dus we moeten die tekstwaarde ook transformeren. En we kunnen het onderstrepingsteken gebruiken om toegang te krijgen tot elk item van de lijst en we zullen dat achtervoegsel toevoegen.

Daarmee krijgen we een lijstwaarde in onze kolom. Het maakt niet uit welke je selecteert, maar klik gewoon opzij in de witte ruimte in een van hen hier.

Onder de pagina zien we een voorbeeld van die lijst die we hebben gemaakt. Dus je ziet dat we die kolomkoppen nu op de juiste manier hebben omhuld, zodat ze overeenkomen met de kolomkoppen die we hier zien met deze lijst.

We hebben nu de middelen om de velden uit ons record te identificeren waartoe we toegang willen hebben. Dus laten we ons dialoogvenster voor aangepaste kolommen opnieuw openen en Record.SelectFields toevoegen . Het wil een record als een record, dus we kunnen dat onderstrepingsteken gebruiken om toegang te krijgen tot de huidige record waarop we ons bevinden. En dan hebben we het een lijst gegeven met al die veldnamen, dus voeg een haakje sluiten toe en druk op OK .

We hebben dus geen lijst meer, maar krijgen nu een record. Laten we weer naar de zijkant in de witte ruimte klikken om de inhoud van dat record te bekijken. Hier zien we dat het de recordveldnamen en de recordveldwaarden weergeeft.

Maar we zijn alleen geïnteresseerd in de recordveldwaarden, dus die moeten we eruit halen. Laten we teruggaan naar ons dialoogvenster met aangepaste kolommen en Record.FieldsValues ​​toevoegen .

We krijgen nu weer een lijst en geen record meer. We klikken weer naar de zijkant in de witte ruimte en je ziet dat we nu een lijst hebben met precies die waarden. Dus dat ziet er goed uit.

Laten we de volledige logica kopiëren die we hebben gemaakt, behalve de laatste haakjes sluiten. En nu kunnen we onze tijdelijke zuil verwijderen omdat we hem niet meer nodig hebben.

Op onze formulebalk selecteren we onze tweede lijst en vervangen deze door onze logica.

Om een ​​lijst met de recordveldwaarden voor de contactpersonen te maken, hoeven we alleen het achtervoegsel te wijzigen. We kunnen dus ook die laatste lijst selecteren en onze logica erin plakken.

Laten we dit dan in plaats van uren wijzigen in contacten .

Geen fouten en het ziet er goed uit. Dit is nu een dynamische oplossing om meerdere kolommen ongedaan te maken.

LuckyTemplates Unpivot-kolommen – Power Query-zelfstudie
Kolommen unpivot in LuckyTemplates
Unpivot en Pivot Basisprincipes in LuckyTemplates – Query Editor Review

Conclusie

Zo kunt u met behulp van List.Zip meerdere kolommen dynamisch ongedaan maken in .

Als er een nieuw attribuut binnenkomt, wordt het automatisch opgepikt door de AttributeList. En wanneer een item niet meer bestaat, zal het niet aanwezig zijn op die lijst.

Ik hoop dat je genoten hebt van deze. Bekijk de volledige video hieronder voor meer informatie over deze zelfstudie en bekijk de onderstaande links voor meer inhoud over het ongedaan maken van meerdere kolommen in LuckyTemplates.

Al het beste

Melissa