Måtttabell i en textfil: Power Query Solution

I det här blogginlägget kommer jag att gå över problem #2 från det pågående veckans problem på , där vi diskuterade hur man sorterar rörig data till en ren dimensionstabell. Detta är den andra upprepningen av detta nya initiativ som vi är värd för på LuckyTemplates. Jag brinner särskilt för den här serien eftersom den ger alla chansen att få mer träning på en regelbunden basis. Du kan se hela videon av denna handledning längst ner på den här bloggen.

Den första onsdagen i varje månad finns det en DAX-utmaning och den tredje onsdagen finns det en power query-utmaning .

Det här är ett utmärkt tillfälle att utforska, upptäcka och lära dig nya saker om dessa språk som du behöver använda i LuckyTemplates.

Du hittar en kategori om veckans problem på LuckyTemplates-forumet.

Det primära fokus ligger på processen och inte så mycket på resultatet.

Om du lyckas bryta ner ett problem i mindre bitar, kommer du att kunna lösa alla problem som du ställs inför.

Melissa de Korte

Brian talade tidigare om en teknik som kallas gummianka-felsökning . Om du missade det, se till att kolla in hans video. Det kan hjälpa dig när du fastnar för dessa utmaningar.

Låt oss först undersöka uppgiften. Vi har en rörig textfil som vi behöver förvandla till en ordentlig dimensionstabell.

Nu, när jag tittar på data som denna, kommer det för det mesta att handla om textrensning, ta bort oönskade tecken, trimma, använda versaler och så vidare.

Men vi måste också hämta alla rader från denna stackdata och omvandla den till en enda rad för varje land. Jag kallar det stackdata eftersom alla fältnamn upprepas i en enda kolumn för varje land.

Jag har viss erfarenhet av Excel, och en av dess mest kraftfulla funktioner enligt min åsikt är.

Innehållsförteckning

• Pivottabeller i Excel
• Använda Power Query för att skapa dimensionstabell
• Anpassad textrengöringsfunktion
• Pivotering av data
• Slutsats

Pivottabeller i Excel

Pivottabeller låter mig se data segment för segment. Beroende på vad du placerar på radsektionen, kondenserar en pivottabell varje förekomst av det segmentet till en enda rad.

Du kan sedan dela upp det ytterligare genom att dra fält till kolumnavsnittet.

Jag har skapat ett exempel på hur det här ser ut. Här har vi i stort sett samma data som vi hade i textfilen.

Och på nästa sida har jag skapat en tom pivottabell.

Nu, det jag inte visade dig är att jag har tre kolumner här istället för de två som jag redan har visat dig.

Jag ska dra mitt segment till raderna.

Eftersom kolumn 1 hade fältnamnen, kommer jag att dra det till kolumnavsnittet.

Kolumn 2 hade alla fältvärden, så jag ska dra det till värdesektionen.

Vi kan se här att pivottabellerna inte kan hantera textsträngar. Det räknar dem, men det visar att vi har ett enda värde för varje fält.

Låt oss nu ta en titt på det segmentet som jag skapade. Så jag ska gå tillbaka till Data och jag ska visa min kolumn. Du kan se att det bara är ett indexnummer som identifierar vart och ett av de separata blocken av data som fortfarande är staplade ovanpå varandra.

Så för mig kommer nyckelomvandlingen att vara en pivot av data för att få tillbaka den till det tabellformatet.

I forumet har jag sett andra sätt att hantera detta. Det finns andra sätt att komma till ett tabellformat förutom att använda en pivotåtgärd och de fungerar lika bra. Om du är intresserad av det, gå till forumet och börja utforska.

Använda Power Query för att skapa dimensionstabell

Låt oss gå igenom energifrågan och granska min lösning.

Personligen tycker jag att användargränssnittet gör ett bra jobb med att skriva huvuddelen av M-koden åt oss. Så jag tenderar att designa mina frågor med hjälp av användargränssnittet så mycket jag kan.

När frågan gör vad jag behövde göra, går jag in i den avancerade redigeraren och undersöker M-koden för att se om jag kan ändra den. Låt oss se hur det ser ut.

Detta är min basgrupp som är byggd med hjälp av användargränssnittet.

Du kan se att det finns många steg i de tillämpade stegen på höger sida.

Nu är det i sig inte ett problem, men bara genom att titta på dessa steg kan du se att det finns många transformationer som kan grupperas ihop.

Låt oss öppna den avancerade redigeraren.

Vi ser att denna fråga har 31 steg.

Jag lade också till några kommentarer till samma fråga som innehåller de 31 stegen, men jag delade upp det i avsnitt.

Det första jag gjorde var att ta bort steget för att ändra typ. Jag föreslår att du skapar en anpassad funktion för att utföra alla dessa texttransformationssteg.

Det finns många kommentarer här, men det är bara två saker jag vill lyfta fram i det här blogginlägget. Först är den anpassade funktionen för textrensningsändamål .

Det andra är pivotsteget för att förvandla denna typ av data tillbaka till ett korrekt tabellformat .

Anpassad textrengöringsfunktion

Låt oss gå tillbaka till de tidiga stadierna av att bygga ut min fråga, där jag hade alla dessa grupperade steg för att rensa upp dessa texter: kolumn 1 och den sammanslagna kolumnen.

Jag har också lagt till en extra anpassad kolumn. Dess enda syfte är att bygga ut min anpassade textrengöringsfunktion . Jag åberopade det i den sammanslagna kolumnen.

På så sätt behöver jag inte skriva funktionen på en gång, utan bygga upp den gradvis ett steg i taget, lägga till en ny transformation efter att ha granskat resultaten från föregående steg.

Låt oss titta på M-koden för textrensningsfunktionen.

Som du kan se har jag flera steg. När jag byggde den här textfunktionen gick jag fram och tillbaka mellan frågan för att titta på resultaten för att se vad som skulle byggas och vad som skulle korrigeras härnäst.

Med detta resultat utförde jag alla transformationer som jag behövde. Några av M-funktionerna som jag använde här tillhandahålls av användargränssnittet när jag byggde min första fråga, som texten. Trimfunktion. De andra funktionerna som användes var det dock inte.

Om du inte är bekant med dem kan du slå upp allai M-formelguiden online. Det här är länken dit du behöver gå.

Du hittar ett avsnitt dedikerat till energifrågor och funktioner.

Scrollar du ner hittar du avsnittet om textfunktion och varje avsnitt börjar med en översikt. Det finns en lista över alla textfunktioner i power-frågan och formel M-språk.

Om du letar efter en specifik transformation kan du slå upp den här.

Pivotering av data

Den andra delen jag ville lyfta fram när jag skapade den här dimensionstabellen är svängningen av själva data. Låt oss också titta närmare på detta.

Jag började med att lägga till ett index. Jag uppdaterade det indexet för att segmentera datablocken korrekt. Jag gjorde detta genom att returnera indexnumret för varje rad där det hade textlandet i kolumn 1, och sedan fyllde jag ner det värdet.

Allt vi behöver göra är att pivotera själva data. På transformeringsfliken hittar du pivotkolumnen. Med kolumn 1 vald, klicka på Pivot kolumn.

Den kommer att använda kolumn 1:s värden som det nya kolumnnamnet. Den vill också veta var värdena för dessa fältnamn finns. De finns i vår sammanslagna kolumn.

Om Excel kan hantera textvärden kan Power Query det också på grund av dess avancerade alternativinställning. Allt vi behöver göra är att välja Don't Aggregate för att den ska kunna hantera textvärden.

När vi klickar på OK kan vi se att vår data har pivoterats.

Slutsats

Bilden ovan är den slutliga resultatfrågan. Jag hoppas att du har njutit av hur vi har format om den röriga informationen i den medföljande textfilen och förvandlat den till en rensning som är lämplig för analys.

Om du har gillat det här blogginlägget, prenumerera på LuckyTemplates-kanalen så att du inte missar något nytt innehåll.

Jag hoppas att få se er alla i framtida Veckans problem-utmaningar.

Melissa