M-Code in benutzerdefinierten Power Query-Spalten | LuckyTemplates

vorstellen , einschließlich eines einfachen und verallgemeinerbaren Frameworks für die Integration von M-Code in eine benutzerdefinierte Power Query-Spalte. Sie können sich das vollständige Video dieses Tutorials unten in diesem Blog ansehen.

Durch die Möglichkeit, Spalten in Power Query mithilfe von M-Code zu erstellen, können Sie komplexere Probleme besser lösen.

Bevor wir auf die Lösung eingehen, werfen wir einen Blick auf die Art des Problems. Ich habe einen großen Datensatz von Kaggle mit Oscar-Daten aus 93 Jahren bereitgestellt. Wir wollten die beste Schauspielerin, die Gewinnerinnen und insbesondere die Frauen herausgreifen, die mehr als einen Preis als beste Schauspielerin gewonnen hatten, und dann mithilfe der Queryon-Zeitleiste das Jahr visualisieren, in dem sie ihren zweiten Preis gewannen.

Als ich dieses Problem zusammenstellte, hatte ich mehrere Ziele. Worauf soll sich das Problem konzentrieren und was sollen die Hauptziele sein?

Und in diesem Fall waren es drei davon.

Die erste bestand darin, das Konstrukt „Group By and All Rows“ zu erlernen oder etwas Übung damit zu entwickeln . Darauf habe ich mich wirklich konzentriert, weil es eine Vielzahl von Problemen ganz einfach löst und oft die Notwendigkeit eines sehr komplexen DAX überflüssig macht.

Das zweite Ziel richtet sich insbesondere an Leute, die nicht daran gewöhnt sind, in M ​​zu arbeiten. Ich wollte ein Problem erstellen, das beim Schreiben von grundlegendem M-Code etwas Komfort bietet . Ich wollte diese Denkweise darüber erzwingen, wie es ist, grundlegenden M-Code zu entwickeln und wie dies geschieht.

Laut einem Kapitel in Gil Ravivs Buch über Power Query können etwa 40 % der Probleme in Power Query allein über die Benutzeroberfläche gelöst werden. Aber mit der Hinzufügung von etwas grundlegendem M-Code und der Möglichkeit, benutzerdefinierte Spalten zu erstellen, ist das wirklich nicht so schwierig. Sie können diesen Prozentsatz um über 80 % erhöhen. Mit einigen grundlegenden, einfachen Fähigkeiten können Sie Ihre Fähigkeiten zur Problemlösung verdoppeln.

Als Drittes möchte ich das benutzerdefinierte Queryon Timeline-Visual denjenigen vorstellen, die damit nicht vertraut sind. Es ist etwas, das ich wirklich lieben gelernt habe. Ich habe es in verschiedenen Fällen verwendet und denke, dass es einfach eine wirklich einzigartige Möglichkeit zur Datenvisualisierung bietet.

Inhaltsverzeichnis

• Lösungsansatz zum Problem der Woche Nr. 9
• Verwenden von „Gruppieren nach“ in Power Query
• Einbindung des Table.AddIndexColumn M-Codes
• Visualisieren mit der benutzerdefinierten Visualisierung der Queryon-Zeitleiste
• Abschluss

Lösungsansatz zum Problem der Woche Nr. 9

Als ich über dieses Problem nachdachte, wollte ich ein Gedankenexperiment durchführen. Was wäre, wenn ich Ihnen anstelle einer CSV-Datei nur 93 Zettel geben würde (jeweils einen mit der Gewinnerin der Kategorie „Beste Hauptdarstellerin“), wie würde die Lösung aussehen?

Ihre Lösung würde wahrscheinlich ungefähr so ​​aussehen, wie ich sie unten habe.

Ich denke, dass jeder eine relativ ähnliche Lösung dafür hätte. Sobald Sie diese konzeptionelle Lösung haben, müssen Sie nur noch herausfinden, wie Sie Power Query dazu bringen, jeden dieser Schritte auszuführen.

Daher nehme ich dies einfach als groben Überblick und gehe dann darauf ein, wie sie diese konzeptionelle Lösung in etwas in Power Query umsetzen. Der Ansatz, den wir verwenden werden, ist das, was Melissa, unsere ansässige Power Query-Guruin, als „Learn by Googling“ bezeichnet.

Wenn wir nun zu unserem konzeptionellen Modell zurückkehren, umfassen die ersten beiden Schritte das Sortieren der Zettel in Gruppen nach Darstellern und das Auswerfen jeder Gruppe mit nur einem Zettel darauf. Also wollen wir eine Zählung durchführen. Wenn wir nicht wissen, wie das geht, können wir bei Google versuchen, nach einem Begriff zu suchen. In diesem Beispiel habe ich „Gruppe in Power-Abfrage“ ausprobiert. Damit finden wir alles, was wir über die Durchführung dieser Gruppierung wissen müssen.

Verwenden von „Gruppieren nach“ in Power Query

Dann können wir in Power Query einsteigen und mit der Operationalisierung unserer Lösung beginnen. Was ich getan habe, ist, dass ich in der Tabelle dieses Oscars einfach die CSV-Datei durchgesehen und eingelesen und nach den Gewinnern der Kategorie „Beste Hauptdarstellerin“ für jedes Jahr gefiltert habe. Wir haben also nur die 93 Zeilen mit dem Namen und dem Jahr des Films.

Wenn wir in die Power Query gehen und zu dieser Tabelle gehen, ist das erste, was wir tun möchten, diese Group By .

Ich habe mich immer für „Erweitert“ entschieden , nur weil es einfach eine etwas bessere Benutzeroberfläche bietet, auch wenn das, was wir tun, ziemlich einfach ist.

Wir möchten nach Name gruppieren , dann erstellen wir eine Spalte, AllData , und dies wird einfach unser All Rows sein. Anstatt nur das Aggregat zurückzugeben, wird die Aggregation zurückgegeben, wobei jedoch alle gewünschten Zeilen noch intakt bleiben.

Wir werden eine weitere Spalte mit dem Namen Count haben und wir wollen sie, wenn wir zu Count Rows gehen . Wir wollen die Anzahl der Zettel in jedem Stapel ermitteln. Dann klicken wir auf OK.

Wir können dann auf diese Leerstelle in der Tabelle klicken und erhalten nur einen Zettel.

Aber für diejenigen, die mehrere Zählungen haben, werden Sie sehen, dass wir alle Datensätze erhalten.

Als nächstes wollen wir diejenigen (1) herausfiltern. Wir werfen also im Grunde alle Stapel weg, die nur einen Namen haben. Wählen Sie 1 und klicken Sie auf OK.

Und jetzt sind es nur noch 14 mehrfache Preisträger. Und jetzt sind es nur noch 14 mehrfache Preisträger. Wenn wir das nehmen und erweitern, können wir Name weglassen, weil wir hier bereits Name haben, und einfach year_film behalten .

Wir haben jetzt nur noch 30 Reihen, weil es in einigen davon mehrere Siege geben wird. Und wir können sogar die Spalte „Anzahl“ loswerden, da wir die Einsen bereits mit nur einer weggelassen haben.

Jetzt gehen wir zur nächsten Phase unseres konzeptionellen Modells über, die darin besteht, die Zettel in jeder Gruppe nach aufsteigendem Datum zu sortieren und dann den zweiten vorkommenden Tag innerhalb jeder Gruppe auszuwählen.

Das Sortieren ist einfach. Wir können zu Power Query zurückkehren und eine Sortierung aufsteigend nach Schauspielerin und dann nach Datum durchführen.

Und es zeigt Ihnen eine Eins und eine Zwei.

Wir sind also gut sortiert, und jetzt möchten wir einen Index oder eine Rangfolge haben, die für jeden einen hat, und dann in der Lage sein, den zweiten in jedem Stapel zu nehmen.

Einbindung des Table.AddIndexColumn M-Codes

Und jetzt sind wir wieder bei unserem „Learned by Googling“-Ansatz, bei dem wir einfach nach Rang oder Index suchen können, aber wir können auch zum Microsoft-Referenzhandbuch gehen . Ich habe ein externes Tool erstellt, das ich ständig verwende und das Sie einfach von LuckyTemplates in das Microsoft-Referenzhandbuch führt.

Im Microsoft-Referenzhandbuch ist alles nach Funktion gruppiert und verfügt über ein Filterfeld, in dem Sie nach dem suchen können, was Sie benötigen. In diesem Fall geben wir „Rang“ oder „Index“ in das Filterfeld ein. Es gibt uns nichts für den Rang, aber für den Index haben wir diese Table.AddIndexColumn , die für das, was wir suchen, relevant und anwendbar ist. Es enthält auch einige Beispiele für die Verwendung dieser Funktion.

Gehen wir also zu Power Query und gruppieren unsere Tabelle nach Schauspielerin neu.

Damit erhalten wir die 14 Gewinnergruppe nach Tabelle. Und wenn wir in eine dieser Tabellen gehen, sagen wir Frances McDormand, können wir sehen, dass die Tabelle nach Datum sortiert ist.

Und dann ist hier der große Schritt, nämlich das Hinzufügen einer benutzerdefinierten Power Query- Spalte und die Eingabe unseres M-Codes. Wir benötigen Table.AddIndexColumn und fügen dann das Feld AllData hinzu . Wir nennen unsere neue Spalte (als Text) hier Index , beginnen unseren Index bei eins (1) und erhöhen ihn um eins (1).

Und wir bekommen hier diesen perfekten Index.

Jetzt können wir unsere AllData-Spalte hier löschen, da alles im Custom übernommen wird . Als nächstes können wir einfach die Spalte „Benutzerdefiniert“ erweitern und „Name“ herausnehmen, da wir auch hier bereits „Name“ haben. Damit haben wir den nummerierten Index für jeden einzelnen.

Schließlich können wir das tun, was wir bei Count getan haben, nämlich alle diejenigen herauszufiltern, die im zweiten Sieg enthalten sind.

Und los geht's. Da ist unsere Lösung.

Visualisieren mit der benutzerdefinierten Visualisierung der Queryon-Zeitleiste

Wir möchten hier noch ein paar andere Dinge tun, die alle auf der Implementierung von Queryon Visual basieren . Wir führen eine Zusammenführung durch und fügen einfach die Bild-URL hinzu.

Wir führen also Abfragen zusammen, rufen die Tabelle „Schauspielerinnen“ auf und führen die Abfrage nach „Name“ zusammen . Wir erhalten eine Übereinstimmung von 14 bis 14 Reihen, was gut ist.

Und dann erweitern wir die Spalte „Schauspielerinnen“ einfach auf die URL.

Und jetzt haben wir diese Spalte mit den URLs.

Es gibt noch eine weitere Sache, die mit der Kenntnis der Queryon-Timeline einhergeht. Queryon funktioniert am besten, wenn es sich um vollständige Datumsangaben und nicht um Jahre handelt . Das ist also etwas, das beim Experimentieren entsteht, aber wir können die Spalte „Jahr“ nehmen und eine Spalte aus Beispielen erstellen . Was wir hier tun wollen, ist, die Jahre einfach in Datumsangaben umzuwandeln.

Dadurch wird die Formatierung besser. Ich würde nicht erwarten, dass Sie das auf Anhieb wissen, aber es kommt mit der Übung mit Queryon. Wir klicken auf „OK“, und das wird als Datumswert eingegeben. Wir können die Indextabelle löschen, da wir sie nicht mehr benötigen, und dann den Namen der Spalte year_film in Year ändern .

Kommen wir nun zum Visualisierungsaspekt und ich möchte den Beitrag eines der LuckyTemplates-Mitglieder, Jose, hervorheben, der bei der Visualisierung wirklich großartige Arbeit geleistet hat.

Zurück zu Power Query für die Visualisierung: Es ist erwähnenswert, dass Microsoft kürzlich einige Änderungen an der Art und Weise implementiert hat, wie benutzerdefinierte Visuals zertifiziert werden . Im Rahmen dieser Zertifizierung wurde die Verwendung externer URL-Bilder strenger eingeschränkt.

Wenn Sie tatsächlich die Version von Queryon Timeline verwenden, die sich in der App Source befindet (Version 1.05), können Sie diese URL-Bilder nicht abrufen. Und so haben Sie jetzt die Möglichkeit, diese Version 1.04 zu verwenden und die URL-Bilder beizubehalten, wo sie auf ihrer Website eine Möglichkeit haben, binäre 64-Bilder direkt in die PBIX-Datei einzubetten.

Jetzt habe ich erwähnt, dass wir dieses Jahr in ein Datumsfeld umwandeln, also müssen wir das mit unserem Datenmodell verbinden.

Ändern wir es einfach von „eins zu eins“ zu „ eins zu viele“ und „ Einzelne Richtung“ im Kreuzfilter und machen wir diese Beziehung dann aktiv .

Als Nächstes müssen wir für das Feld „Bild-URL“ sicherstellen, dass es in der Kategorie „Daten“ nicht als „Unkategorisiert“, sondern als „ Bild-URL“ angezeigt wird .

Nachdem wir nun alles gut vorbereitet haben, ziehen wir einfach die Felder „ Name“ , „ Datum “ und „ URL “ per Drag-and-Drop. Der Rest lässt sich in den Formatoptionen ganz einfach bereinigen.

Power Query-Tabelle: So führen Sie Spalten dynamisch zusammen.
So führen Sie Abfragen in LuckyTemplates
zusammen. Extrahieren Sie Werte aus Datensätzen und Listen in Power Query

Abschluss

Hoffentlich habe ich Sie dazu inspiriert, M-Code zu Ihrem Repertoire hinzuzufügen. Ich habe Ihnen gezeigt, wie Sie ein konzeptionelles Modell erstellen und den „Learn by Googling“-Ansatz umsetzen. Ich habe Sie durch die Schritte zum Einbinden von M-Code in eine benutzerdefinierte Spalte und zum Visualisieren der Ergebnisse mit Queryon Timeline geführt.

Ich hoffe, dass Sie das nützlich fanden. Sehen Sie sich das vollständige Video unten an, um weitere Einzelheiten zum Tutorial zu erfahren, und schauen Sie sich die Links unten an, um weitere verwandte Inhalte zu erhalten.

Alles Gute!