Ophæv pivot flere kolonner dynamisk i Lucky Templates

I dette selvstudium vil vi gense et emne, som jeg dækkede tidligere, om hvordan man kan fjerne pivot af flere kolonner i Power Query ved hjælp af List.Zip. Et LuckyTemplates-medlem spurgte omom, hvordan man gør den metode dynamisk. Du kan se den fulde video af denne tutorial nederst på denne blog.

Jeg har mærket dette avanceret, fordi vi vil skrive en række M-funktioner, og nogle af dem kan endda være nye for dig.

Med det sagt, lad os gå over til Power Query.

Indholdsfortegnelse

• Data i tabelformat
• Ændring af kode for at fjerne pivotering af flere kolonner dynamisk
• Ophæv pivotering af flere kolonner dynamisk: Tilføjelse af brugerdefineret kolonnelogik
• Konklusion

Data i tabelformat

Som du kan se, er disse data allerede blevet transformeret til et ordentligt tabletformat, der er egnet til analyse. Denne proces, selvom den er meget vigtig, vil ikke blive dækket her, men vi vil kort gennemgå hvert trin.

I kildetrinnet ser vi en konstant gentagelse af værdiparrene, som er timerne og kontakterne for hver af de ovennævnte attributter.

I de anvendte trin kan du se resten af ​​trinene. Vi fjernede nogle øverste rækker, fjernede de tomme kolonner, ryddede op i vores tekstværdier og udfyldte dem. Derefter transponerede vi vores data, fjernede den tomme post, slog kolonne et og to sammen, rensede vores tekstværdier igen og transponerede vores data tilbage.

Vi oprettede et brugerdefineret trin, som vi vil undersøge mere detaljeret senere, udtrak værdier fra vores liste og ændrede til sidst typen.

Lad os se på hele koden. Det, vi er interesseret i her, er List.Zip -funktionen. Kort opsummering, det tager en enkelt liste som en og returnerer derefter en liste. Som et resultat ser vi, at denne enkelte liste i dette tilfælde indeholder tre separate lister.

Hvis vi undersøger den første liste, indeholder den hårdkodede attributnavne, blot tekstværdier, der blev indtastet. Den anden liste indeholder hårdkodede kolonnereferencer for timeværdierne. Den endelige liste indeholder hårdkodede kolonnereferencer for kontaktværdierne.

List.Zip tager derefter et element fra hver af disse tre lister og kombinerer dem baseret på deres placering i en ny liste. For eksempel bliver hvert første element fra disse lister kombineret til en ny liste. Det samme sker for alle værdier, der er anført som nummer to på listen, og så videre.

Hardkodede værdier kan være bekymrede, hvis attributterne sandsynligvis ikke ændrer sig over tid. Så hvis næste gang ikke alle varer er til stede eller nye varer findes, kan du få problemer.

Ændring af kode for at fjerne pivotering af flere kolonner dynamisk

Så hvordan gør vi denne dynamisk?

Den første ting, jeg gør, er at gå gennem koden for at se, om der er et sted, hvor vi kan hente disse attributværdier uden at skulle designe et separat trin for at opnå det. Så vi går tilbage gennem koden, og dette RemoveNull- trin er perfekt.

Du kan se, at den viser alle attributværdierne her i kolonne et. Vi kan bruge brugergrænsefladen til at udtrække dem.

Så højreklik på den overskrift, og vælg Tilføj som ny forespørgsel nede under .

Vi får denne liste, og det er gode nyheder, fordi vi ønsker at erstatte den hårdkodede liste, vi så tidligere, med den dynamiske. Det eneste, vi skal gøre, er at rydde op i værdierne.

Lad os starte med at fjerne disse nuller. Så inde i formellinjen tilføjer vi en List.RemoveNulls . Som du kan se nu, gentages alle elementer, og det er på grund af værdiparrene. Det er opført én gang for timerne.

Og så igen, for kontakterne, for at fjerne disse dubletter, kan vi tilføje en List.Distinct . Nu har vi skabt denne logik, og alt vi skal gøre er at kopiere den og indsætte den tilbage til vores eksempelforespørgsel.

I prøveforespørgslen skal du åbne Advanced Editor og oprette et nyt variabelnavn. Lad os kalde det, AttributeList . Så indsætter vi koden og sætter et komma.

Lad os nu gå tilbage til vores brugerdefinerede trin. Inde i List.Zip kan vi erstatte den første liste med vores variabelnavn (AttributeList).

Lad os nu se nærmere på den anden liste. Vi ved, at det refererer til kolonnenavne, og hver af disse kolonnenavne starter med den samme attribut, som vi allerede har på listen efterfulgt af et mellemrum og en tekst "timer".

Vi skal blot tilføje et suffiks til hver vare. Så lad os gå et trin tilbage og tilføje en brugerdefineret kolonne for at skabe noget logik.

Ophæv pivotering af flere kolonner dynamisk: Tilføjelse af brugerdefineret kolonnelogik

Det er kun en midlertidig spalte, så vi behøver ikke give den et rigtigt navn. Vi fjerner det, når vi er færdige.

For at ændre værdierne i en liste kan vi bruge List.Transform- funktionen. Vi er nødt til at transformere værdierne i vores AttributeList , så jeg vil indsætte det.

Her i overskrifterne ser vi, at tekstværdierne starter med stort bogstav, og så er resten med små bogstaver. Vores attribut indeholder kun et stort ord, så vi skal også transformere denne tekstværdi. Og vi kan bruge understregningen til at få adgang til hvert punkt på listen, og vi tilføjer det suffiks.

Med det får vi en listeværdi i vores kolonne. Det er lige meget, hvilken du vælger, men klik blot ud til siden i det hvide rum i en af ​​dem her.

Under siden ser vi en forhåndsvisning af den liste, vi har oprettet. Så du kan se, at vi har disse kolonneoverskrifter, der nu er korrekt indkapslede, så de matcher de kolonneoverskrifter, vi ser her med denne liste.

Vi har nu midlerne til at identificere de felter fra vores registrering, som vi ønsker at få adgang til. Så lad os åbne vores brugerdefinerede kolonnedialogboks igen og tilføje Record.SelectFields . Den vil have en post som en post, så vi kan bruge den understregning til at få adgang til den aktuelle post, som vi er på. Og så har vi givet den en liste med alle de feltnavne, så tilføj en afsluttende parentes og tryk OK .

Så vi har ikke længere en liste, men vi får nu en rekord. Lad os klikke ud til siden i det hvide rum igen for at se indholdet af den post. Her ser vi, at den viser postfeltnavnene og postfeltværdierne.

Men vi er kun interesserede i rekordfeltværdierne, så det skal vi udtrække. Lad os gå tilbage til vores brugerdefinerede kolonnedialogboks og tilføje Record.FieldsValues ​​.

Vi får nu en liste igen og ikke længere en rekord. Vi klikker ud til siden i det hvide rum igen, og du ser, at vi nu har en liste med netop disse værdier. Så det ser godt ud.

Lad os kopiere den fulde logik, som vi har oprettet, bortset fra de sidste afsluttende parenteser. Og nu kan vi fjerne vores midlertidige kolonne, fordi vi ikke længere har brug for den.

På vores formellinje vælger vi vores anden liste og erstatter den med vores logik.

For at oprette en liste med postfelternes værdier for kontakterne skal vi kun ændre suffikset. Så vi kan også vælge den sidste liste og indsætte vores logik.

Så lad os i stedet for timer ændre dette til kontakter .

Ingen fejl og det ser godt ud. Dette er nu en dynamisk løsning til at frigøre flere kolonner.

Konklusion

Det er sådan, du deaktiverer flere kolonner dynamisk indved hjælp af List.Zip.

Hvis en ny attribut kommer ind, vil den automatisk blive hentet af AttributeList. Og når en vare ikke længere eksisterer, vil den ikke være til stede på listen.

Jeg håber, du har nydt denne. Se hele videoen nedenfor for flere detaljer om denne tutorial og tjek nedenstående links for mere indhold omkring unpivot flere kolonner i LuckyTemplates.

Alt det bedste

Melissa