Power Query Modulo: Hvordan håndtere stablede data

I denne opplæringen skal jeg demonstrere hvordan du kan forvandle rotete stablede data til et mer organisert format. Det er en ganske enkel måte å gjøre dette på, og det er en som kommer til å være utrolig nyttig og har mange forskjellige applikasjoner. Vi skal bruke Power Query modulo. Du kan se hele videoen av denne opplæringen nederst på denne bloggen.

Min kone kom ned på kontoret og sa at hun hadde kopiert en haug med adresser fra en kalenderinvitasjon, og at den ble limt inn i dette formatet. Hun hadde 28 e-postadresser og navn som endte opp i et slikt merkelig, stablet format. Det var en liste over navn med en haug med sitater, kommaer, søppelpost og e-postadresser. Hun ville vite hvordan man bruker Power Query for å få det til et bedre format.

Så la oss hoppe inn i Power Query, så ser vi rådataene. La oss beholde rådataene intakte og bare referere til dem. Vi kaller denne referansefilen, TestData .

Det første vi skal gjøre er å legge til en indeks, og det spiller ingen rolle for disse formålene om det er en null-basert indeks eller en en-basert indeks. Vi vil bare bruke en null-basert indeks for denne demonstrasjonen. Det er enkelt å klikke uten å måtte gå gjennom andre alternativer.

Dette kommer til å involvere en pivot fordi en pivot tar rader og gjør dem om til kolonner. I dette tilfellet ønsker vi å ta dette settet med rader og gjøre dem om til tre kolonner - en for fornavnet, en for etternavnet og en for e-postadressen.

Vi skal pivotere de i kolonnene, men vi må ha en måte for pivoten å vite hvilket element som går inn i hvilken kolonne. Det er her Power Query Modulo kommer inn.

Innholdsfortegnelse

• Håndtering av stablede data ved hjelp av Power Query Modulo
• Konklusjon

Håndtering av stablede data ved hjelp av Power Query Modulo

Modulo gir oss resten når vi deler et bestemt tall. For å få tilgang til Modulo går vi til fanen Legg til kolonne, så finner vi Standard, klikker på rullegardinpilen og blar nedover.

Når vi klikker på Modulo, får vi en popup-boks som spør om antall kolonner vi ønsker. I dette tilfellet ønsker vi tre kolonner.

Hvis vi deler tre på tre, får vi en med resten av null. Tre til fire gir resten av én, tre til fem gir resten av to, og deretter tre til seks gir oss resten av null. Så det produserer dette fine repeterende mønsteret som vi kan bruke for vår pivot.

Men før vi gjør det, la oss rydde opp i dataene. Det er to måter vi kan gjøre dette på. Den ene er at vi rett og slett kan erstatte verdier. For eksempel, for sitatene, kan vi bare sette sitatet der og deretter erstatte det med ingenting. Det tar og renser disse sitatene.

Den andre måten å gjøre dette på, hvor vi samtidig kan rydde opp og lagre noen trinn, er ved å bruke en funksjon kalt Text.Remove . Først legger vi til en egendefinert kolonne, og deretter lister vi ned alle verdiene vi ønsker å bli kvitt.

Som du kan se, fjerner det alt pent og enkelt. Og så, nå kan vi bli kvitt den første kolonnen, og deretter flytte den tilpassede kolonnen over.

Vi kan nå gjøre vårt pivot rent. Vi tar på oss Modulo, går til Transform, og deretter Pivot Column, og vi kommer til å pivotere på Custom, men vi vil ikke at den skal samles. Så vi velger Ikke aggregér og klikker OK.

Vi erstatter det bare med disse verdiene i kolonnen. Deretter lager den de tre kolonnene for oss. Vi kan se etternavnet, fornavnet og e-postadressen der inne, men det er ikke akkurat det formatet vi ønsker.

For å fikse det, kan vi ta disse kolonnene, og deretter gjøre en Fyll opp .

Og nå har vi disse ekstra radene på grunn av det gjentatte mønsteret til Modulo.

Vi kan enkelt bli kvitt det ved å fjerne alternative rader. Vi kan gjøre det i alternativet Fjern rader.

Vi kan si at den første raden som skal fjernes er to. Fjern så den andre og tredje raden, og behold den fjerde og gjør det som et repeterende mønster.

Du kan nå se at vi har renset alle de riktige dataene på en fin måte, og vi har fortsatt de 28 originale e-postadressene.

Nå kan vi bli kvitt indekskolonnen og gi nytt navn til kolonneoverskriftene deretter.

Konklusjon

Du kan se at med bare Modulo-trikset, og deretter pivotering av dataene og med litt rengjøring, er det ganske enkelt å gjøre. Når du først har fått taket på det, tar det sannsynligvis bare et minutt å gjøre hele greia.

Så forhåpentligvis har dette gitt deg noen flere ideer og verktøy. Du kan også sjekke ut min neste veiledning . I det skal vi ta en nærmere titt på noen av tingene vi dekket i denne opplæringen, og også se på hvordan vi kan automatisere denne prosessen på en repeterbar måte ved å bruke tilpassede funksjoner. Jeg skal også vise deg noen kule triks for å feilsøke dine egendefinerte funksjoner.

Jeg håper du fant det nyttig. Du kan se hele videoen nedenfor og sjekk ut lenkene nedenfor for mer relatert innhold.

Beste ønsker!