Power Query Modulo: Hur man hanterar staplade data

I den här handledningen ska jag demonstrera hur du kan omvandla rörig staplad data till ett mer organiserat format. Det finns ett ganska enkelt sätt att göra detta, och det är ett som kommer att vara otroligt användbart och har många olika applikationer. Vi kommer att använda Power Query-modulen. Du kan se hela videon av denna handledning längst ner på den här bloggen.

Min fru kom ner till kontoret och sa att hon hade kopierat ett gäng adresser från en kalenderinbjudan och det slutade med att det klistrades in i det här formatet. Hon hade 28 e-postadresser och namn som hamnade i den här typen av konstiga, staplade format. Det var en lista med namn med en massa citattecken, kommatecken, skräp och e-postadresser. Hon ville veta hur man använder Power Query för att få det till ett bättre format.

Så låt oss hoppa in i Power Query, så ser vi rådata. Låt oss behålla rådata intakt och bara referera till den. Vi kallar denna referensfil, TestData .

Det första vi ska göra är att lägga till ett index och det spelar egentligen ingen roll för dessa ändamål om det är ett nollbaserat index eller ett enbaserat index. Vi kommer bara att använda ett nollbaserat index för denna demonstration. Det är lätt att klicka utan att behöva gå igenom några andra alternativ.

Detta kommer att involvera en pivot eftersom en pivot tar rader och förvandlar dem till kolumner. I det här fallet vill vi ta den här uppsättningen rader och omvandla dem till tre kolumner – en för förnamnet, en för efternamnet och en för e-postadressen.

Vi kommer att pivotera dem i kolumnerna, men vi måste ha ett sätt för pivoten att veta vilket element som går in i vilken kolumn. Det är här Power Query Modulo kommer in.

Innehållsförteckning

• Hantera staplade data med Power Query Modulo
• Slutsats

Hantera staplade data med Power Query Modulo

Modulo ger oss resten när vi delar ett visst tal. För att komma åt Modulo går vi till fliken Lägg till kolumn, då hittar vi Standard, klickar på rullgardinspilen och scrollar ner.

När vi klickar på Modulo får vi en popup-ruta som frågar efter antalet kolumner vi vill ha. I det här fallet vill vi ha tre kolumner.

Om vi ​​delar tre med tre får vi en med en återstod av noll. Tre till fyra ger en återstod av ett, tre till fem ger en återstod av två, och sedan tre på sex ger oss en återstod av noll. Så det producerar detta trevliga upprepande mönster som vi kan använda för vår pivot.

Men innan vi gör det, låt oss rensa upp data. Det finns två sätt vi kan göra detta. En är att vi helt enkelt kan byta ut värderingar. Till exempel, för citat, kan vi bara lägga citatet där och sedan ersätta det med ingenting. Det kommer att ta och rensa ut dessa citat.

Det andra sättet att göra detta där vi kan städa upp det på en gång och spara några steg är att använda en funktion som heter Text.Remove . Först lägger vi till en anpassad kolumn och sedan listar vi alla värden som vi vill bli av med.

Som du kan se rensar det ut allt snyggt och enkelt. Och så, nu kan vi bli av med den första kolumnen och sedan flytta över den anpassade kolumnen.

Vi kan nu göra vårt pivot rent. Vi tar på oss Modulo, går till Transform och sedan Pivot Column, och vi kommer att pivotera på Custom, men vi vill inte att det ska aggregeras. Så vi väljer Don't Aggregate och klickar på OK.

Vi ersätter det bara med dessa värden i kolumnen. Det skapar sedan dessa tre kolumner åt oss. Vi kan se efternamn, förnamn och e-postadress där, men det är inte precis det format vi vill ha.

För att fixa det kan vi ta dessa kolumner och sedan göra en Fill Up .

Och nu har vi dessa extra rader på grund av det upprepade mönstret hos Modulo.

Vi kan enkelt bli av med det genom att ta bort alternativa rader. Vi kan göra det i alternativet Ta bort rader.

Vi kan säga att den första raden att ta bort är två. Ta sedan bort den andra och tredje raden och behåll sedan den fjärde och gör det som ett upprepande mönster.

Du kan nu se att vi har rensat all korrekt data på ett bra sätt och vi har fortfarande de 28 ursprungliga e-postadresserna.

Nu kan vi bli av med Index-kolumnen och byta namn på kolumnrubrikerna därefter.

Slutsats

Du kan se att med bara Modulo-tricket, och sedan svänga data och med lite rengöring, är det ganska enkelt att göra. När du väl fått kläm på det tar det förmodligen bara ungefär en minut att göra det hela.

Så förhoppningsvis har detta gett dig några fler idéer och verktyg. Du kan också kolla in min nästa handledning . I det kommer vi att ta en ytterligare titt på några av de saker vi täckte i den här handledningen och även titta på hur vi kan automatisera den här processen på ett repeterbart sätt med hjälp av anpassade funktioner. Jag ska också visa dig några coola knep för att felsöka dina anpassade funktioner.

Jag hoppas att du tyckte att det var till hjälp. Du kan se hela videon nedan och kolla in länkarna nedan för mer relaterat innehåll.

Med vänliga hälsningar!