Verschachtelte Ausdrücke: Power Query-Umgebungen

In diesem Tutorial erfahren Sie mehr über verschachtelte Ausdrücke, Objekte und Umgebungen in Power Query . Verschachtelte Ausdrücke und Variablen werden verwendet, um die Funktionsweise von Umgebungen zu diskutieren und bei der Generierung von Daten zu helfen. Außerdem erfahren Sie, wie Sie mit gängigen Szenarien bei der Transformation verschachtelter Objekte umgehen.

Inhaltsverzeichnis

• Erstellen verschachtelter Ausdrücke
• Verschachtelte Ausdrücke in einer Umgebung verstehen
• Häufige Szenarien lösen
  • Verwenden von Variablen zum Speichern von Werten
  • Einen einzelnen Wert abrufen
• Abschluss

Erstellen verschachtelter Ausdrücke

Ausdrücke werden als Formeln definiert, die zum Konstruieren von Werten verwendet werden.

Der Ausdruck in der Bearbeitungsleiste wird aus Unterausdrücken erstellt. Die Literale 1 und 2 sind Unterausdrücke des übergeordneten Ausdrucks.

Variablen werden als benannte Werte definiert. Dies ist ein Beispieldatensatz.

Wenn Sie das Fenster „Erweiterter Editor“ öffnen, können Sie die in der Abfrage verwendeten Variablen sehen.

Die Variablen a , b und c stellen jeweils einen Wert dar, der das Ergebnis eines Ausdrucks ist, den Sie häufig nach einem Gleichheitszeichen finden. Alle Variablen innerhalb des übergeordneten Ausdrucks, der der Datensatz selbst ist, bilden die Umgebung dieses Datensatzes. Innerhalb dieser Umgebung muss jede Variable eindeutig sein.

Wenn Sie also eine weitere Variable mit dem Namen c hinzufügen , wird unter dem Fenster „Erweiterter Editor“ eine Fehlermeldung angezeigt.

Der Datensatz kann über Bezeichner oder Variablennamen auf alle seine Unterausdrücke zugreifen. Bei verschachtelten Ausdrücken innerhalb des Datensatzes ist jedoch jede Umgebung unterschiedlich, da sie auf alle anderen Variablen im Datensatz außer auf sich selbst zugreifen können.

In diesem Beispiel können Sie sehen, dass die Variable c auf sich selbst verweist.

Wenn Sie auf „Fertig“ klicken, erhalten Sie eine Fehlermeldung.

Wenn Sie den Wert von c durch Hinzufügen des Elementzugriffsoperators extrahieren, erhalten Sie ein Ergebnis. Das gleiche Ergebnis können Sie auch mit dem let- Ausdruck erzielen, da auf ihn dieselben Regeln angewendet werden.

Verschachtelte Ausdrücke in einer Umgebung verstehen

Dies ist ein weiteres Beispiel für einen Rekordwert.

Wenn Sie das Fenster „Erweiterter Editor“ öffnen, können Sie sehen, dass die Variablen a und b dieselbe Umgebung haben, nämlich die äußerste Umgebung. Sie werden auch sehen, dass die Variablen x , y und z Unterausdrücke des übergeordneten Ausdrucks a sind .

Jede Variable kann auf alle anderen Variablen im verschachtelten Datensatz außer auf sich selbst zugreifen. Sie können eine Variable aufrufen, die in einer anderen Umgebung vorhanden ist. In diesem Beispiel können Sie sehen, dass z auf b verweist, das sich in der äußeren Umgebung befindet.

Die Variablen x , y und z können auf b zugreifen , da b Teil ihrer übergeordneten Ausdrucksumgebung ist. Sie können dieselbe Variable auch in unterschiedlichen Umgebungen verwenden.

In diesem anderen Beispiel können Sie sehen, dass der innere und äußere Datensatz eine Variable x haben .

Variablen müssen innerhalb ihrer eigenen Umgebung eindeutig sein. Es wird das nächstgelegene x verwendet, das einer Variablen zugewiesen ist. In dieser Beispielabfrage wird das innere x verwendet, da es aus Sicht des Datensatzes a näher liegt .

In dieser anderen Abfrage wird im Ausdruck das nächstgelegene x verwendet , das in der Nähe der Variablen b liegt.

Die äußere Variable b kann mithilfe des Elementzugriffsoperators auf die Variable x im Datensatz a verweisen . Sie müssen auf die Variable a verweisen und dann mit einer Reihe eckiger Klammern auf x zugreifen. Auf die gleiche Weise kann Datensatz a auf die Variable x innerhalb der Variablen b verweisen .

Wenn die Umgebung des verschachtelten Datensatzes a mit Variablen aus dem übergeordneten Ausdruck zusammengeführt wird, führt dies zu einem Konflikt, da x zweimal existiert und alle Variablen in ihrer Umgebung eindeutig sein müssen. Da eine Variable nicht auf sich selbst verweisen kann, wird der Konflikt gelöst, indem auf das äußere x verwiesen wird.

Häufige Szenarien lösen

Ein häufiges Szenario ist die Transformation verschachtelter Objekte. Zur Demonstration wird diese Tabelle verwendet.

Wenn Sie einen Wert aus der äußeren Tabelle abrufen und in der verschachtelten Tabelle platzieren möchten, gibt es zwei Möglichkeiten, dies zu erreichen. Die erste Methode besteht darin, die Option „Benutzerdefinierte Spalte hinzufügen“ zu verwenden .

Klicken Sie auf das Tabellensymbol in der oberen linken Ecke des Vorschaufensters und wählen Sie „Benutzerdefinierte Spalte hinzufügen“.

Geben Sie „Temp“ als Spaltennamen ein und geben Sie einen Platzhalter in die benutzerdefinierte Spaltenformel ein . Wenn Sie fertig sind, drücken Sie OK.

Ersetzen Sie in der Bearbeitungsleiste jeden Ausdruck durch eine benutzerdefinierte Funktion, indem Sie Ihrer verschachtelten Tabelle eine Spalte hinzufügen. Starten Sie die Logik für eine benutzerdefinierte Funktion und nennen Sie sie OT für äußere Tabelle. Fügen Sie dann das Go-to-Schild hinzu.

Verwenden Sie als Nächstes die Funktion Table.AddColumn , um eine Spalte hinzuzufügen. Sehen Sie sich die Tabelle in der äußeren Tabelle in der Spalte „Namen“ an. Geben Sie dann einen neuen Spaltennamen ein.

In diesem Beispiel lautet der verwendete Spaltenname Revenue . Geben Sie die Funktion „each“ ein , um die Werte jeder Zeile in der Spalte „Umsatz abrufen“ abzurufen.

Wenn Sie in der Spalte „Temp“ auf den Raum innerhalb der Zelle einer Zeile klicken, wird unterhalb des Vorschaufensters eine Vorschau der verschachtelten Tabelle angezeigt. Sie werden sehen, dass jede Zeile den Wert der äußeren Tabelle hat.

Verwenden von Variablen zum Speichern von Werten

Die zweite Methode zur Transformation verschachtelter Objekte besteht darin, Variablen zum Speichern von Werten zu verwenden. Fügen Sie zunächst eine benutzerdefinierte Spalte hinzu und geben Sie „Temp2“ als Spaltennamen ein. Verwenden Sie für die Formel den let- Ausdruck, um Werte in Variablen zu speichern.

Geben Sie let ein und schreiben Sie dann einen Variablennamen. In diesem Beispiel lautet der Variablenname myR . Als nächstes setzen Sie die Variable mit der Spalte „Get Revenue“ gleich. Geben Sie anschließend die in- Klausel ein und verwenden Sie die Funktion Table.AddColumn , um der verschachtelten Tabelle eine Spalte hinzuzufügen.

Geben Sie als ersten Parameter der Funktion die Spalte „Names“ ein, die die verschachtelten Tabellen enthält. Benennen Sie als Nächstes die neue Spalte „Umsatz“. Verwenden Sie every , um die Variablen jeder Zeile aufzurufen.

Wenn Sie auf das Leerzeichen innerhalb der Zelle einer Zeile in der Spalte „Temp2“ klicken, werden Sie sehen, dass die Werte aus der äußeren Tabelle abgerufen wurden.

Ein weiteres häufiges Szenario ist eine Suche, bei der kein gemeinsamer Schlüssel vorhanden ist. Für dieses Szenario werden zwei Beispieltabellen verwendet: eine Kategorietabelle und eine Produkttabelle .

Verwenden Sie die Kategorietabelle als Staging-Abfrage, um die Attribute der Produktdimensionstabelle zu ergänzen. Da kein gemeinsamer Schlüssel vorhanden ist, kann keine Zusammenführung durchgeführt werden. Als Erstes müssen Sie die vollständige Kategorietabelle mithilfe der Funktion „Benutzerdefinierte Spalte hinzufügen“ in der Produkttabellenabfrage verschachteln.

Fügen Sie eine benutzerdefinierte Spalte hinzu und schreiben Sie „Kategorie“ als Spaltennamen. Rufen Sie dann in der benutzerdefinierten Formel die Kategorieabfrage auf.

Danach haben Sie nun die vollständige Kategorietabelle in jeder Zeile der Produkttabelle verschachtelt.

Wenn Sie in der Spalte „Kategorie“ auf den Raum innerhalb der Zelle einer Zeile klicken, können Sie in jeder Zeile die vollständige Kategorietabelle sehen.

Einen einzelnen Wert abrufen

Wenn Sie nur einen einzelnen Wert abrufen möchten, müssen Sie die Kategorietabelle bis auf eine einzelne Zeile filtern, in der ein Teil der Zeichenfolge aus dem Produkt mit der Produktgruppe in der Kategorietabelle übereinstimmt.

Öffnen Sie das Dialogfeld „Benutzerdefinierte Spalte“, indem Sie im Bereich „Angewandte Schritte“ auf die Zahnradschaltfläche neben „Hinzugefügte benutzerdefinierte Spalte“ klicken.

Fügen Sie in der Formel die Funktion Table.SelectRows hinzu . Für den ersten Parameter wird die Kategorietabelle verwendet. Der zweite Parameter verwendete die Funktion Text.Contains als Bedingung. Für die innere Tabelle prüft diese Funktion, ob ein Text einen Teil aus der Produktzeichenfolge in der äußeren Tabelle enthält.

Wenn Sie auf die äußere Tabelle zugreifen, werden Sie feststellen, dass Power Query jedes Schlüsselwort in die Formelleiste einfügt . Es handelt sich um eine unäre Funktion, die den Unterstrich ( _ ) als namenlose Variable akzeptiert. Da Sie jedoch nur auf eine Spalte zugreifen, kann diese weggelassen werden.

Wenn Sie in der Spalte „Kategorie“ auf den Raum innerhalb der Zelle einer Zeile klicken, wird nun in jeder der verschachtelten Tabellen eine einzelne Zeile angezeigt.

Von dort aus können Sie mit der Kombination der Zeilen- und Feldzugriffsoperatoren einen Wert für die Kategorie extrahieren.

Gehen Sie zurück zum Dialogfeld „Benutzerdefinierte Spalte“ und fügen Sie die Operatoren in die Formel ein. Um die erste Zeile der Tabelle zu erhalten, geben Sie 0 in geschweifte Klammern ein. Listen Sie dann den Feldnamen „Kategorie“ in eckigen Klammern auf.

Um die Lösung zukunftssicher zu machen, können Sie am Ende ein Fragezeichen ( ? ) hinzufügen, um das Nicht-Gefunden-Verhalten von der Rückgabe eines Fehlers in eine Null zu ändern.

Funktionsweise des Abfrage-Editors in LuckyTemplates
M Language und des Power Query-Editors in LuckyTemplates

Abschluss

Umgebungen und verschachtelte Ausdrücke helfen Ihrer Abfrage dabei, bestimmte Werte aus Ihren Codes hervorzuheben, um gewünschte Ergebnisse zu liefern. Beim Schreiben von Funktionen ist es wichtig, sich ihrer bewusst zu sein, denn wenn man sie versteht, kann man anspruchsvollere und komplexere Szenarien lösen.

Melissa