Cod M în coloanele personalizate Power Query | LuckyTemplates

În blogul de astăzi, voi prezenta soluția pentru , inclusiv un cadru simplu și generalizabil pentru încorporarea codului M într-o coloană personalizată Power Query. Puteți urmări videoclipul complet al acestui tutorial în partea de jos a acestui blog.

Posibilitatea de a crea coloane în Power Query folosind codul M crește capacitatea de a rezolva probleme mai complexe.

Înainte de a trece la soluție, să trecem în revistă natura problemei. Am oferit un set mare de date de la Kaggle de 93 de ani de date Oscar. Am vrut să scoatem cea mai bună actriță, câștigătorii și în special acele femei care au câștigat mai mult de un premiu pentru cea mai bună actriță și apoi să vizualizăm folosind Cronologia Queryon anul în care au câștigat al doilea premiu.

Am avut o serie de obiective când am pus această problemă cap la cap. Pe ce dorim să se concentreze problema și care dorim să fie obiectivele principale?

Și în acesta, erau trei.

Prima a fost să înveți sau să dezvolți o anumită practică cu constructul Group By și All Rows . Este ceva asupra căruia m-am concentrat cu adevărat, deoarece rezolvă o gamă largă de probleme destul de simplu și, de multe ori, elimină necesitatea unor DAX foarte complexe.

Al doilea obiectiv este în special pentru oamenii care nu erau obișnuiți să lucreze în M. Am vrut să creez o problemă care să dezvolte un anumit confort în scrierea codului M de bază . Am vrut să forțez acest mod de a gândi despre cum este să dezvolt codul M de bază și procesul de a face asta.

Potrivit unui capitol din cartea lui Gil Raviv despre Power Query, aproximativ 40% dintre problemele din Power Query pot fi rezolvate doar prin interfața cu utilizatorul. Dar asta, cu adăugarea unui cod M de bază și abilitatea de a crea coloane personalizate, nu este chiar atât de greu. Puteți crește acest procent cu peste 80%. Vă puteți dubla capacitățile de rezolvare a problemelor cu câteva abilități fundamentale și simple.

Al treilea lucru pe care vreau să-l fac este să introduc elementele vizuale personalizate de Queryon Timeline celor care nu sunt familiarizați cu el. Este una pe care am ajuns să o iubesc cu adevărat. L-am folosit într-un număr de cazuri diferite și cred că oferă doar o modalitate unică de vizualizare a datelor.

Cuprins

• Abordarea soluției la problema săptămânii #9
• Folosind Gruparea după în Power Query
• Încorporarea Tabelului.Codul AddIndexColumn M
• Vizualizarea utilizând interogare Timeline Custom Visual
• Concluzie

Abordarea soluției la problema săptămânii #9

Gândindu-mă la această problemă, am vrut să fac un experiment de gândire. Dacă în loc de un fișier CSV, ți-aș da doar 93 de bucăți de hârtie (una cu câștigătorul pentru cea mai bună actriță în fiecare an), cum ar arăta soluția?

Soluția ta ar arăta probabil ceva ca ceea ce am mai jos.

Cred că toată lumea ar avea o soluție relativ similară. Odată ce ai acea soluție conceptuală, atunci este doar o chestiune de a descoperi cum să faci Power Query să execute fiecare dintre acești pași.

Și așa, voi lua asta ca pe o schiță generală și apoi voi analiza modul în care operaționalizează această soluție conceptuală în ceva în Power Query. Abordarea pe care o vom folosi este ceea ce Melissa, guru-ul nostru rezident în Power Query, se referă la învățare prin Google.

Acum, dacă ne întoarcem la modelul nostru conceptual, primii doi pași includ sortarea depunerilor în grupuri în funcție de actriță și aruncarea oricărui grup cu o singură derapaj pe el. Deci, vrem să facem o numărătoare. Dacă nu știm cum să facem asta, putem intra pe Google și încercam să căutăm un termen. În acest exemplu, am încercat „grup în interogare de putere”. Cu aceasta, putem găsi tot ce trebuie să știm despre cum să facem această grupare.

Folosind Gruparea după în Power Query

Apoi putem sări în Power Query și să începem operaționalizarea soluției noastre. Ceea ce am făcut este că, în acest tabel al Oscarului, tocmai am parcurs și extras fișierul CSV și am filtrat până la câștigătorii pentru cea mai bună actriță pentru fiecare an. Deci, avem doar cele 93 de rânduri din Nume și Anul filmului.

Dacă intrăm în Power Query și mergem la acel tabel, primul lucru pe care vrem să-l facem este acest Group By .

Întotdeauna mi-a plăcut să merg Advanced doar pentru că oferă o interfață puțin mai bună, chiar dacă ceea ce facem este destul de simplu.

Vrem să grupăm după Nume , apoi să avem o coloană, AllData , iar aceasta va fi doar toate rândurile noastre. În loc să returneze doar agregatul, va returna agregarea, dar cu toate rândurile pe care le dorim să rămână intacte.

Vom avea o altă coloană numită Count și o dorim când vom merge la Count Rows . Vrem să aflăm numărul de alunecări din fiecare grămadă. Apoi, facem clic pe OK.

Putem apoi să facem clic pe acest spațiu alb din tabel și obținem doar o fișă.

Dar apoi, pentru cei care au numărări multiple, veți vedea că obținem toate înregistrările.

În continuare, vrem să le filtram pe cele (1). Deci, practic, aruncăm la gunoi toate acele grămezi care au un singur nume. Selectați 1 și faceți clic pe OK.

Și acum am ajuns la cei 14 câștigători multipli de premii. Și acum am ajuns la cei 14 câștigători multipli de premii. Dacă luăm și extindem asta, putem elimina Name , deoarece avem deja Name aici și păstrăm doar year_film .

Acum am ajuns la 30 de rânduri, deoarece unele dintre acestea vor avea mai multe victorii. Și chiar putem scăpa de coloana Număr pentru că am aruncat deja pe 1 doar cu unul.

Acum trebuie să trecem la următoarea fază a modelului nostru conceptual, care este să sortăm fișele în fiecare grup după data crescătoare și apoi să alegem a doua zi care apare în fiecare grup.

Sortarea este ușoară. Putem reveni la Power Query și facem o sortare ascendentă după actriță și apoi după dată.

Și îți va arăta unul și doi.

Așa că suntem bine sortați, iar acum vrem să avem ca un index sau un clasament care să aibă pentru fiecare, și apoi să putem lua pe al doilea din fiecare grămadă.

Încorporarea Tabelului.Codul AddIndexColumn M

Și așa că acum ne-am întors la abordarea noastră „Învățat prin Google”, unde putem doar să căutăm rang sau index, dar putem accesa și Ghidul de referință Microsoft . Am creat un instrument extern pe care îl folosesc tot timpul, care doar te trimite din LuckyTemplates în Ghidul de referință Microsoft.

În Ghidul de referință Microsoft, totul este grupat după funcție și are o casetă de filtrare în care puteți căuta orice aveți nevoie. În acest caz, introducem Rank sau Index în caseta de filtru. Nu ne oferă nimic pentru Rank, dar pentru Index, avem acest Table.AddIndexColumn , care este relevant și aplicabil pentru ceea ce căutăm. De asemenea, oferă câteva exemple de utilizare a acestei funcții.

Deci, să intrăm în Power Query și să ne regrupăm tabelul după actriță.

Cu asta, obținem grupa de 14 câștigători pe masă. Și dacă intrăm în oricare dintre aceste tabele să spunem, Frances McDormand, putem vedea că tabelul este sortat după dată.

Și apoi, iată pasul cel mare, care este adăugarea unei coloane personalizate Power Query și introducerea codului nostru M. Vom avea Table.AddIndexColumn , apoi vom adăuga câmpul AllData . Vom numi noua noastră coloană (ca text) aici ca Index și vom începe Indexul nostru la unu (1) și îl vom incrementa cu unul (1).

Și obținem acest index perfect aici.

Acum, putem șterge coloana noastră AllData aici, deoarece totul este preluat în Custom . Apoi, putem extinde coloana Personalizată, scoateți Nume, deoarece avem deja și Nume aici. Cu asta, ceea ce avem este acel index numerotat pentru fiecare.

În cele din urmă, putem face ceea ce am făcut pe Count, și anume să le filtram pe toți cei care sunt în a doua victorie.

Și iată-ne. Acolo este soluția noastră.

Vizualizarea utilizând interogare Timeline Custom Visual

Mai sunt câteva lucruri pe care vrem să le facem aici, care sunt toate bazate pe implementarea Queryon Visual . Vom face o îmbinare și vom adăuga doar adresa URL a imaginii.

Deci vom îmbina interogările, vom ridica acel tabel Actresses și vom îmbina pe Nume . Obținem 14 până la 14 rânduri de potrivire, ceea ce este bine.

Și apoi, în coloana Actrițe, ne vom extinde și ne vom extinde la adresa URL.

Și acum avem această coloană cu adresele URL.

Mai există un lucru care vine odată cu cunoașterea cronologiei Queryon. Queryon funcționează cel mai bine atunci când are de-a face cu date complete și nu cu ani . Așadar, acesta este ceva care vine doar odată cu experimentarea, dar putem lua coloana An și crea o coloană din exemple . Ceea ce vrem să facem aici este doar să transformăm anii în date.

Asta va face formatarea mai bună. Este ceva ce nu m-aș aștepta să știi de la început, dar vine odată cu exersarea cu Queryon. Facem clic pe OK, iar aceasta intră ca valoare de dată. Putem șterge tabelul de index pentru că nu mai avem nevoie de el și apoi putem schimba numele coloanei year_film în Year .

Să trecem acum la aspectul de vizualizare și vreau să subliniez intrarea unuia dintre membrii LuckyTemplates, Jose, care a făcut cu adevărat o treabă grozavă în vizualizare.

Revenind la Power Query pentru vizualizare, merită menționat că Microsoft a implementat recent unele modificări în modul în care certifică imaginile personalizate . Ca parte a acestei certificări, au înăsprit utilizarea imaginilor URL externe.

Dacă utilizați de fapt versiunea cronologiei Queryon care se află în App Source (versiunea 1.05), nu veți putea extrage aceste imagini URL. Și astfel, acum aveți opțiunea de a folosi acest 1.04, păstrând imaginile URL, unde au pe site-ul lor o modalitate de a încorpora imagini binare 64 direct în fișierul PBIX.

Acum am menționat schimbarea acelui an într-un câmp de dată, așa că trebuie să-l conectăm la modelul nostru de date.

Să o schimbăm doar de la unu la unu la unul la mai mulți și Direcție unică pe filtrul încrucișat, apoi facem această relație activă .

Apoi, pentru câmpul Adresă URL a imaginii, trebuie să ne asigurăm că, în categoria Date, se afișează nu ca Necategorizat, ci ca Adresă URL a imaginii .

Acum că avem totul pregătit frumos, trebuie doar să glisăm și să plasăm câmpurile Nume , Data și URL- ul. Restul devine o curățare foarte simplă în opțiunile de format.

Power Query Table: Cum să îmbinați dinamic coloanele
Cum să îmbinați interogări în LuckyTemplates
Extrageți valori din înregistrări și liste din Power Query

Concluzie

Sper că v-am entuziasmat să adăugați cod M la repertoriul dvs. V-am arătat cum să configurați un model conceptual și să implementați abordarea Learn by Google. V-am explicat pașii pentru încorporarea codului M într-o coloană personalizată și vizualizarea rezultatelor utilizând Cronologia Queryon.

Sper că ați găsit asta util. Urmăriți videoclipul complet de mai jos pentru mai multe detalii despre tutorial și consultați linkurile de mai jos pentru mai multe conținuturi similare.

Toate cele bune!