Power Query Modulo: Sådan håndteres stablede data

I denne tutorial vil jeg demonstrere, hvordan du kan transformere rodet stablede data til et mere organiseret format. Der er en ganske nem måde at gøre dette på, og det er en, der vil være utrolig nyttig og har en masse forskellige applikationer. Vi kommer til at bruge Power Query modulo. Du kan se den fulde video af denne tutorial nederst på denne blog.

Min kone kom ned på kontoret og sagde, at hun havde kopieret en masse adresser ud af en kalenderinvitation, og den endte med at indsætte i dette format. Hun havde 28 e-mailadresser og navne, der endte i denne slags mærkelige, stablede format. Det var en liste over navne med en masse citater, kommaer, junk og e-mail-adresser. Hun ville vide, hvordan man bruger Power Query til at få det til et bedre format.

Så lad os springe ind i Power Query, og vi vil se de rå data. Lad os beholde de rå data intakte og bare referere til dem. Vi kalder denne referencefil, TestData .

Den første ting, vi skal gøre, er at tilføje et indeks, og det betyder ikke rigtig noget til disse formål, om det er et nul-baseret indeks eller et et-baseret indeks. Vi vil bare bruge et nul-baseret indeks til denne demonstration. Det er nemt at klikke uden at skulle gennemgå andre muligheder.

Dette vil involvere en pivot, fordi en pivot tager rækker og forvandler dem til kolonner. I dette tilfælde vil vi tage dette sæt rækker og omdanne dem til tre kolonner - en til fornavnet, en til efternavnet og en til e-mailadressen.

Vi kommer til at dreje dem i kolonnerne, men vi skal have en måde, hvorpå pivoten kan vide, hvilket element der går ind i hvilken kolonne. Det er her Power Query Modulo kommer ind i billedet.

Indholdsfortegnelse

• Håndtering af stablede data ved hjælp af Power Query Modulo
• Konklusion

Håndtering af stablede data ved hjælp af Power Query Modulo

Modulo giver os resten, når vi dividerer et bestemt tal. For at få adgang til Modulo går vi til fanen Tilføj kolonne, så finder vi Standard, klik på rullepilen og scroll ned.

Når vi klikker på Modulo, giver det os en pop-up-boks, der beder om antallet af kolonner, vi ønsker. I dette tilfælde ønsker vi tre kolonner.

Hvis vi dividerer tre med tre, får vi en med en rest på nul. Tre til fire giver en rest af en, tre til fem giver en rest af to, og derefter giver tre til seks os en rest af nul. Så det producerer dette fine gentagelsesmønster, som vi kan bruge til vores pivot.

Men før vi gør det, lad os rydde op i dataene. Der er to måder, vi kan gøre dette på. Den ene er, at vi simpelthen kan erstatte værdier. For eksempel, for citater, kan vi bare sætte citatet der og derefter erstatte det med ingenting. Det vil tage og rense disse citater ud.

Den anden måde at gøre dette på, hvor vi på én gang kan rydde op og gemme nogle trin, er ved at bruge en funktion kaldet, Text.Remove . Først tilføjer vi en brugerdefineret kolonne, og derefter lister vi alle de værdier ned, som vi ønsker at slippe af med.

Som du kan se, rydder det alt pænt og nemt ud. Og så, nu kan vi slippe af med den første kolonne og derefter flytte den brugerdefinerede kolonne over.

Vi kan nu gøre vores pivot rent. Vi tager på Modulo, går til Transform og derefter Pivot Column, og vi vil dreje på Custom, men vi ønsker ikke, at den skal samles. Så vi vælger Don't Aggregate og klikker OK.

Vi erstatter det bare med disse værdier i kolonnen. Det skaber så de tre kolonner for os. Vi kan se efternavnet, fornavnet og e-mailadressen derinde, men det er ikke lige det format, vi ønsker.

For at rette op på det kan vi tage disse kolonner og derefter lave en Fyld op .

Og nu har vi disse ekstra rækker på grund af det gentagne mønster af Modulo.

Det kan vi nemt slippe af med ved at fjerne alternative rækker. Det kan vi gøre i indstillingen Fjern rækker.

Vi kan sige, at den første række, der skal fjernes, er to. Fjern derefter anden og tredje række, og behold derefter den fjerde og gør det som et gentaget mønster.

Du kan nu se, at vi har fået renset alle de korrekte data på en pæn måde, og vi har stadig de 28 originale e-mailadresser.

Nu kan vi slippe af med indekskolonnen og omdøbe kolonneoverskrifterne i overensstemmelse hermed.

Konklusion

Du kan se, at med bare Modulo-tricket, og derefter pivotering af data og med en lille smule rengøring, er det ret nemt at gøre. Når du først har fået styr på det, tager det sandsynligvis kun omkring et minut at gøre det hele.

Så forhåbentlig har dette givet dig et par flere ideer og værktøjer. Du kan også tjekke min næste tutorial ud . I det vil vi tage et yderligere kig på nogle af de ting, vi dækkede i denne tutorial og også se på, hvordan vi kan automatisere denne proces på en gentagelig måde ved hjælp af brugerdefinerede funktioner. Jeg vil også vise dig nogle fede tricks til fejlretning af dine brugerdefinerede funktioner.

Jeg håber, du fandt det nyttigt. Du kan se hele videoen nedenfor og se linkene nedenfor for mere relateret indhold.

Alt det bedste!