M kód vo vlastných stĺpcoch Power Query | LuckyTemplates

V dnešnom blogu predstavím riešenie LuckyTemplates, vrátane jednoduchého a zovšeobecniteľného rámca na začlenenie M kódu do vlastného stĺpca Power Query. Celé video tohto návodu si môžete pozrieť v spodnej časti tohto blogu.

Možnosť vytvárať stĺpce v Power Query pomocou kódu M zvyšuje vašu schopnosť riešiť zložitejšie problémy.

Skôr než sa pustíme do riešenia, zopakujme si podstatu problému. Dal som veľký súbor údajov od Kaggle s údajmi o Oscaroch za 93 rokov. Chceli sme vybrať najlepšiu herečku, víťazky a najmä tie ženy, ktoré získali viac ako jednu cenu za najlepšiu herečku, a potom pomocou časovej osi Queryon vizualizovať rok, v ktorom získali svoje druhé ocenenie.

Pri zostavovaní tohto problému som mal niekoľko cieľov. Na čo chceme, aby sa problém zameral a aké chceme, aby boli hlavné ciele?

A v tomto boli traja.

Prvým bolo naučiť sa alebo rozvíjať nejakú prax s konštruktom Group By a All Rows . Je to niečo, na čo som sa skutočne sústredil, pretože to rieši širokú škálu problémov celkom jednoducho a často to eliminuje potrebu nejakého veľmi zložitého DAX.

Druhý cieľ je určený najmä pre ľudí, ktorí neboli zvyknutí pracovať v M. Chcel som vytvoriť problém, ktorý by zvýšil pohodlie pri písaní základného M kódu . Chcel som prinútiť tento spôsob uvažovania o tom, aké to je vyvinúť základný M kód a proces, ako to urobiť.

Podľa kapitoly v knihe Gila Raviva o Power Query možno asi 40 % problémov v Power Query vyriešiť práve cez používateľské rozhranie. Ale to, s pridaním nejakého základného M kódu a schopnosti vytvárať vlastné stĺpce, naozaj nie je také ťažké. Toto percento môžete zvýšiť o viac ako 80%. Svoje schopnosti riešenia problémov môžete zdvojnásobiť pomocou niekoľkých základných, jednoduchých zručností.

Tretia vec, ktorú chcem urobiť, je predstaviť vlastný vizuál časovej osi Queryon tým, ktorí ho nepoznajú. Je to ten, ktorý som si naozaj zamiloval. Použil som ho v mnohých rôznych prípadoch a myslím si, že poskytuje skutočne jedinečný spôsob vizualizácie údajov.

Obsah

• Riešenie Prístup k problému týždňa #9
• Používanie Zoskupiť podľa v Power Query
• Incorporating Table.AddIndexColumn M Code
• Vizualizácia pomocou vlastnej vizuálnej časovej osi Queryon
• Záver

Riešenie Prístup k problému týždňa #9

Pri premýšľaní o tomto probléme som chcel urobiť myšlienkový experiment. Čo keby som vám namiesto súboru CSV dal len 93 papierikov (jeden s víťazom v kategórii najlepšia herečka za rok), ako by vyzeralo riešenie?

Vaše riešenie by pravdepodobne vyzeralo asi ako to, čo mám nižšie.

Myslím, že každý by mal relatívne podobné riešenie. Keď už budete mať toto koncepčné riešenie, potom už zostáva len prísť na to, ako prinútiť Power Query vykonať každý z týchto krokov.

A tak to vezmem len ako široký náčrt a potom si prejdem, ako sfunkčnia toto koncepčné riešenie do niečoho v Power Query. Prístup, ktorý použijeme, je to, čo Melissa, náš miestny guru Power Query, označuje ako učenie pomocou Googlu.

Ak sa teraz vrátime k nášmu konceptuálnemu modelu, prvé dva kroky zahŕňajú triedenie lístkov do skupín podľa herečky a vyhodenie akejkoľvek skupiny, na ktorej je len jeden lístok. Takže chceme spočítať. Ak nevieme, ako na to, môžeme ísť do Google a skúsiť vyhľadať výraz. V tomto príklade som vyskúšal „skupinu v silovom dotaze“. Vďaka tomu môžeme nájsť všetko, čo potrebujeme vedieť o tom, ako urobiť toto zoskupenie.

Používanie Zoskupiť podľa v Power Query

Potom môžeme skočiť do Power Query a začať prevádzkovať naše riešenie. To, čo som urobil, je, že v tejto oscarovej tabuľke som práve prešiel a stiahol súbor CSV a prefiltroval som sa na víťazov za najlepší ženský herecký výkon za každý rok. Takže máme len 93 riadkov len s názvom a rokom filmu.

Ak prejdeme do Power Query a prejdeme k tejto tabuľke, prvá vec, ktorú chceme urobiť, je táto skupina podľa .

Vždy som rád šiel na pokročilý len preto, že poskytuje o niečo lepšie rozhranie, aj keď to, čo robíme, je celkom jednoduché.

Chceme zoskupiť podľa Name , potom dáme stĺpec AllData a toto budú len naše Všetky riadky. Namiesto vrátenia iba súhrnu vráti agregáciu, ale všetky riadky, ktoré chceme, zostanú nedotknuté.

Budeme mať ďalší stĺpec s názvom Count a chceme ho, keď ideme na Count Rows . Chceme nájsť počet prepadov v každej hromade. Potom klikneme na tlačidlo OK.

Potom môžeme kliknúť na toto biele miesto v tabuľke a dostaneme len jeden lapsus.

Ale potom pre tie, ktoré majú viacero počtov, uvidíte, že získame všetky záznamy.

Ďalej chceme odfiltrovať tie (1). Takže v podstate likvidujeme všetky tie kôpky, ktoré majú len jedno meno. Vyberte 1 a kliknite na OK.

A teraz sa dostávame k 14 viacnásobným víťazom ocenení. A teraz sa dostávame k 14 viacnásobným víťazom ocenení. Ak to vezmeme a rozšírime, môžeme odstrániť Meno , pretože Meno tu už máme a ponecháme si year_film .

Teraz máme 30 riadkov, pretože niektoré z nich budú mať viacero výhier. A vlastne sa môžeme zbaviť aj stĺpca Count , pretože sme už vyhodili jedničky len s jedným.

Teraz prejdeme k ďalšej fáze nášho koncepčného modelu, ktorou je triedenie lístkov v každej skupine podľa vzostupného dátumu a potom výber druhého vyskytujúceho sa dňa v rámci každej skupiny.

Triedenie je jednoduché. Môžeme sa vrátiť do Power Query a urobiť apodľa herečky a potom podľa dátumu.

A ukáže vám jednotku a dvojku.

Takže sme dobre zoradení a teraz chceme mať ako index alebo rebríček, ktorý má pre každý z nich, a potom byť schopný vziať druhý z každej kôpky.

Incorporating Table.AddIndexColumn M Code

A tak sa teraz vraciame k nášmu prístupu „Learned by Googling“, kde môžeme len vyhľadávať podľa hodnotenia alebo indexu, ale môžeme tiež prejsť na referenčnú príručku spoločnosti Microsoft . Vytvoril som externý nástroj, ktorý neustále používam a ktorý vás odkáže z LuckyTemplates do referenčnej príručky spoločnosti Microsoft.

V referenčnej príručke od spoločnosti Microsoft je všetko zoskupené podľa funkcií a obsahuje pole filtra, v ktorom môžete vyhľadať čokoľvek, čo potrebujete. V tomto prípade do poľa filtra napíšeme Rank alebo Index . Pre Rank nám to nič nedáva, ale pre Index máme tento Table.AddIndexColumn , ktorý je relevantný a použiteľný pre to, čo hľadáme. Poskytuje tiež niekoľko príkladov použitia tejto funkcie.

Poďme teda do Power Query a preskupme náš stôl podľa herečky.

Takto dostaneme 14 víťazných skupín podľa tabuľky. A ak vstúpime do ktorejkoľvek z týchto tabuliek, povedzme, Frances McDormand, vidíme, že tabuľka je zoradená podľa dátumu.

A potom je tu veľký krok, ktorým je pridanie avlastný stĺpec a zadajte náš M kód. Budeme mať Table.AddIndexColumn , potom pridáme pole AllData . Náš nový stĺpec (ako text) tu nazveme ako Index a index začneme na jednotke (1) a zvýšime ho o jednu (1).

A tu máme tento dokonalý index.

Teraz tu môžeme vymazať náš stĺpec AllData, pretože všetko je uložené v. Ďalej môžeme len rozbaliť stĺpec Vlastné a vybrať Meno, pretože meno tu už máme. To, čo máme, je očíslovaný index pre každý z nich.

Nakoniec môžeme urobiť to, čo sme urobili na Count, čiže odfiltrovať všetky, ktoré sú v druhej výhre.

A ideme. Tu je naše riešenie.

Vizualizácia pomocou vlastnej vizuálnej časovej osi Queryon

Je tu niekoľko ďalších vecí, ktoré tu chceme urobiť, pričom všetky sú založené na implementácii Queryon Visual . Urobíme zlúčenie a pridáme len adresu URL obrázka.

Takže zlúčime dopyty, vytiahneme tabuľku Herečky a zlúčime sa na Meno . Získame 14 až 14 zodpovedajúcich riadkov, čo je dobré.

A potom v stĺpci Herečky sa len rozbalíme a rozbalíme na adresu URL.

A teraz máme tento stĺpec s adresami URL.

So znalosťou časovej osi Queryonu prichádza ešte jedna vec. Queryon funguje najlepšie, keď sa zaoberá úplnými dátumami a nie rokmi . Toto je niečo, čo prichádza len s experimentovaním, ale môžeme použiť stĺpec Rok a vytvoriť stĺpec z príkladov . To, čo tu chceme urobiť, je zmeniť roky na dátumy.

Vďaka tomu bude formátovanie lepšie. To je niečo, čo by som nečakal, že to budete vedieť hneď na začiatku, ale prichádza to s praxou s Queryonom. Klikneme na tlačidlo OK a zobrazí sa ako hodnota dátumu. Indexovú tabuľku môžeme vymazať, pretože ju už nepotrebujeme, a potom môžeme zmeniť názov stĺpca year_film na Year .

Prejdime teraz k aspektu vizualizácie a chcem zdôrazniť príspevok jedného z členov LuckyTemplates, Joseho, ktorý pri vizualizácii skutočne odviedol skvelú prácu.

Keď sa vrátime do Power Query pre vizualizáciu, stojí za zmienku, že Microsoft nedávno implementoval niektoré zmeny v spôsobe certifikácie vlastných vizuálov . V rámci tejto certifikácie sprísnili používanie externých obrázkov URL.

Ak skutočne používate verziu časovej osi dotazu, ktorá je v zdroji aplikácie (verzia 1.05), nebudete môcť stiahnuť tieto obrázky adries URL. A tak máte teraz možnosť použiť túto verziu 1.04 a ponechať si obrázky URL, kde majú na svojich stránkach spôsob, ako vložiť binárne obrázky 64 priamo do súboru PBIX.

Teraz som spomenul zmenu tohto roku na dátumové pole, takže to musíme prepojiť s naším dátovým modelom.

Zmeňme to z jednej na jednu na jednu na veľa a jeden smer na krížovom filtri a potom urobme toto.

Ďalej sa v prípade poľa adresy URL obrázka musíme uistiť, že v kategórii Údaje sa nezobrazuje ako Nezaradené, ale ako.

Teraz, keď máme všetko pekne pripravené, jednoducho pretiahneme polia Názov , Dátum a URL . Zvyšok sa stáva skutočne jednoduchým vyčistením v možnostiach formátu.

Záver

Dúfam, že som vás nadchol pridaním M kódu do vášho repertoáru. Ukázal som vám, ako nastaviť koncepčný model a implementovať prístup Learn by Googling. Previedol som vás krokmi pri začlenení M kódu do vlastného stĺpca a vizualizácii výsledkov pomocou časovej osi Queryon.

Dúfam, že to bolo pre vás užitočné. Pozrite si celé video nižšie, kde nájdete ďalšie podrobnosti o návode, a pozrite si odkazy nižšie, kde nájdete ďalší súvisiaci obsah.

Všetko najlepšie!