M-code in aangepaste Power Query-kolommen | Power-BI

presenteren , inclusief een eenvoudig en generaliseerbaar raamwerk voor het opnemen van M-code in een aangepaste Power Query-kolom. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

Als u kolommen kunt maken in Power Query met behulp van M-code, kunt u meer complexe problemen oplossen.

Voordat we ingaan op de oplossing, bekijken we eerst de aard van het probleem. Ik heb een grote dataset van Kaggle gegeven van 93 jaar aan Oscar-gegevens. We wilden de beste actrice, winnaars en vooral die vrouwen die meer dan één prijs voor beste actrice hadden gewonnen eruit halen en vervolgens met behulp van de Queryon-tijdlijn visualiseren in welk jaar ze hun tweede prijs wonnen.

Ik had een aantal doelstellingen bij het samenstellen van dit probleem. Waar willen we dat het probleem zich op richt en wat willen we dat de belangrijkste doelstellingen zijn?

En in deze waren het er drie.

De eerste was om wat oefening te leren of te ontwikkelen met de constructie Groeperen op en Alle rijen . Het is iets waar ik me echt op heb gericht, omdat het een breed scala aan problemen eenvoudig oplost en vaak de behoefte aan een zeer complexe DAX elimineert.

Het tweede doel is vooral bedoeld voor mensen die niet gewend waren om in M ​​te werken. Ik wilde een probleem creëren dat wat meer comfort zou bieden bij het schrijven van standaard M-code . Ik wilde die manier van denken forceren over hoe het is om basis-M-code te ontwikkelen en het proces om dat te doen.

Volgens een hoofdstuk in het boek van Gil Raviv over Power Query kan ongeveer 40% van de problemen in Power Query worden opgelost via de gebruikersinterface. Maar dat, met de toevoeging van wat standaard M-code en de mogelijkheid om aangepaste kolommen te maken, is echt niet zo moeilijk. U kunt dat percentage met meer dan 80% verhogen. U kunt uw probleemoplossend vermogen verdubbelen met enkele fundamentele, eenvoudige vaardigheden.

Het derde dat ik wil doen, is de aangepaste visual Queryon Timeline introduceren aan degenen die er niet bekend mee zijn. Het is er een waar ik echt van ben gaan houden. Ik heb het in een aantal verschillende gevallen gebruikt en ik denk dat het gewoon een heel unieke manier biedt om gegevens te visualiseren.

Inhoudsopgave

• Oplossingsaanpak voor probleem van de week #9
• Groeperen op gebruiken in Power Query
• Table.AddIndexColumn M-code opnemen
• Visualiseren met Queryon Timeline Custom Visual
• Conclusie

Oplossingsaanpak voor probleem van de week #9

Bij het nadenken over dit probleem wilde ik een gedachte-experiment doen. Wat als ik je in plaats van een CSV-bestand gewoon 93 stukjes papier zou geven (één met de winnaar van de beste actrice van elk jaar), hoe zou de oplossing eruit zien?

Uw oplossing zou er waarschijnlijk ongeveer zo uitzien als wat ik hieronder heb.

Ik denk dat iedereen daar een relatief vergelijkbare oplossing voor zou hebben. Zodra u die conceptuele oplossing heeft, is het een kwestie van uitzoeken hoe u Power Query elk van deze stappen kunt laten uitvoeren.

En dus zal ik dit als een grote schets beschouwen en dan bespreken hoe ze deze conceptuele oplossing operationaliseren tot iets in Power Query. De benadering die we gaan gebruiken, is wat Melissa, onze residente Power Query-goeroe, leren door Googlen noemt.

Als we nu teruggaan naar ons conceptuele model, omvatten de eerste twee stappen het sorteren van de slips in groepen op actrice en het weggooien van elke groep met slechts één slip erop. Dus we willen een telling doen. Als we niet weten hoe dat moet, kunnen we naar Google gaan en proberen naar een term te zoeken. In dit voorbeeld heb ik "groep in powerquery" geprobeerd. Daarmee kunnen we alles vinden wat we moeten weten over hoe we deze groepering moeten doen.

Groeperen op gebruiken in Power Query

Dan kunnen we in Power Query springen en beginnen met het operationaliseren van onze oplossing. Wat ik heb gedaan, is dat ik in de tabel van deze Oscar zojuist het CSV-bestand heb doorgenomen en erin heb gefilterd naar de winnaars van de beste actrice voor elk jaar. Dus we hebben alleen de 93 rijen van alleen Naam en het jaar van de film.

Als we naar de Power Query gaan en naar die tabel gaan, is het eerste dat we willen doen dit Group By .

Ik vond het altijd leuk om voor Geavanceerd te gaan , alleen omdat het gewoon een iets betere interface biedt, ook al is wat we doen vrij eenvoudig.

We willen groeperen op Name , laten we dan een kolom hebben, AllData , en dit worden gewoon onze All Rows. In plaats van alleen de aggregatie te retourneren, wordt de aggregatie geretourneerd, maar met alle rijen die we willen behouden.

We hebben nog een kolom met de naam Count en die willen we hebben als we naar Count Rows gaan . We willen het aantal slips in elke stapel vinden. Vervolgens klikken we op OK.

We kunnen dan op deze witte ruimte in de tabel klikken en we krijgen slechts één slip.

Maar voor degenen die meerdere tellingen hebben, zult u zien dat we alle records krijgen.

Vervolgens willen we degenen (1) eruit filteren. Dus we gooien eigenlijk al die stapels weg die maar één naam hebben. Selecteer 1 en klik op OK.

En we zijn nu bij de 14 meervoudige prijswinnaars. En we zijn nu bij de 14 meervoudige prijswinnaars. Als we dat nemen en uitbreiden, kunnen we Name verwijderen omdat we hier al Name hebben en gewoon year_film behouden .

We hebben nu nog maar 30 rijen omdat sommige hiervan meerdere overwinningen zullen hebben. En we kunnen zelfs de Count- kolom verwijderen, omdat we de 1-en al met slechts één hebben weggegooid.

Nu gaan we naar de volgende fase van ons conceptuele model, namelijk het sorteren van slips in elke groep op oplopende datum en vervolgens de tweede voorkomende dag binnen elke groep kiezen.

Sorteren is eenvoudig. We kunnen teruggaan naar Power Query en Sorteren op Oplopend op Actrice en vervolgens op Datum.

En het zal je een een en een twee laten zien.

Dus we zijn goed gesorteerd, en nu willen we een index of een rangorde hebben die voor elk heeft, en dan in staat zijn om de tweede in elke stapel te nemen.

Table.AddIndexColumn M-code opnemen

En dus zijn we nu terug bij onze "Learned by Googling"-benadering, waar we alleen kunnen zoeken op rang of index, maar we kunnen ook naar de Microsoft Reference Guide gaan . Ik heb een externe tool gemaakt die ik altijd gebruik en die je gewoon van LuckyTemplates naar de Microsoft Reference Guide stuurt.

In de Microsoft-referentiehandleiding is alles gegroepeerd op functie en heeft het een filtervak ​​waarin u kunt zoeken naar wat u nodig hebt. In dit geval typen we Rang of Index in het filtervak. Het geeft ons niets voor Rank, maar voor Index hebben we deze Table.AddIndexColumn , die relevant en van toepassing is op wat we zoeken. Het geeft ook enkele voorbeelden van het gebruik van deze functie.

Dus laten we naar Power Query gaan en onze tabel hergroeperen op actrice.

Daarmee krijgen we de 14 winnaars per tafel. En als we naar een van deze tabellen gaan, bijvoorbeeld Frances McDormand, kunnen we zien dat de tabel is gesorteerd op datum.

En dan is hier de grote stap, namelijk het toevoegen van een aangepaste Power Query- kolom en het invoeren van onze M-code. We hebben de Table.AddIndexColumn en voegen vervolgens het veld AllData toe . We noemen onze nieuwe kolom (als tekst) hier Index , en we beginnen onze Index op één (1) en verhogen deze met één (1).

En we krijgen deze perfecte index hier.

Nu kunnen we onze AllData-kolom hier verwijderen, omdat alles is opgepikt in de Custom . Vervolgens kunnen we de kolom Aangepast gewoon uitvouwen en Naam eruit halen, omdat we hier ook al Naam hebben. Daarmee hebben we die genummerde index voor elk.

Ten slotte kunnen we doen wat we deden op Count, namelijk alle spelers eruit filteren die in de tweede overwinning zitten.

En daar gaan we. Daar is onze oplossing.

Visualiseren met Queryon Timeline Custom Visual

Er zijn een aantal andere dingen die we hier willen doen, die allemaal zijn gebaseerd op de implementatie van de Queryon Visual . We gaan een samenvoeging doen en voegen gewoon de afbeeldings-URL toe.

Dus we voegen query's samen, halen die tabel Actrices op en we voegen samen op Naam . We krijgen 14 tot 14 rijen die overeenkomen, wat goed is.

En dan vouwen we in de kolom Actrices gewoon uit en breiden we uit naar de URL.

En nu hebben we deze kolom met de URL's.

Er is nog iets dat hoort bij de kennis van de Queryon-tijdlijn. Queryon werkt het beste als het gaat om volledige datums in plaats van jaren . En dus is dit iets dat gewoon met experimenteren komt, maar we kunnen de kolom Jaar nemen en een Kolom met voorbeelden maken . Wat we hier willen doen, is de jaren in datums veranderen.

Dat maakt de opmaak beter. Dat is iets waarvan ik niet verwacht dat je het meteen weet, maar het komt met oefenen met Queryon. We klikken op OK, en dat wordt ingevoerd als een datumwaarde. We kunnen de indextabel verwijderen omdat we deze niet meer nodig hebben, en we kunnen dan de naam van de kolom year_film wijzigen in Year .

Laten we nu ingaan op het visualisatieaspect hiervan en ik wil de aandacht vestigen op de inzending van een van de LuckyTemplates-leden, Jose, die echt geweldig werk heeft geleverd op het gebied van visualisatie.

Als we teruggaan naar Power Query voor de visualisatie, is het vermeldenswaard dat Microsoft onlangs enkele wijzigingen heeft doorgevoerd in de manier waarop ze aangepaste visuals certificeren . Als onderdeel van die certificering hebben ze het gebruik van externe URL-afbeeldingen aangescherpt.

Als u daadwerkelijk de versie van Queryon Timeline gebruikt die op de app-bron staat (versie 1.05), kunt u deze URL-afbeeldingen niet ophalen. En dus heb je nu de mogelijkheid om deze 1.04 te gebruiken, waarbij de URL-afbeeldingen behouden blijven, waar ze op hun site een manier hebben om binaire 64-afbeeldingen rechtstreeks in het PBIX-bestand in te sluiten.

Nu noemde ik het veranderen van dat jaar in een datumveld, dus we moeten dat koppelen aan ons datamodel.

Laten we het gewoon veranderen van een naar een naar een naar veel , en Enkele richting op het kruisfilter, en dan deze relatie actief maken .

Vervolgens moeten we er voor het afbeeldings-URL-veld voor zorgen dat het in de categorie Gegevens niet wordt weergegeven als Uncategorized, maar als Afbeeldings-URL .

Nu we alles goed hebben voorbereid, slepen we gewoon de velden Naam , Datum en URL . De rest wordt een heel eenvoudige opschoning in de formaatopties.

Power Query-tabel: Kolommen dynamisch samenvoegen
Query's samenvoegen in LuckyTemplates
Waarden extraheren uit records en lijsten in Power Query

Conclusie

Hopelijk heb ik je enthousiast gemaakt om M-code aan je repertoire toe te voegen. Ik heb je laten zien hoe je een conceptueel model opzet en de Learn by Googling-benadering implementeert. Ik heb u door de stappen geleid voor het opnemen van M-code in een aangepaste kolom en het visualiseren van de resultaten met Queryon Timeline.

Ik hoop dat je dat nuttig vond. Bekijk de volledige video hieronder voor meer informatie over de zelfstudie en bekijk de onderstaande links voor meer gerelateerde inhoud.

Al het beste!