Dimensjonstabell i en tekstfil: Power Query Solution

I dette blogginnlegget skal jeg gå gjennom problem nr. 2 fra det pågående ukens problem på , hvor vi diskuterte hvordan man sorterer rotete data til en ren dimensjonstabell. Dette er den andre gjentakelsen av dette nye initiativet som vi er vertskap for hos LuckyTemplates. Jeg er spesielt lidenskapelig opptatt av denne serien fordi den gir alle muligheten til å øve mer regelmessig. Du kan se hele videoen av denne opplæringen nederst på denne bloggen.

Den første onsdagen i hver måned er det en DAX-utfordring og den tredje onsdagen er det en power-query-utfordring .

Dette er en flott mulighet til å utforske, oppdage og lære nye ting om disse språkene som du trenger å utnytte i LuckyTemplates.

Du finner en kategori om ukens problem på LuckyTemplates-forumet.

Hovedfokuset handler om prosessen og ikke så mye på resultatet.

Hvis du lykkes med å bryte ned et problem i mindre biter, vil du kunne løse ethvert problem du står overfor.

Melissa de Korte

Brian snakket tidligere om en teknikk kalt gummiand-feilsøking . Hvis du gikk glipp av det, sørg for å sjekke ut videoen hans. Det kan hjelpe deg når du står fast på disse utfordringene.

La oss først undersøke oppgaven. Vi har en rotete tekstfil som vi må transformere til en skikkelig dimensjonstabell.

Nå, når jeg ser på data som dette, kommer det for det meste til å handle om tekstrensing, fjerning av uønskede tegn, trimming, bruk av store bokstaver, og så videre.

Men vi må også hente alle radene fra disse stabeldataene og gjøre dem om til en enkelt linje for hvert land. Jeg kaller det stabeldata fordi alle feltnavnene gjentas i en enkelt kolonne for hvert land.

Jeg har litt erfaring med Excel, og en av de kraftigste funksjonene etter mitt syn er.

Innholdsfortegnelse

• Pivottabeller i Excel
• Bruke Power Query for å lage dimensjonstabell
• Egendefinert tekstrensefunksjon
• Pivotering av data
• Konklusjon

Pivottabeller i Excel

Pivottabeller lar meg se data segment for segment. Avhengig av hva du plasserer på radseksjonen, kondenserer en pivottabell hver forekomst av det segmentet til en enkelt linje.

Du kan deretter dele det opp ytterligere ved å dra feltene inn i kolonnedelen.

Jeg har laget et eksempel på hvordan dette ser ut. Her har vi stort sett de samme dataene som vi hadde i tekstfilen.

Og på neste side har jeg laget en tom pivottabell.

Nå, det jeg ikke viste deg er at jeg har tre kolonner her i stedet for de to som jeg allerede har vist deg.

Jeg skal dra segmentet mitt til radene.

Siden kolonne 1 hadde feltnavnene, skal jeg dra det til kolonnedelen.

Kolonne 2 hadde alle feltverdiene, så jeg skal dra den inn i verdidelen.

Vi kan se her at pivottabellene ikke kan håndtere tekststrenger. Det teller dem, men det viser at vi har en enkelt verdi for hvert felt.

La oss nå ta en titt på det segmentet jeg opprettet. Så jeg kommer til å gå tilbake til dataene, og jeg skal vise kolonnen min. Du kan se at det bare er et indeksnummer som identifiserer hver av de separate blokkene av dataene som fortsatt er stablet oppå hverandre.

Så for meg vil nøkkeltransformasjonen være en pivot av dataene for å få dem tilbake til det tabellformatet.

I forumet har jeg sett andre måter å håndtere dette på. Det er andre måter å komme til et tabellformat på bortsett fra å bruke en pivothandling, og de fungerer like bra. Hvis du er interessert i det, gå til forumet og begynn å utforske.

Bruke Power Query for å lage dimensjonstabell

La oss gå gjennom strømspørsmålet og se gjennom løsningen min.

Personlig synes jeg brukergrensesnittet gjør en god jobb med å skrive mesteparten av M-koden for oss. Så jeg pleier å designe spørsmålene mine ved å bruke brukergrensesnittet så mye jeg kan.

Når spørringen gjør det jeg trengte at den skulle gjøre, går jeg inn i den avanserte editoren og undersøker M-koden for å se om jeg kan endre den. La oss se hvordan det ser ut.

Dette er min basisgruppe som er bygget ved hjelp av brukergrensesnittet.

Du kan se at det er mange trinn i de anvendte trinnene på høyre side.

Nå er det i seg selv ikke et problem, men bare ved å se på disse trinnene kan du se at det er mange transformasjoner som kan grupperes sammen.

La oss åpne den avanserte editoren.

Vi ser at denne spørringen har 31 trinn.

Jeg la også til noen kommentarer til det samme søket som inneholder de 31 trinnene, men jeg delte det opp i seksjoner.

Det første jeg gjorde var å fjerne trinnet for endringstype. Jeg foreslår å lage en egendefinert funksjon for å utføre alle disse teksttransformasjonstrinnene.

Det er mange kommentarer her, men det er bare to ting jeg vil trekke frem i dette blogginnlegget. Først er den tilpassede funksjonen for tekstrensingsformål .

Det andre er pivottrinnet for å gjøre denne typen data tilbake til et riktig tabellformat .

Egendefinert tekstrensefunksjon

La oss gå tilbake til de tidlige stadiene av å bygge ut spørringen min, hvor jeg hadde alle de grupperte trinnene for å rydde opp i disse tekstene: kolonne 1 og den sammenslåtte kolonnen.

Jeg har også lagt til en ekstra egendefinert kolonne. Dens eneste formål er å bygge ut min egendefinerte tekstrensefunksjon . Jeg påkalte det på den sammenslåtte kolonnen.

På denne måten trenger jeg ikke å skrive funksjonen på én gang, men bygge den opp gradvis ett trinn om gangen, og legger til en ny transformasjon etter å ha gjennomgått resultatene fra forrige trinn.

La oss se på M-koden for tekstrensefunksjonen.

Som du kan se, har jeg flere trinn. Da jeg bygde denne tekstfunksjonen, gikk jeg frem og tilbake mellom spørringen for å se på resultatene for å se hva jeg skulle bygge og hva som skulle rettes videre.

Med dette resultatet utførte jeg alle transformasjonene jeg trengte. Noen av M-funksjonene som jeg brukte her ble levert av brukergrensesnittet da jeg bygde den første spørringen, som teksten. Trimfunksjon. De andre funksjonene som ble brukt var det imidlertid ikke.

Hvis du ikke er kjent med dem, kan du slå opp allei M-formelguiden online. Dette er lenken du må gå.

Du finner en seksjon dedikert til strømspørring og funksjoner.

Scroller du ned finner du seksjonen om tekstfunksjon og hver seksjon starter med en oversikt. Det er en liste over alle tekstfunksjonene i kraftspørringen og formel M-språket.

Hvis du leter etter en spesifikk transformasjon, kan du slå den opp her.

Pivotering av data

Den andre delen jeg ønsket å fremheve mens jeg opprettet denne dimensjonstabellen, er pivotering av selve dataene. La oss også se nærmere på dette.

Jeg startet med å legge til en indeks. Jeg oppdaterte den indeksen for å segmentere datablokkene riktig. Jeg gjorde dette ved å returnere indeksnummeret for hver linje der det hadde tekstlandet i kolonne 1, og så fylte jeg den verdien ned.

Alt vi trenger å gjøre er å pivotere selve dataene. På transformeringsfanen finner du pivotkolonnen. Med kolonne 1 valgt, klikk på Pivot Column.

Den kommer til å bruke kolonne 1s verdier som det nye kolonnenavnet. Den ønsker også å vite hvor verdiene for disse feltnavnene er. De er i vår sammenslåtte kolonne.

Hvis Excel kan håndtere tekstverdier, kan Power Query også på grunn av den avanserte alternativinnstillingen. Alt vi trenger å gjøre er å velge Don't Aggregate for at den skal kunne håndtere tekstverdier.

Når vi klikker på OK, kan vi se at dataene våre har blitt pivotert.

Konklusjon

Bildet ovenfor er det endelige resultatet. Jeg håper du har likt hvordan vi har omformet de rotete dataene i den medfølgende tekstfilen og gjort den til en rens som er egnet for analyse.

Hvis du har likt dette blogginnlegget, vennligst abonner på LuckyTemplates-kanalen slik at du ikke går glipp av nytt innhold.

Jeg håper å se dere alle i fremtidige Problem of the Week-utfordringer.

Melissa