M-kod i Power Query Anpassade kolumner | Lucky Templates

I dagens blogg kommer jag att presentera lösningen på LuckyTemplates, inklusive ett enkelt och generaliserbart ramverk för att integrera M-kod i en anpassad Power Query-kolumn. Du kan se hela videon av denna handledning längst ner på den här bloggen.

Att kunna skapa kolumner i Power Query med hjälp av M-kod ökar din förmåga att lösa mer komplexa problem.

Innan vi går in på lösningen, låt oss se över problemets natur. Jag har gett en stor datamängd från Kaggle med 93 års Oscar-data. Vi ville dra ut den bästa skådespelerskan, vinnarna och särskilt de kvinnor som vunnit mer än ett pris för bästa skådespelerska och sedan visualisera med hjälp av Queryon Timeline året då de vann sitt andra pris.

Jag hade ett antal mål när jag satte ihop det här problemet. Vad vill vi att problemet ska fokusera på och vilka vill vi att huvudmålen ska vara?

Och i den här var det tre av dem.

Den första var att lära sig eller utveckla lite övningar med Group By and All Rows-konstruktionen . Det är något som jag verkligen har fokuserat på eftersom det löser ett brett spektrum av problem helt enkelt och ofta eliminerar behovet av mycket komplex DAX.

Det andra målet är särskilt för personer som inte var vana vid att arbeta i M. Jag ville skapa ett problem som skulle utveckla en viss komfort i att skriva grundläggande M-kod . Jag ville tvinga det sättet att tänka på hur det är att utveckla grundläggande M-kod och processen att göra det.

Enligt ett kapitel i Gil Ravivs bok om Power Query kan cirka 40 % av problemen i Power Query lösas bara genom användargränssnittet. Men det, med tillägg av lite grundläggande M-kod och möjligheten att skapa anpassade kolumner, är verkligen inte så svårt. Du kan öka den andelen med över 80 %. Du kan fördubbla din problemlösningsförmåga med några grundläggande, enkla färdigheter.

Det tredje jag vill göra är att introducera Queryon Timeline anpassade visual för dem som inte är bekanta med den. Det är en som jag verkligen har kommit att älska. Jag har använt det i ett antal olika tillfällen och jag tror att det bara ger ett riktigt unikt sätt att visualisera data.

Innehållsförteckning

• Lösningsinställning till veckans problem #9
• Använder Group By In Power Query
• Inkluderar Table.AddIndexColumn M-kod
• Visualisera med Queryon Timeline Custom Visual
• Slutsats

Lösningsinställning till veckans problem #9

När jag tänkte på det här problemet ville jag göra ett tankeexperiment. Tänk om jag istället för en CSV-fil gav dig 93 papperslappar (en med varje års vinnare av Bästa kvinnliga huvudroll), hur skulle lösningen se ut?

Din lösning skulle förmodligen se ut ungefär som den jag har nedan.

Jag tror att alla skulle ha en relativt liknande lösning på det. När du väl har fått den konceptuella lösningen är det bara att ta reda på hur du får Power Query att exekvera vart och ett av dessa steg.

Och så, jag ska bara ta det här som en bred översikt och sedan gå igenom hur de operationaliserar denna konceptuella lösning till något i Power Query. Tillvägagångssättet vi kommer att använda är vad Melissa, vår inhemska Power Query-guru, refererar till som att lära sig genom att googla.

Om vi ​​nu går tillbaka till vår konceptuella modell, inkluderar de första två stegen att sortera lapparna i grupper efter skådespelerska och kasta ut vilken grupp som helst med bara en lapp på den. Så vi vill göra en räkning. Om vi ​​inte vet hur man gör det kan vi gå in på Google och försöka söka efter en term. I det här exemplet försökte jag "grupp i kraftfråga". Med det kan vi hitta allt vi behöver veta om hur man gör denna gruppering.

Använder Group By In Power Query

Sedan kan vi hoppa in i Power Query och börja operationalisera vår lösning. Det jag har gjort är att i denna Oscars tabell har jag precis gått igenom och hämtat CSV-filen och filtrerat ner till vinnarna av bästa kvinnliga huvudroll för varje år. Så vi har bara de 93 raderna med bara namn och filmens år.

Om vi ​​går in i Power Query och går till den tabellen, är det första vi vill göra denna Group By .

Jag har alltid gillat att gå Advanced bara för att det bara ger ett lite bättre gränssnitt även om det vi gör är ganska okomplicerat.

Vi vill gruppera efter namn , låt oss sedan ha en kolumn, AllData , och det här kommer bara att bli våra Alla rader. Istället för att bara returnera aggregatet kommer det att returnera aggregatet, men med alla rader som vi vill ha kvar intakta.

Vi kommer att ha en annan kolumn som heter Räkna och den vill vi ha när vi ska Räkna rader . Vi vill hitta antalet lappar i varje hög. Sedan klickar vi på OK.

Vi kan sedan klicka på detta vita utrymme i tabellen, och vi får bara en lapp.

Men sedan för de som har flera räkningar, kommer du att se att vi får alla poster.

Därefter vill vi filtrera bort de (1). Så vi slänger i princip alla högar som bara har ett namn. Välj 1 och klicka på OK.

Och vi är nu nere på de 14 pristagarna. Och vi är nu nere på de 14 pristagarna. Om vi ​​tar och utökar det kan vi ta ut Name eftersom vi redan har Name här och bara behålla year_film .

Vi är nu nere på 30 rader eftersom några av dessa kommer att ha flera vinster. Och vi kan faktiskt till och med bli av med Count- kolumnen eftersom vi redan har kastat ut 1:orna med bara en.

Nu ska vi gå till nästa fas av vår konceptuella modell, som är att sortera lappar i varje grupp efter stigande datum och sedan välja den andra dagen som inträffar inom varje grupp.

Sortering är lätt. Vi kan gå tillbaka till Power Query och göra enav skådespelerska och sedan efter datum.

Och den visar dig en etta och en tvåa.

Så vi är väl sorterade och nu vill vi ha som ett index eller en ranking som har för var och en och sedan kunna ta den andra i varje hög.

Inkluderar Table.AddIndexColumn M-kod

Och så nu är vi tillbaka till vår "Learned by Googling"-metod där vi bara kan söka rangordna eller indexera, men vi kan också gå till Microsofts referensguide . Jag har skapat ett externt verktyg som jag använder hela tiden som bara studsar dig från LuckyTemplates till Microsofts referensguide.

I Microsofts referensguide är allt grupperat efter funktion och den har en filterruta där du kan söka efter det du behöver. I det här fallet skriver vi Rank eller Index i filterrutan. Det ger oss ingenting för Rank, men för Index har vi den här Table.AddIndexColumn , som är relevant och tillämplig på det vi letar efter. Den ger också några exempel på hur man använder den här funktionen.

Så låt oss gå in på Power Query och låt oss omgruppera vårt bord efter skådespelerska.

Med det får vi de 14 vinnarna grupp efter Tabell. Och om vi går in i någon av dessa tabeller, säger Frances McDormand, kan vi se att tabellen är sorterad efter Datum.

Och sedan, här är det stora steget, som är att lägga till enanpassad kolumn och ange vår M-kod. Vi kommer att ha Table.AddIndexColumn och lägg sedan till fältet AllData . Vi kallar vår nya kolumn (som text) här som Index , och vi startar vårt Index på ett (1) och ökar det med ett (1).

Och vi får det här perfekta indexet här.

Nu kan vi ta bort vår AllData-kolumn här eftersom allt har plockats upp i. Därefter kan vi bara utöka kolumnen Anpassad, ta bort Namn, eftersom vi redan har Namn här också. Med det, vad vi har är det numrerade indexet för var och en.

Slutligen kan vi göra vad vi gjorde på Count, vilket är att filtrera bort alla de som är i den andra vinsten.

Och där går vi. Där är vår lösning.

Visualisera med Queryon Timeline Custom Visual

Det finns ett par andra saker vi vill göra här, som alla är baserade på implementeringen av Queryon Visual . Vi kommer att göra en sammanslagning och bara lägga till bildens URL.

Så vi slår samman frågor, drar upp den där skådespelerskorstabellen och vi slår samman på Namn . Vi får 14 till 14 rader som matchar, vilket är bra.

Och sedan i kolumnen Skådespelerskor expanderar vi bara och expanderar till URL:en.

Och nu har vi den här kolumnen med webbadresserna.

Det finns en annan sak som kommer med kunskapen om Queryon Timeline. Queryon fungerar bäst när det handlar om fullständiga datum snarare än år . Och så, det här är något som bara kommer med experiment, men vi kan ta kolumnen År och skapa en kolumn från exempel . Vad vi vill göra här är att bara förvandla åren till datum.

Det kommer att göra formateringen bättre. Det är något jag inte förväntar mig att du ska veta direkt, men det kommer med övning med Queryon. Vi klickar på OK, och det går in som ett datumvärde. Vi kan ta bort indextabellen eftersom vi inte behöver den längre, och vi kan sedan ändra namnet på kolumnen year_film till Year .

Låt oss nu hoppa in i visualiseringsaspekten av detta och jag vill lyfta fram bidraget från en av LuckyTemplates-medlemmarna, Jose, som verkligen gjorde ett bra jobb med att visualisera.

Om vi ​​går tillbaka till Power Query för visualiseringen är det värt att nämna att Microsoft nyligen implementerade några ändringar i sättet de certifierar anpassade bilder . Som en del av den certifieringen skärpte de användningen av externa URL-bilder.

Om du faktiskt använder versionen av Queryon Timeline som finns på appkällan (version 1.05), kommer du inte att kunna hämta in dessa URL-bilder. Och så har du nu möjlighet att använda denna 1.04, och behålla URL-bilderna, där de har på sin sida ett sätt att bädda in binära 64-bilder direkt i PBIX-filen.

Nu nämnde jag att ändra det året till ett datumfält, så vi måste koppla det till vår datamodell.

Låt oss bara ändra det från en till en till en till många och enkel riktning på korsfiltret och sedan göra detta.

Därefter måste vi för fältet bildadress se till att det i kategorin Data inte visas som okategoriserat, utan som.

Nu när vi har allt förberett, drar vi bara och släpper fälten Namn , Datum och URL . Resten av det blir riktigt okomplicerad sanering i formatalternativen.

Slutsats

Förhoppningsvis fick jag dig entusiastisk över att lägga till M-kod till din repertoar. Jag har visat dig hur du skapar en konceptuell modell och implementerar Learn by Googling-metoden. Jag har gått igenom stegen för att införliva M-kod i en anpassad kolumn och visualisera resultaten med hjälp av Queryon Timeline.

Jag hoppas att du tyckte att det var användbart. Se hela videon nedan för mer information om handledningen och kolla in länkarna nedan för mer relaterat innehåll.

Med vänliga hälsningar!