Entpivotieren Sie mehrere Spalten dynamisch in LuckyTemplates

In diesem Tutorial befassen wir uns noch einmal mit einem Thema, das ich zuvor behandelt habe, nämlich dem Aufheben der Pivotierung mehrerer Spalten in Power Query mithilfe von List.Zip. Ein LuckyTemplates-Mitglied fragte im , wie man diese Methode dynamisch machen könne. Sie können sich das vollständige Video dieses Tutorials unten in diesem Blog ansehen.

Ich habe dies als „fortgeschritten“ bezeichnet, da wir eine Reihe von M-Funktionen schreiben werden und einige davon möglicherweise sogar neu für Sie sind.

Nachdem dies gesagt ist, gehen wir zu Power Query über.

Inhaltsverzeichnis

• Daten im Tabellenformat
• Code ändern, um mehrere Spalten dynamisch zu entpivotieren
• Dynamisches Aufheben der Pivotierung mehrerer Spalten: Hinzufügen einer benutzerdefinierten Spaltenlogik
• Abschluss

Daten im Tabellenformat

Wie Sie sehen, wurden diese Daten bereits in ein geeignetes Tablet-Format umgewandelt, das für die Analyse geeignet ist. Obwohl dieser Prozess sehr wichtig ist, wird er hier nicht behandelt, wir gehen jedoch kurz auf jeden Schritt ein.

Im Quellschritt sehen wir eine ständige Wiederholung der Wertepaare, bei denen es sich um die Stunden und Kontakte für jedes der oben aufgeführten Attribute handelt.

Unter „Angewandte Schritte“ können Sie die restlichen Schritte sehen. Wir haben einige obere Zeilen entfernt, die leeren Spalten entfernt, unsere Textwerte bereinigt und sie nach unten gefüllt. Dann haben wir unsere Daten transponiert, den leeren Datensatz entfernt, die Spalten eins und zwei zusammengeführt, unsere Textwerte erneut bereinigt und unsere Daten zurück transponiert.

Wir haben einen benutzerdefinierten Schritt erstellt, den wir später genauer untersuchen werden, Werte aus unserer Liste extrahiert und schließlich den Typ geändert.

Schauen wir uns den gesamten Code an. Was uns hier interessiert, ist die List.Zip- Funktion. Kurze Zusammenfassung: Es wird eine einzelne Liste als Parameter verwendet und dann eine Liste zurückgegeben. Als Ergebnis sehen wir, dass diese einzelne Liste in diesem Fall drei separate Listen enthält.

Wenn wir die erste Liste untersuchen, enthält sie hartcodierte Attributnamen, nur eingegebene Textwerte. Die zweite Liste enthält hartcodierte Spaltenverweise für die Stundenwerte. Die endgültige Liste enthält hartcodierte Spaltenreferenzen für die Kontaktwerte.

List.Zip nimmt dann ein Element aus jeder dieser drei Listen und kombiniert sie basierend auf ihrer Position in einer neuen Liste. Beispielsweise wird jedes erste Element dieser Listen zu einer neuen Liste zusammengefasst. Dasselbe geschieht für alle Werte, die in dieser Liste an zweiter Stelle aufgeführt sind, und so weiter.

Hartcodierte Werte können problematisch sein, wenn es unwahrscheinlich ist, dass sich die Attribute im Laufe der Zeit ändern. Wenn also beim nächsten Mal nicht alle Artikel vorhanden sind oder neue Artikel vorhanden sind, kann es zu Problemen kommen.

Code ändern, um mehrere Spalten dynamisch zu entpivotieren

Wie machen wir das also dynamisch?

Als Erstes gehe ich den Code durch, um zu sehen, ob es eine Stelle gibt, an der wir diese Attributwerte abrufen können, ohne dafür einen separaten Schritt entwerfen zu müssen. Wir gehen also einen Schritt zurück durch den Code und dieser RemoveNull- Schritt ist perfekt.

Sie können sehen, dass hier in Spalte eins alle Attributwerte aufgelistet sind. Wir können sie über die Benutzeroberfläche extrahieren.

Klicken Sie also mit der rechten Maustaste auf die Überschrift und wählen Sie unten „ Als neue Abfrage hinzufügen“ aus .

Wir erhalten diese Liste und das sind gute Nachrichten, denn wir möchten die hartcodierte Liste, die wir zuvor gesehen haben, durch die dynamische ersetzen. Alles, was wir tun müssen, ist, die Werte aufzuräumen.

Beginnen wir mit dem Entfernen dieser Nullen. Also fügen wir in der Formelleiste eine List.RemoveNulls hinzu . Wie Sie jetzt sehen können, wiederholen sich alle Elemente und das liegt an den Wertepaaren. Es wird einmal für die Stunden aufgeführt.

Und um diese Duplikate für die Kontakte zu entfernen, können wir ein List.Distinct hinzufügen . Jetzt haben wir diese Logik erstellt und müssen sie nur noch kopieren und wieder in unsere Beispielabfrage einfügen.

Öffnen Sie in der Beispielabfrage den erweiterten Editor und erstellen Sie einen neuen Variablennamen. Nennen wir es AttributeList . Dann fügen wir den Code ein und setzen ein Komma.

Kehren wir nun zu unserem Schritt „Benutzerdefiniert“ zurück. In List.Zip können wir diese erste Liste durch unseren Variablennamen (AttributeList) ersetzen.

Schauen wir uns nun die zweite Liste genauer an. Wir wissen, dass es auf Spaltennamen verweist, und jeder dieser Spaltennamen beginnt mit demselben Attribut, das wir bereits in dieser Liste haben, gefolgt von einem Leerzeichen und dem Text „Stunden“.

Wir müssen nur jedem Element ein Suffix hinzufügen. Gehen wir also einen Schritt zurück und fügen eine benutzerdefinierte Spalte hinzu, um eine gewisse Logik zu erstellen.

Dynamisches Aufheben der Pivotierung mehrerer Spalten: Hinzufügen einer benutzerdefinierten Spaltenlogik

Da es sich nur um eine temporäre Spalte handelt, müssen wir ihr keinen richtigen Namen geben. Wir werden es entfernen, sobald wir fertig sind.

Um die Werte in einer Liste zu ändern, können wir die Funktion List.Transform verwenden . Wir müssen die Werte in unserer AttributeList umwandeln , also werde ich das einfügen.

Hier in den Kopfzeilen sehen wir, dass die Textwerte mit einem Großbuchstaben beginnen und der Rest dann in Kleinbuchstaben geschrieben ist. Unser Attribut enthält nur ein großgeschriebenes Wort, daher müssen wir auch diesen Textwert transformieren. Und wir können den Unterstrich verwenden, um auf jedes Element der Liste zuzugreifen, und wir fügen dieses Suffix hinzu.

Damit erhalten wir einen Listenwert in unserer Spalte. Es spielt keine Rolle, welches Sie auswählen, aber klicken Sie einfach in den weißen Bereich einer der hier aufgeführten Optionen.

Unterhalb der Seite sehen wir eine Vorschau der von uns erstellten Liste. Sie sehen also, dass diese Spaltenüberschriften jetzt richtig geschrieben sind, sodass sie mit den Spaltenüberschriften übereinstimmen, die wir hier in dieser Liste sehen.

Wir haben jetzt die Möglichkeit, die Felder aus unserem Datensatz zu identifizieren, auf die wir zugreifen möchten. Öffnen wir also erneut unser benutzerdefiniertes Spaltendialogfeld und fügen wir Record.SelectFields hinzu . Es möchte einen Datensatz als Datensatz, sodass wir diesen Unterstrich verwenden können, um auf den aktuellen Datensatz zuzugreifen, in dem wir uns befinden. Und dann haben wir ihm eine Liste mit all diesen Feldnamen gegeben, also fügen Sie eine schließende Klammer hinzu und drücken Sie OK .

Wir haben also keine Liste mehr, sondern bekommen jetzt einen Datensatz. Klicken wir erneut auf die Seite des Leerraums, um den Inhalt dieses Datensatzes anzuzeigen. Hier sehen wir, dass die Datensatzfeldnamen und die Datensatzfeldwerte aufgelistet sind.

Da uns aber nur die Datensatzfeldwerte interessieren, müssen wir diese extrahieren. Gehen wir zurück zu unserem benutzerdefinierten Spaltendialogfeld und fügen Record.FieldsValues ​​hinzu .

Wir bekommen jetzt wieder eine Liste und keinen Datensatz mehr. Wir klicken erneut auf die Seite im Leerraum und Sie sehen, dass wir jetzt eine Liste mit genau diesen Werten haben. Das sieht also gut aus.

Kopieren wir die vollständige Logik, die wir erstellt haben, mit Ausnahme der letzten schließenden Klammern. Und jetzt können wir unsere temporäre Spalte entfernen, weil wir sie nicht mehr benötigen.

In unserer Bearbeitungsleiste wählen wir unsere zweite Liste aus und ersetzen sie durch unsere Logik.

Um eine Liste mit den Datensatzfeldwerten für die Kontakte zu erstellen, müssen wir lediglich das Suffix ändern. Wir können also auch die letzte Liste auswählen und unsere Logik einfügen.

Dann ändern wir dies anstelle von Stunden in Kontakte .

Keine Fehler und es sieht gut aus. Dies ist jetzt eine dynamische Lösung zum Aufheben der Pivotierung mehrerer Spalten.

LuckyTemplates Unpivot-Spalten – Power Query-Tutorial
So entpivotieren Sie Spalten in LuckyTemplates
Unpivot- und Pivot-Grundlagen in LuckyTemplates – Überprüfung des Abfrageeditors

Abschluss

Auf diese Weise können Sie mithilfe von List.Zip mehrere Spalten in dynamisch entpivotieren .

Wenn ein neues Attribut eingeht, wird es automatisch von der AttributeList übernommen. Und wenn ein Element nicht mehr vorhanden ist, ist es nicht in dieser Liste enthalten.

Ich hoffe, Ihnen hat es gefallen. Sehen Sie sich das vollständige Video unten an, um weitere Einzelheiten zu diesem Tutorial zu erfahren, und schauen Sie sich die Links unten an, um weitere Inhalte zum Aufheben der Pivotierung mehrerer Spalten in LuckyTemplates zu erhalten.

Alles Gute

Melissa