Avpivot flera kolumner dynamiskt i Lucky Templates

I den här självstudien kommer vi att återkomma till ett ämne som jag behandlade tidigare om hur man avpivoterar flera kolumner i Power Query med List.Zip. En LuckyTemplates-medlem frågade omom hur man gör den metoden dynamisk. Du kan se hela videon av denna handledning längst ner på den här bloggen.

Jag har märkt detta som avancerad eftersom vi kommer att skriva ett antal M-funktioner och några av dem kan till och med vara nya för dig.

Med det sagt, låt oss gå över till Power Query.

Innehållsförteckning

• Data i tabellformat
• Ändra kod för att avpivotera flera kolumner dynamiskt
• Avpivot flera kolumner dynamiskt: Lägga till anpassad kolumnlogik
• Slutsats

Data i tabellformat

Som du kan se har dessa data redan omvandlats till ett lämpligt tablettformat som lämpar sig för analys. Den processen, även om den är mycket viktig, kommer inte att tas upp här, men vi kommer kortfattat gå igenom varje steg.

I källsteget ser vi en konstant upprepning av värdeparen, som är timmarna och kontakterna för vart och ett av ovanstående attribut.

I Tillämpade steg kan du se resten av stegen. Vi tog bort några översta rader, tog bort de tomma kolumnerna, rensade upp våra textvärden och fyllde i dem. Sedan transponerade vi våra data, tog bort den tomma posten, slog samman kolumn ett och två, rengjorde våra textvärden igen och transponerade tillbaka våra data.

Vi skapade ett anpassat steg, som vi kommer att undersöka mer i detalj senare, extraherade värden från vår lista och ändrade sedan till slut typen.

Låt oss titta på hela koden. Det vi är intresserade av här är List.Zip -funktionen. Kort sammanfattning, det tar en enda lista som en och returnerar sedan en lista. Som ett resultat ser vi att denna enda lista i det här fallet innehåller tre separata listor.

Om vi ​​undersöker den första listan innehåller den hårdkodade attributnamn, bara textvärden som skrevs in. Den andra listan innehåller hårdkodade kolumnreferenser för timvärdena. Den slutliga listan innehåller hårdkodade kolumnreferenser för kontaktvärdena.

List.Zip tar sedan ett element från var och en av dessa tre listor och kombinerar dem baserat på deras placering i en ny lista. Till exempel, varje första element från dessa listor kombineras till en ny lista. Samma sak händer för alla värden som finns på andra plats i den listan, och så vidare.

Hårdkodade värden kan vara oroliga om attributen sannolikt inte kommer att förändras över tiden. Så om nästa gång inte alla föremål finns eller nya föremål finns, kan du få problem.

Ändra kod för att avpivotera flera kolumner dynamiskt

Så hur gör vi detta dynamiskt?

Det första jag gör är att gå igenom koden för att se om det finns en plats där vi kan plocka upp dessa attributvärden utan att behöva designa ett separat steg för att åstadkomma det. Så vi kommer att gå tillbaka genom koden, och det här RemoveNull- steget är perfekt.

Du kan se att den listar alla attributvärden här i kolumn ett. Vi kan använda användargränssnittet för att extrahera dem.

Så högerklicka på den rubriken och välj Lägg till som ny fråga nedanför .

Vi får den här listan och det är goda nyheter eftersom vi vill ersätta den hårdkodade listan vi såg tidigare med den dynamiska. Allt vi behöver göra är att göra i ordning värderingarna.

Låt oss börja med att ta bort dessa nollor. Så inuti formelfältet lägger vi till en List.RemoveNulls . Som du kan se nu upprepas alla objekt och det är på grund av värdeparen. Det är listat en gång för timmarna.

Och sedan igen, för kontakterna, för att ta bort dessa dubbletter kan vi lägga till en List.Distinct . Nu har vi skapat den här logiken och allt vi behöver göra är att kopiera den och klistra tillbaka den i vår exempelfråga.

I exempelfrågan öppnar du Advanced Editor och skapar ett nytt variabelnamn. Låt oss kalla det AttributeList . Sedan klistrar vi in ​​koden och sätter ett kommatecken.

Låt oss nu gå tillbaka till vårt anpassade steg. Inuti List.Zip kan vi ersätta den första listan med vårt variabelnamn (AttributeList).

Låt oss nu titta närmare på den andra listan. Vi vet att det refererar till kolumnnamn, och var och en av dessa kolumnnamn börjar med samma attribut som vi redan har i den listan följt av ett mellanslag och en text "timmar".

Vi behöver bara lägga till ett suffix till varje objekt. Så låt oss gå tillbaka ett steg och lägga till en anpassad kolumn för att skapa lite logik.

Avpivot flera kolumner dynamiskt: Lägga till anpassad kolumnlogik

Det är bara en tillfällig kolumn, så vi behöver inte ge den ett riktigt namn. Vi tar bort det när vi är klara.

För att ändra värdena i en lista kan vi använda funktionen List.Transform . Vi måste omvandla värdena i vår AttributeList , så jag ska klistra in det.

Här i rubrikerna ser vi att textvärdena börjar med stor bokstav, och sedan är resten gemener. Vårt attribut innehåller bara ett ord med versaler, så vi måste omvandla det textvärdet också. Och vi kan använda understrecket för att komma åt varje punkt i listan och vi lägger till det suffixet.

Med det får vi ett listvärde i vår kolumn. Det spelar ingen roll vilken du väljer, utan klicka bara åt sidan i det vita utrymmet i någon av dem här.

Nedanför sidan ser vi en förhandsvisning av listan som vi har skapat. Så du ser att vi har de kolumnrubrikerna som nu är korrekt kapslade så att de matchar kolumnrubrikerna vi ser här med den här listan.

Vi har nu möjlighet att identifiera de fält från vår post som vi vill komma åt. Så låt oss öppna vår anpassade kolumndialogruta igen och lägga till Record.SelectFields . Den vill ha en post som en post, så vi kan använda det understrecket för att komma åt den aktuella posten som vi är på. Och sedan har vi gett den en lista med alla dessa fältnamn, så lägg till en avslutande parentes och tryck på OK .

Så vi har ingen lista längre, men vi får nu ett rekord. Låt oss klicka åt sidan i det vita utrymmet igen för att se innehållet i den posten. Här ser vi att den listar postfältnamnen och postfältvärdena.

Men vi är bara intresserade av rekordfältsvärdena, så vi måste extrahera det. Låt oss gå tillbaka till vår anpassade kolumndialogruta och lägga till Record.FieldsValues ​​.

Vi får nu en lista igen och inte längre ett rekord. Vi klickar bort åt sidan i det vita utrymmet igen, och du ser att vi nu har en lista med just dessa värden. Så det ser bra ut.

Låt oss kopiera hela logiken som vi har skapat, förutom de sista avslutande parenteserna. Och nu kan vi ta bort vår tillfälliga kolumn eftersom vi inte längre behöver den.

På vår formelfält väljer vi vår andra lista och ersätter den med vår logik.

För att skapa en lista med postfältens värden för kontakterna behöver vi bara ändra suffixet. Så vi kan välja den sista listan också och klistra in vår logik.

Sedan, istället för timmar , låt oss ändra detta till kontakter .

Inga fel och det ser bra ut. Detta är nu en dynamisk lösning för att avpivotera flera kolumner.

Slutsats

Det är så du avpivoterar flera kolumner dynamisktmed List.Zip.

Om ett nytt attribut kommer in kommer det automatiskt att hämtas av AttributeList. Och när ett föremål inte längre finns, kommer det inte att finnas på den listan.

Jag hoppas att du har njutit av den här. Titta på hela videon nedan för mer information om den här handledningen och kolla in länkarna nedan för mer innehåll kring upphäva flera kolumner i LuckyTemplates.

Med vänliga hälsningar

Melissa