Dimensionstabel i en tekstfil: Power Query Solution

I dette blogindlæg vil jeg gennemgå problem #2 fra ugens igangværende problem på , hvor vi diskuterede, hvordan man sorterer rodet data i en ren dimensionstabel. Dette er den anden gentagelse af dette nye initiativ, som vi er vært for hos LuckyTemplates. Jeg er især passioneret omkring denne serie, fordi den giver alle mulighed for at få mere øvelse på en regelmæssig basis. Du kan se den fulde video af denne tutorial nederst på denne blog.

Den første onsdag i hver måned er der en DAX-udfordring , og den tredje onsdag er der en power-forespørgselsudfordring .

Dette er en fantastisk mulighed for at udforske, opdage og lære nye ting om disse sprog, som du skal bruge i LuckyTemplates.

Du finder en kategori om Ugens problem på LuckyTemplates forum.

Det primære fokus er alt om processen og ikke så meget på resultatet.

Hvis du har succes med at nedbryde et problem i mindre stykker, vil du være i stand til at løse ethvert problem, du står over for.

Melissa de Korte

Brian har tidligere talt om en teknik kaldet rubber duck debugging . Hvis du gik glip af det, så sørg for at tjekke hans video ud. Det kan hjælpe dig, når du bliver hængende i disse udfordringer.

Lad os først undersøge den aktuelle opgave. Vi har en rodet tekstfil, som vi skal transformere til en ordentlig dimensionstabel.

Nu, når jeg ser på data som denne, kommer det for det meste til at handle om tekstrensning, fjernelse af uønskede tegn, trimning, brug af store bogstaver og så videre.

Men vi skal også hente alle rækkerne fra disse stakdata og omdanne dem til en enkelt linje for hvert land. Jeg kalder det stakdata, fordi alle feltnavnene gentages i en enkelt kolonne for hvert land.

Jeg har en vis erfaring med Excel, og en af ​​dens mest kraftfulde funktioner efter min mening er.

Indholdsfortegnelse

• Pivottabeller i Excel
• Brug af Power Query til at oprette dimensionstabel
• Brugerdefineret tekstrensningsfunktion
• Pivotering af data
• Konklusion

Pivottabeller i Excel

Pivottabeller giver mig mulighed for at se data segment for segment. Afhængigt af hvad du placerer på rækkesektionen, kondenserer en pivottabel hver forekomst af det segment til en enkelt linje.

Du kan derefter opdele det yderligere ved at trække felter ind i kolonneafsnittet.

Jeg har lavet et eksempel på, hvordan det ser ud. Her har vi stort set de samme data, som vi havde i tekstfilen.

Og på næste side har jeg lavet en tom pivottabel.

Nu, hvad jeg ikke viste dig er, at jeg har tre kolonner her i stedet for de to, som jeg allerede har vist dig.

Jeg vil trække mit segment til rækkerne.

Da kolonne 1 havde feltnavnene, vil jeg trække det til kolonneafsnittet.

Kolonne 2 havde alle feltværdierne, så jeg vil trække det ind i værdiafsnittet.

Vi kan se her, at pivottabellerne ikke kan håndtere tekststrenge. Det tæller dem, men det viser, at vi har en enkelt værdi for hvert felt.

Lad os nu tage et kig på det segment, som jeg oprettede. Så jeg vil gå tilbage til dataene, og jeg vil vise min kolonne. Du kan se, at det kun er et indeksnummer, der identificerer hver af de separate blokke af data, der stadig er stablet oven på hinanden.

Så for mig vil nøgletransformationen være en omdrejningspunkt for dataene for at få dem tilbage til det tabelformat.

I forummet har jeg set andre måder at håndtere dette på. Der er andre måder at komme til et tabelformat på bortset fra at bruge en pivothandling, og de fungerer lige så godt. Hvis du er interesseret i det, så gå til forummet og begynd at udforske.

Brug af Power Query til at oprette dimensionstabel

Lad os gennemgå strømforespørgslen og gennemgå min løsning.

Personligt synes jeg, at brugergrænsefladen gør et godt stykke arbejde med at skrive størstedelen af ​​M-koden for os. Så jeg har en tendens til at designe mine forespørgsler ved hjælp af brugergrænsefladen, så meget jeg kan.

Når forespørgslen gør, hvad jeg havde brug for, går jeg ind i den avancerede editor og undersøger M-koden for at se, om jeg kan ændre den. Lad os se, hvordan det ser ud.

Dette er min basisgruppe, der er bygget ved hjælp af brugergrænsefladen.

Du kan se, at der er mange trin i de anvendte trin i højre side.

Det er nu i sig selv ikke et problem, men bare ved at se på disse trin, kan du se, at der er en masse transformationer, der kan grupperes sammen.

Lad os åbne den avancerede editor.

Vi ser, at denne forespørgsel har 31 trin.

Jeg tilføjede også nogle kommentarer til den samme forespørgsel, der indeholdt de 31 trin, men jeg delte den op i sektioner.

Den første ting jeg gjorde var at fjerne ændringstypetrinnet. Jeg foreslår at oprette en brugerdefineret funktion til at udføre alle disse teksttransformationstrin.

Der er mange kommentarer her, men der er kun to ting, jeg vil fremhæve i dette blogindlæg. Først er den brugerdefinerede funktion til tekstrensningsformål .

Det andet er omdrejningspunktet for at vende denne type data tilbage til et korrekt tabelformat .

Brugerdefineret tekstrensningsfunktion

Lad os gå tilbage til de tidlige stadier af opbygningen af ​​min forespørgsel, hvor jeg havde alle disse grupperede trin til at rydde op i disse tekster: kolonne 1 og den flettede kolonne.

Jeg tilføjede også en ekstra brugerdefineret kolonne. Dens eneste formål er at opbygge min brugerdefinerede tekstrensningsfunktion . Jeg påberåbte mig det i den flettede kolonne.

På denne måde behøver jeg ikke skrive funktionen på én gang, men opbygge den gradvist et trin ad gangen, og tilføje en ny transformation efter at have gennemgået resultaterne fra det forrige trin.

Lad os se på M-koden for tekstrensningsfunktionen.

Som du kan se, har jeg flere trin. Da jeg byggede denne tekstfunktion, gik jeg frem og tilbage mellem forespørgslen for at se på resultaterne for at se, hvad der skulle bygges, og hvad der skulle rettes derefter.

Med dette resultat udførte jeg alle de transformationer, jeg havde brug for. Nogle af de M-funktioner, som jeg brugte her, blev leveret af brugergrænsefladen, da jeg byggede min indledende forespørgsel, såsom teksten. Trim funktion. Det var de andre anvendte funktioner dog ikke.

Hvis du ikke er bekendt med dem, kan du slå alle opi M-formelguiden online. Dette er linket , hvor du skal hen.

Du vil finde et afsnit dedikeret til strømforespørgsler og funktioner.

Hvis du scroller ned, finder du afsnittet om tekstfunktion og hvert afsnit starter med en oversigt. Der er en liste over alle tekstfunktionerne inde i power-forespørgslen og formel M-sproget.

Hvis du leder efter en specifik transformation, kan du slå den op lige her.

Pivotering af data

Den anden del, jeg ville fremhæve, mens jeg oprettede denne dimensionstabel, er pivotering af selve dataene. Lad os også se nærmere på dette.

Jeg startede med at tilføje et indeks. Jeg opdaterede det indeks for at segmentere datablokkene korrekt. Det gjorde jeg ved at returnere indeksnummeret for hver linje, hvor det havde teksten land i kolonne 1, og så udfyldte jeg denne værdi.

Alt, hvad vi skal gøre, er at dreje selve dataene. På transformationsfanen finder du pivotkolonnen. Med kolonne 1 valgt, klik på Pivot kolonne.

Det kommer til at bruge kolonne 1's værdier som det nye kolonnenavn. Den ønsker også at vide, hvor værdierne for disse feltnavne er. De er i vores fusionerede kolonne.

Hvis Excel kan håndtere tekstværdier, kan Power Query også på grund af dens avancerede indstillinger. Alt, hvad vi skal gøre, er at vælge Don't Aggregate for at kunne håndtere tekstværdier.

Når vi klikker på OK, kan vi se, at vores data er blevet pivoteret.

Konklusion

Billedet ovenfor er det endelige resultatforespørgsel. Jeg håber, du har nydt, hvordan vi har omformet de rodede data i den medfølgende tekstfil og forvandlet den til en rensning, der er egnet til analyse.

Hvis du har nydt dette blogindlæg, bedes du abonnere på LuckyTemplates-kanalen, så du ikke går glip af nyt indhold.

Jeg håber at se jer alle i fremtidige problem of the week-udfordringer.

Melissa