M-kode i Power-forespørgsel Brugerdefinerede kolonner | Lucky Templates

I dagens blog vil jeg præsentere løsningen til LuckyTemplates, herunder en enkel og generaliserbar ramme til inkorporering af M-kode i en tilpasset Power Query-kolonne. Du kan se den fulde video af denne tutorial nederst på denne blog.

At kunne oprette kolonner i Power Query ved hjælp af M-kode øger din evne til at løse mere komplekse problemer.

Før vi går ind i løsningen, lad os gennemgå problemets art. Jeg har givet et stort datasæt fra Kaggle med 93 års Oscar-data. Vi ønskede at trække den bedste skuespillerinde, vinderne og især de kvinder, der havde vundet mere end én pris for bedste skuespillerinde, og derefter visualisere ved hjælp af Queryon Timeline det år, hvor de vandt deres anden pris.

Jeg havde en række mål, da jeg satte dette problem sammen. Hvad ønsker vi, at problemet skal fokusere på, og hvad ønsker vi, at hovedmålene skal være?

Og i denne var der tre af dem.

Den første var at lære eller udvikle noget praksis med Group By og All Rows konstruktionen . Det er noget, jeg virkelig har fokuseret på, fordi det løser en lang række problemer ganske enkelt og ofte eliminerer behovet for nogle meget komplekse DAX.

Det andet mål er især for folk, der ikke var vant til at arbejde i M. Jeg ønskede at skabe et problem, der ville udvikle en vis komfort ved at skrive grundlæggende M-kode . Jeg ønskede at tvinge den måde at tænke på, hvordan det er at udvikle grundlæggende M-kode og processen med at gøre det.

Ifølge et kapitel i Gil Ravivs bog om Power Query kan omkring 40 % af problemerne i Power Query løses blot gennem brugergrænsefladen. Men det, med tilføjelsen af ​​noget grundlæggende M-kode og muligheden for at oprette brugerdefinerede kolonner, er virkelig ikke så svært. Du kan øge den procentdel med over 80 %. Du kan fordoble dine problemløsningsevner med nogle grundlæggende, enkle færdigheder.

Den tredje ting, jeg vil gøre, er at introducere Queryon Timeline-tilpasset visualisering til dem, der ikke er bekendt med det. Det er en, som jeg virkelig er kommet til at elske. Jeg har brugt det i en række forskellige tilfælde, og jeg synes bare, det giver en virkelig unik måde at visualisere data på.

Indholdsfortegnelse

• Løsningstilgang til ugens problem #9
• Brug af Group By In Power Query
• Inkorporerer Table.AddIndexColumn M-kode
• Visualisering ved hjælp af Queryon Timeline Custom Visual
• Konklusion

Løsningstilgang til ugens problem #9

Da jeg tænkte over dette problem, ville jeg lave et tankeeksperiment. Hvad hvis jeg i stedet for en CSV-fil lige gav dig 93 lapper (en med hvert års vinder af bedste skuespillerinde), hvordan ville løsningen se ud?

Din løsning vil sandsynligvis ligne den, jeg har nedenfor.

Jeg tror, ​​at alle ville have en forholdsvis ens løsning på det. Når du først har fået den konceptuelle løsning, er det bare et spørgsmål om at finde ud af, hvordan du får Power Query til at udføre hvert af disse trin.

Og så vil jeg bare tage dette som en bred oversigt, og så gennemgå, hvordan de operationaliserer denne konceptuelle løsning til noget i Power Query. Den tilgang, vi vil bruge, er det, Melissa, vores hjemmehørende Power Query-guru, refererer til som learning by Googling.

Hvis vi nu går tilbage til vores konceptuelle model, omfatter de første to trin at sortere sedlerne i grupper efter skuespillerinde og at smide en hvilken som helst gruppe ud med kun én seddel på. Så vi vil gerne lave en optælling. Hvis vi ikke ved, hvordan man gør det, kan vi gå ind på Google og prøve at søge efter et udtryk. I dette eksempel prøvede jeg "gruppe i strømforespørgsel". Med det kan vi finde alt, hvad vi behøver at vide om, hvordan man laver denne gruppering.

Brug af Group By In Power Query

Så kan vi hoppe ind i Power Query og begynde at operationalisere vores løsning. Det, jeg har gjort, er, at i denne Oscars tabel har jeg lige gået igennem og trukket CSV-filen ind og filtreret ned til vinderne af bedste skuespillerinde for hvert år. Så vi har kun de 93 rækker med kun Navn og filmens år.

Hvis vi går ind i Power-forespørgslen og går til den tabel, er den første ting, vi vil gøre, denne gruppe efter .

Jeg har altid godt kunne lide at gå Avanceret , bare fordi det bare giver en lidt bedre grænseflade, selvom det, vi laver, er ret ligetil.

Vi vil gruppere efter navn , så lad os have en kolonne, AllData , og dette bliver bare vores Alle Rows. I stedet for kun at returnere aggregatet, vil det returnere aggregatet, men med alle de rækker, vi ønsker, stadig forbliver intakte.

Vi får endnu en kolonne kaldet Tæl , og vi vil have den, når vi skal Tæl rækker . Vi ønsker at finde antallet af sedler i hver bunke. Derefter klikker vi OK.

Vi kan så klikke på dette hvide mellemrum i tabellen, og vi får kun én lapp.

Men for dem, der har flere optællinger, vil du se, at vi får alle posterne.

Dernæst vil vi filtrere dem fra (1). Så vi smider dybest set alle de bunker, der kun har ét navn. Vælg 1, og klik på OK.

Og vi er nu nede på de 14 flere prisvindere. Og vi er nu nede på de 14 flere prisvindere. Hvis vi tager og udvider det, kan vi udtage Name , fordi vi allerede har Name her og bare beholde year_film .

Vi er nu nede på 30 rækker, fordi nogle af disse vil have flere gevinster. Og vi kan faktisk endda slippe af med Count- kolonnen, fordi vi allerede har smidt 1'erne ud med kun én.

Nu skal vi gå til næste fase af vores konceptuelle model, som er at sortere sedler i hver gruppe efter stigende dato og derefter vælge den anden dag inden for hver gruppe.

Sortering er let. Vi kan gå tilbage til Power Query og lave enaf skuespillerinde og derefter efter dato.

Og den viser dig en en og en to.

Så vi er sorteret godt, og nu vil vi gerne have et indeks eller en ranking, der har for hver enkelt, og så kunne tage den anden i hver bunke.

Inkorporerer Table.AddIndexColumn M-kode

Og så nu er vi tilbage til vores "Learned by Googling"-tilgang, hvor vi bare kan søge på rangering eller indeks, men vi kan også gå til Microsoft Reference Guide . Jeg har lavet et eksternt værktøj, som jeg bruger hele tiden, og som bare springer dig fra LuckyTemplates ind i Microsoft Reference Guide.

I Microsoft Reference Guide er alt grupperet efter funktion, og det har en filterboks, hvor du kan søge efter det, du har brug for. I dette tilfælde skriver vi Rank eller Index i filterboksen. Det giver os ikke noget for Rank, men for Index har vi denne Table.AddIndexColumn , som er relevant og anvendelig til det, vi leder efter. Den giver også nogle eksempler på, hvordan du bruger denne funktion.

Så lad os gå ind i Power Query, og lad os omgruppere vores bord efter skuespillerinde.

Dermed får vi de 14 vindere gruppe efter Tabel. Og hvis vi går ind i nogen af ​​disse tabeller, siger Frances McDormand, kan vi se, at tabellen er sorteret efter dato.

Og så er her det store skridt, som er at tilføje entilpasset kolonne og indtast vores M-kode. Vi har Table.AddIndexColumn , og tilføj derefter feltet AllData . Vi kalder vores nye kolonne (som tekst) her ind som Index , og vi starter vores indeks på én (1) og øger den med én (1).

Og vi får dette perfekte indeks her.

Nu kan vi slette vores AllData-kolonne her, da alt er samlet op i. Dernæst kan vi bare udvide kolonnen Brugerdefineret ud, tage Navn ud, for vi har allerede Navn her også. Med det, hvad vi har, er det nummererede indeks for hver enkelt.

Til sidst kan vi gøre, hvad vi gjorde på Count, som er at filtrere alle dem fra, der er i den anden sejr.

Og der går vi. Der er vores løsning.

Visualisering ved hjælp af Queryon Timeline Custom Visual

Der er et par andre ting, vi ønsker at gøre her, som alle er baseret på implementeringen af ​​Queryon Visual . Vi vil lave en fletning og blot tilføje billed-URL'en.

Så vi fusionerer forespørgsler, trækker denne skuespillerinder- tabel frem, og vi fusionerer på Navn . Vi får 14 til 14 rækker, der matcher, hvilket er godt.

Og så udvider vi i kolonnen Skuespillerinder, og vi udvider til URL'en.

Og nu har vi denne kolonne med URL'erne.

Der er en anden ting, der følger med viden om Queryon-tidslinjen. Queryon fungerer bedst, når det handler om fulde datoer i stedet for år . Og så er dette noget, der bare kommer med eksperimenter, men vi kan tage kolonnen År og oprette en kolonne fra eksempler . Det, vi vil gøre her, er bare at omdanne årene til datoer.

Det vil gøre formateringen bedre. Det er noget, jeg ikke ville forvente, at du ved med det samme, men det kommer med øvelse med Queryon. Vi klikker OK, og det går ind som en datoværdi. Vi kan slette indekstabellen, fordi vi ikke har brug for den længere, og vi kan så ændre navnet på kolonnen år_film til År .

Lad os nu springe ind i visualiseringsaspektet af dette, og jeg vil fremhæve indgangen fra et af LuckyTemplates-medlemmerne, Jose, som virkelig gjorde et godt stykke arbejde med at visualisere.

Når vi går tilbage til Power Query for visualiseringen, er det værd at nævne, at Microsoft for nylig implementerede nogle ændringer af den måde, de certificerer tilpassede visuals på . Som en del af den certificering strammede de op på brugen af ​​eksterne URL-billeder.

Hvis du rent faktisk bruger den version af Queryon Timeline, der er på App Source (version 1.05), vil du ikke være i stand til at trække disse URL-billeder ind. Og så har du nu muligheden for at bruge denne 1.04, og beholde URL-billederne, hvor de har på deres websted en måde at indlejre binære 64-billeder direkte i PBIX-filen.

Nu nævnte jeg at ændre det år til et datofelt, så vi skal forbinde det med vores datamodel.

Lad os bare ændre det fra én til én til én til mange , og enkelt retning på krydsfilteret, og så laver dette.

Dernæst skal vi for billed-URL-feltet sørge for, at det i datakategorien ikke vises som ukategoriseret, men som.

Nu hvor vi har forberedt alt, trækker og slipper vi bare felterne Navn , Dato og URL . Resten af ​​det bliver virkelig ligetil oprydning i formatmulighederne.

Konklusion

Forhåbentlig fik jeg dig begejstret for at tilføje M-kode til dit repertoire. Jeg har vist dig, hvordan du opsætter en konceptuel model og implementerer Learn by Googling-tilgangen. Jeg har ledt dig gennem trinene i at inkorporere M-kode i en brugerdefineret kolonne og visualisere resultaterne ved hjælp af Queryon Timeline.

Jeg håber du fandt det nyttigt. Se hele videoen nedenfor for flere detaljer om selvstudiet og tjek nedenstående links for mere relateret indhold.

Alt det bedste!