M-kode i Power Query Egendefinerte kolonner | Lucky Templates

I dagens blogg skal jeg presentere løsningen på LuckyTemplates, inkludert et enkelt og generaliserbart rammeverk for å inkludere M-kode i en egendefinert Power Query-kolonne. Du kan se hele videoen av denne opplæringen nederst på denne bloggen.

Å kunne lage kolonner i Power Query ved hjelp av M-kode øker din evne til å løse mer komplekse problemer.

Før vi går inn på løsningen, la oss gå gjennom problemets natur. Jeg har gitt et stort datasett fra Kaggle med 93 år med Oscar-data. Vi ønsket å trekke ut den beste skuespillerinnen, vinnerne, og spesielt de kvinnene som hadde vunnet mer enn én pris for beste skuespillerinne, og deretter visualisere ved hjelp av Queryon Timeline året da de vant sin andre pris.

Jeg hadde en rekke mål når jeg satte dette problemet sammen. Hva vil vi at problemet skal fokusere på og hva vil vi at hovedmålene skal være?

Og i denne var det tre av dem.

Den første var å lære eller utvikle litt praksis med Group By and All Rows-konstruksjonen . Det er noe jeg virkelig har fokusert på fordi det løser et bredt spekter av problemer ganske enkelt og ofte eliminerer behovet for noen veldig komplekse DAX.

Det andre målet er spesielt for folk som ikke var vant til å jobbe i M. Jeg ønsket å lage et problem som ville utvikle en viss komfort i å skrive grunnleggende M-kode . Jeg ønsket å tvinge den måten å tenke på om hvordan det er å utvikle grunnleggende M-kode og prosessen med å gjøre det.

I følge et kapittel i Gil Ravivs bok om Power Query kan omtrent 40 % av problemene i Power Query løses bare gjennom brukergrensesnittet. Men det, med tillegg av litt grunnleggende M-kode og muligheten til å lage egendefinerte kolonner, er egentlig ikke så vanskelig. Du kan øke denne prosentandelen med over 80 %. Du kan doble problemløsningsevnene dine med noen grunnleggende, enkle ferdigheter.

Den tredje tingen jeg vil gjøre er å introdusere Queryon Timeline egendefinerte visualisering for de som ikke er kjent med den. Det er en jeg virkelig har begynt å elske. Jeg har brukt det i en rekke forskjellige tilfeller, og jeg tror det bare gir en veldig unik måte å visualisere data på.

Innholdsfortegnelse

• Løsning tilnærming til ukens problem #9
• Bruker Group By In Power Query
• Inkorporerer Table.AddIndexColumn M-kode
• Visualisering ved hjelp av Queryon Timeline Custom Visual
• Konklusjon

Løsning tilnærming til ukens problem #9

Når jeg tenkte på dette problemet, ønsket jeg å gjøre et tankeeksperiment. Hva om jeg i stedet for en CSV-fil bare ga deg 93 lapper (en med vinneren av hvert års beste skuespillerinne), hvordan ville løsningen se ut?

Løsningen din vil sannsynligvis se omtrent ut som det jeg har nedenfor.

Jeg tror alle ville ha en relativt lik løsning på det. Når du først har fått den konseptuelle løsningen, er det bare å finne ut hvordan du får Power Query til å utføre hvert av disse trinnene.

Så jeg tar dette som en bred oversikt, og så går jeg gjennom hvordan de operasjonaliserer denne konseptuelle løsningen til noe i Power Query. Tilnærmingen vi skal bruke er det Melissa, vår fastboende Power Query-guru, refererer til som læring ved å google.

Hvis vi nå går tilbake til vår konseptuelle modell, inkluderer de to første trinnene å sortere lappene i grupper etter skuespillerinne og kaste ut en hvilken som helst gruppe med bare en lapp på. Så vi ønsker å gjøre en telling. Hvis vi ikke vet hvordan vi gjør det, kan vi gå inn på Google og prøve å søke etter et begrep. I dette eksemplet prøvde jeg "gruppe i strømspørring". Med det kan vi finne alt vi trenger å vite om hvordan vi gjør denne grupperingen.

Bruker Group By In Power Query

Deretter kan vi hoppe inn i Power Query og begynne å operasjonalisere løsningen vår. Det jeg har gjort er at i denne Oscar-tabellen har jeg nettopp gått gjennom og hentet inn CSV-filen og filtrert ned til vinnerne av beste skuespillerinne for hvert år. Så, vi har bare de 93 radene med bare navn og år for filmen.

Hvis vi går inn i Power Query og går til den tabellen, er det første vi ønsker å gjøre denne Group By .

Jeg har alltid likt å gå Advanced bare fordi det bare gir et litt bedre grensesnitt selv om det vi gjør er ganske enkelt.

Vi ønsker å gruppere etter navn , så la oss ha en kolonne, AllData , og dette kommer bare til å bli vår Alle rader. I stedet for å returnere bare aggregatet, kommer det til å returnere aggregeringen, men med alle radene vi ønsker fortsatt intakte.

Vi vil ha en annen kolonne som heter Count og vi vil ha den når vi skal telle rader . Vi ønsker å finne antall lapper i hver haug. Deretter klikker vi OK.

Vi kan deretter klikke på denne hvite plassen i tabellen, og vi får bare én lapp.

Men for de som har flere tellinger, vil du se at vi får alle postene.

Deretter ønsker vi å filtrere ut de (1). Så vi kaster i utgangspunktet alle de haugene som bare har ett navn. Velg 1 og klikk OK.

Og vi er nå nede til de 14 prisvinnerne. Og vi er nå nede til de 14 prisvinnerne. Hvis vi tar og utvider det, kan vi ta ut Name fordi vi allerede har Name her og bare beholde year_film .

Vi er nå nede i 30 rader fordi noen av disse kommer til å ha flere gevinster. Og vi kan faktisk til og med bli kvitt Count- kolonnen fordi vi allerede har kastet ut 1-erne med bare én.

Nå skal vi gå til neste fase av vår konseptuelle modell, som er å sortere lapper i hver gruppe etter stigende dato, og deretter velge den andre dagen i hver gruppe.

Sortering er enkelt. Vi kan gå tilbake til Power Query og gjøre enav skuespillerinne og deretter etter dato.

Og den viser deg en og en to.

Så vi er sortert godt, og nå ønsker vi å ha som en indeks eller en rangering som har for hver enkelt, og så kunne ta den andre i hver haug.

Inkorporerer Table.AddIndexColumn M-kode

Og så nå er vi tilbake til vår "Lært av Googling"-tilnærmingen hvor vi bare kan søke rangering eller indeksere, men vi kan også gå til Microsoft Reference Guide . Jeg har laget et eksternt verktøy som jeg bruker hele tiden som bare spretter deg fra LuckyTemplates til Microsoft Reference Guide.

I Microsoft Reference Guide er alt gruppert etter funksjon og den har en filterboks hvor du kan søke etter det du trenger. I dette tilfellet skriver vi Rang eller Indeks i filterboksen. Det gir oss ikke noe for Rangering, men for Index har vi denne Table.AddIndexColumn , som er relevant og anvendelig for det vi leter etter. Den gir også noen eksempler på hvordan du bruker denne funksjonen.

Så la oss gå inn i Power Query og la oss omgruppere bordet vårt etter skuespillerinne.

Med det får vi de 14 vinnerne gruppe etter Tabell. Og hvis vi går inn i noen av disse tabellene si, Frances McDormand, kan vi se at tabellen er sortert etter dato.

Og så, her er det store steget, som er å legge til enegendefinert kolonne og skriv inn M-koden vår. Vi vil ha Table.AddIndexColumn , og deretter legge til feltet AllData . Vi kaller den nye kolonnen vår (som tekst) her som indeks , og vi starter indeksen på én (1) og øker den med én (1).

Og vi får denne perfekte indeksen her.

Nå kan vi slette AllData-kolonnen vår her siden alt er plukket opp i. Deretter kan vi bare utvide Egendefinert-kolonnen ut, ta ut Navn, fordi vi allerede har Navn her også. Med det, det vi har er den nummererte indeksen for hver enkelt.

Til slutt kan vi gjøre det vi gjorde på Count, som er å filtrere ut alle de som er i den andre seieren.

Og der går vi. Der er løsningen vår.

Visualisering ved hjelp av Queryon Timeline Custom Visual

Det er et par andre ting vi ønsker å gjøre her, som alle er basert på implementeringen av Queryon Visual . Vi skal gjøre en sammenslåing og bare legge til bildets URL.

Så vi slår sammen forespørsler, trekker opp den skuespillerinnetabellen , og vi slår sammen på Navn . Vi får 14 til 14 rader som matcher, noe som er bra.

Og så i Skuespillerinner-kolonnen utvider vi bare ut og vi utvider til URL-en.

Og nå har vi denne kolonnen med URL-ene.

Det er en annen ting som følger med kunnskapen om Queryon-tidslinjen. Queryon fungerer best når det handler om fullstendige datoer i stedet for år . Og så, dette er noe som bare kommer med eksperimentering, men vi kan ta år- kolonnen og lage en kolonne fra eksempler . Det vi ønsker å gjøre her er bare å gjøre årene om til datoer.

Det kommer til å gjøre formateringen bedre. Det er noe jeg ikke forventer at du skal vite med en gang, men det kommer med øvelse med Queryon. Vi klikker OK, og det går inn som en datoverdi. Vi kan slette indekstabellen fordi vi ikke trenger den lenger, og vi kan da endre navnet på kolonnen year_film til Year .

La oss nå hoppe inn i visualiseringsaspektet ved dette, og jeg vil fremheve oppføringen til et av LuckyTemplates-medlemmene, Jose, som virkelig gjorde en god jobb med å visualisere.

Når vi går tilbake til Power Query for visualiseringen, er det verdt å nevne at Microsoft nylig implementerte noen endringer i måten de sertifiserer egendefinerte bilder på . Som en del av den sertifiseringen strammet de ned på bruken av eksterne URL-bilder.

Hvis du faktisk bruker versjonen av Queryon Timeline som er på appkilden (versjon 1.05), kommer du ikke til å kunne hente inn disse URL-bildene. Og så har du nå muligheten til å bruke denne 1.04, og beholde URL-bildene, der de har på siden deres en måte å legge inn binære 64-bilder direkte i PBIX-filen.

Nå nevnte jeg å endre det året til et datofelt, så vi må koble det til datamodellen vår.

La oss bare endre det fra én til én til én til mange , og enkeltretning på kryssfilteret, og så gjør dette.

Deretter, for bilde-URL-feltet, må vi sørge for at i datakategorien vises det ikke som ukategorisert, men som.

Nå som vi har alt godt forberedt, drar og slipper vi Name , Date og URL- feltene. Resten av det blir veldig grei opprydding i formatalternativene.

Konklusjon

Forhåpentligvis fikk jeg deg begeistret for å legge til M-kode til repertoaret ditt. Jeg har vist deg hvordan du setter opp en konseptuell modell og implementerer Learn by Googling-tilnærmingen. Jeg har ledet deg gjennom trinnene for å inkludere M-kode i en egendefinert kolonne og visualisere resultatene ved hjelp av Queryon Timeline.

Jeg håper du fant det nyttig. Se hele videoen nedenfor for mer informasjon om opplæringen og sjekk ut koblingene nedenfor for mer relatert innhold.

Beste ønsker!