Best Practices für den Power Query-Sprachfluss

In diesem Tutorial erfahren Sie mehr über den Power Query-Sprachfluss und einige seiner Best Practices. Außerdem erfahren Sie, wie Sie Ihre Daten einfach transformieren, um die bestmöglichen Ergebnisse zu erzielen. Datentransformationen optimieren Ihren Bericht und sorgen dafür, dass er überzeugend aussieht.

Inhaltsverzeichnis

• Legen Sie Datentypen ohne Power Query-Sprachfluss fest
  • Legen Sie Datentypen mithilfe der Registerkarte „Transformieren“ fest
  • Legen Sie Datentypen mithilfe der Spaltenüberschrift fest
  • Legen Sie Datentypen mithilfe von Spaltensymbolen fest
• Legen Sie Datentypen mit dem Power Query-Sprachfluss fest
  • Entfernen Sie unnötige Spalten
  • Weisen Sie Spalten geeignete Datentypen zu und begrenzen Sie Zeilen
  • Konsolidieren Sie redundante Schritte im Power Query-Sprachfluss
• Organisieren Sie Abfragen für einen besseren Power Query-Sprachfluss
• Zusammenfassung des Power Query-Sprachflusses
• Abschluss

Legen Sie Datentypen ohne Power Query-Sprachfluss fest

Die absoluten Mindesttransformationen, die Sie immer durchführen sollten, sind das Umbenennen und Festlegen von Datentypen für Ihre Spalten.

Datentypen werden zur Klassifizierung von Werten in M ​​verwendet . Ein Symbol neben dem Spaltennamen zeigt den zugewiesenen Datentyp für ein Feld an. In dieser Beispieltabelle sehen Sie neben den Spaltennamen ein ABC123-Symbol. Dieses Symbol stellt den Datentyp „Beliebig“ dar und zeigt an, dass dieser Spalte kein Datentyp zugewiesen wurde. 

In den Büchern ist die automatische Typerkennung aktiviert. Dadurch werden in Ihren Abfragen automatisch Schritte vom Typ „Geänderter Typ“ generiert. Auch wenn dies aktiviert ist, stellen Sie immer sicher, dass Power Query die Datentypen für jede Spalte in allen Ihren Tabellen korrekt errät.

Führen Sie die folgenden Schritte aus, um diese Einstellung zu aktivieren oder zu deaktivieren.

Unter der Option „Global“ können Sie eine der drei verfügbaren Typerkennungsoptionen auswählen .

Unter der Option „Aktuelle Datei“ können Sie die Optionen für die Typerkennung je nach Ihrer globalen Einstellung umschalten.

Legen Sie Datentypen mithilfe der Registerkarte „Transformieren“ fest

Die Benutzeroberfläche bietet mehrere Möglichkeiten, Datentypen für Ihre Spalten festzulegen. Im Abschnitt „Beliebige Spalte“ der Registerkarte „Transformieren“ finden Sie die Schaltfläche „Datentyp erkennen“ .

Wenn Sie alle Spalten auswählen und auf diese Schaltfläche klicken, werden die Datentypen aller Spalten automatisch festgelegt. Da jedoch nur die obersten 200 Zeilen gescannt werden, müssen Sie dennoch überprüfen, ob Power Query den richtigen Datentyp hat.

Wenn Sie eine Spalte aus Ihrer Tabelle auswählen, können Sie deren Datentyp im Abschnitt „Beliebige Spalte“ sehen.

Wenn Sie auf die Dropdown-Schaltfläche klicken und Text auswählen, ändert sich das Symbol in der ausgewählten Spalte von ABC123 in nur ABC .

Anschließend wird im Bereich „Angewandte Schritte“ der Schritt „Geänderter Typ“ angezeigt.

Auf der Registerkarte „Startseite“ finden Sie auch die Option, den Datentyp festzulegen.

Legen Sie Datentypen mithilfe der Spaltenüberschrift fest

Eine andere Möglichkeit besteht darin, mit der rechten Maustaste auf eine Überschrift zu klicken und „ Typ ändern“ auszuwählen . Von dort aus können Sie den passenden Datentyp für Ihre Spalten auswählen.

Legen Sie Datentypen mithilfe von Spaltensymbolen fest

Die gebräuchlichste Methode zum Festlegen von Datentypen besteht darin, auf das Symbol neben dem Spaltennamen zu klicken. Es werden Ihnen alle verfügbaren Datentypen angezeigt.

Legen Sie in der Beispieltabelle den Datentyp der OrderDate- Spalte auf Date fest .

Abhängig vom Datentyp der von Ihnen ausgewählten Spalte stellt Power Query auf der Registerkarte „Transformieren “ , der Registerkarte „Spalte hinzufügen “ und im Abschnitt „Smart Filter“ eine Reihe von Transformationen und Optionen bereit, die für diesen bestimmten Datentyp gelten .

Wenn Sie also eine Datumsspalte auswählen und zur Registerkarte „Transformieren“ wechseln, werden unter der Schaltfläche „Datum“ Optionen angezeigt.

Wenn Sie jedoch eine Textspalte auswählen, sind die Optionen unter der Schaltfläche „Datum“ nicht mehr verfügbar.

Für die intelligente Filterung verfügen Textspalten über Textfilter und Datumsspalten über Datumsfilter.

Ändern Sie für die nächste Spalte den Spaltennamen in „ Customer Key“ und den Datentyp in „Ganze Zahl“ .

Ändern Sie als Nächstes den Datentyp der Spalten „Kanal“, „Währungscode“ und „Lagercode“ in „Text“.

Anschließend ändern Sie den Namen des Delivery Region Index in Delivery Region Key und seinen Datentyp in Whole Number . Machen Sie dann dasselbe für die Spalten „Produktbeschreibungsindex“ und „Bestellmenge“ .

Legen Sie abschließend für die Spalten „Stückpreis“, „Positionssumme“ und „Stückkosten“ den Datentyp auf „ Feste Dezimalzahl“ fest .

Legen Sie Datentypen mit dem Power Query-Sprachfluss fest

Aufgrund der Änderungen in den Spalten wurden viele Schritte im Bereich „Angewandte Schritte“ erstellt. Das Problem besteht darin, dass die Beispieltabellenabfrage ineffizient wird , da dieselbe Art von Transformationen mehrmals durchgeführt wird . Dies sollten Sie vermeiden.

Um Ihre Abfrage effizient zu gestalten, versuchen Sie, einen einzelnen Schritt zu erstellen, indem Sie diese spezifische Transformation auf alle Ihre Spalten anwenden, bevor Sie einen weiteren Schritt erstellen.

Duplizieren Sie die Beispieltabellenabfrage und benennen Sie sie in Best Practice um . Klicken Sie im Bereich „Angewandte Schritte“ mit der rechten Maustaste auf die erste in der vorherigen Abfrage durchgeführte Transformation und wählen Sie „ Bis zum Ende löschen“ aus . Bestätigen Sie dann das Löschen des Schritts im Dialogfeld „Schritt löschen“ .

Darüber hinaus finden Sie hier einige Best Practices. Als Erstes müssen Parameter erstellt werden, die den Speicherort der Datenquelle enthalten. Dies erleichtert die Behebung von Problemen, wenn ein Dateiname geändert wurde.

Um Parameter zu erstellen, klicken Sie auf der Registerkarte „Startseite“ auf „Parameter verwalten“ und wählen Sie „ Neuer Parameter“ aus .

Eine andere Möglichkeit besteht darin, mit der rechten Maustaste auf den Abfragebereich zu klicken und „Neuer Parameter“ auszuwählen .

Danach erscheint ein Dialogfeld „Parameter verwalten“ . Benennen Sie den Parameter FileLocation und legen Sie den Typ auf Text fest. Legen Sie für „Vorgeschlagene Werte“ die Option „ Werteliste“ fest, damit Sie Ihre Zeichenfolge einfügen und mehrere Speicherorte ändern oder hinzufügen können, zwischen denen Sie wechseln können.

Gehen Sie als Nächstes zu Ihrem Datei-Explorer und wählen Sie Ihre Datei aus. Kopieren Sie den Pfad und fügen Sie die Parameter ein. Wenn Sie fertig sind, drücken Sie OK .

Gehen Sie zurück zur Best Practice-Abfrage und klicken Sie im Bereich „Angewandte Schritte“ auf den Schritt „Quelle“. Ändern Sie dann den fest codierten Dateipfad in der Bearbeitungsleiste mit FileLocation .

Entfernen Sie unnötige Spalten

Entfernen Sie alle unnötigen Spalten, um Platz zu sparen und die Leistung zu verbessern. Bringen Sie nur die Daten mit, die Sie benötigen, da das Hinzufügen von Tabellen und Spalten viel einfacher ist als das Entfernen.

Am einfachsten geht das über den Schritt „Spalten auswählen“ auf der Registerkarte „Startseite“. Wenn Sie auf diese Schaltfläche klicken, wird ein Dialogfeld angezeigt, in dem Sie die beizubehaltenden Spalten auswählen können. Sie sollten Ihre Tabellen für einen bestimmten Zweck entwerfen und gestalten, damit sie optimal zu der Analyse passen, die Sie durchführen möchten.

Deaktivieren Sie von dort aus die Spalten, die Sie in Ihrer Tabelle nicht benötigen. Für diese Beispieltabelle ist die Kardinalität der Spalte „OrderNumber“ hoch. Am besten deaktivieren Sie diese Spalte, da sie sich auf die Dateigröße und die Gesamtleistung auswirkt.

Wenn Sie für die Analyse der Beispielabfrage keine Standortdaten benötigen, deaktivieren Sie am besten auch den Index der Lieferregion . Da die Tabelle schließlich bereits einen Stückpreis und eine Menge enthält, wird die Spalte „Zeilensumme“ nicht benötigt.

Nachdem Sie die Auswahl der Spalten aufgehoben haben, drücken Sie OK . Wenn Sie die Spaltenauswahl ändern möchten, löschen Sie einfach das Zahnradsymbol neben dem Schrittnamen im Bereich „Angewandte Schritte“.

Weisen Sie Spalten geeignete Datentypen zu und begrenzen Sie Zeilen

Weisen Sie als Nächstes Datentypen für alle Spalten zu. Wählen Sie alle Spalten aus und klicken Sie auf der Registerkarte „Transformieren“ auf „Datentyp erkennen“ .

„Datentyp erkennen“ erkennt automatisch die Datentypen von Spalten basierend auf dem Scan der obersten 200 Zeilen. Überprüfen Sie also, ob Power Query die richtigen Datentypen festlegt.

Es empfiehlt sich, die Anzahl der Zeilen zu begrenzen. Wenn Ihr Geschäftsjahr am 1. Juli beginnt, können Sie die Daten ab Juni weglassen oder einen Parameter einrichten.

Allerdings gibt es einen Haken, wenn Sie einen Parameterwert im LuckyTemplates-Dienst ändern möchten . Nachdem Sie Ihren Bericht veröffentlicht haben, müssen Ihre Parameterwerte entweder vom Typ „Text“ oder vom Typ „Dezimal“ sein.

Um dies zu beheben, erstellen Sie einen Filter für die Daten, indem Sie auf die Dropdown-Schaltfläche in der Spalte „OrderDate“ klicken. Klicken Sie anschließend auf „Datumsfilter“ und wählen Sie „Nach“ aus .

Ändern Sie im Dialogfeld den ersten Parameter in „ ist nach oder gleich“ und geben Sie dann das Datum ein. In diesem Beispiel war das eingegebene Datum der 1. Juli 2014.

Nachdem Sie auf OK geklickt haben , wird der Filter in Ihrer Tabelle angewendet. Erstellen Sie als Nächstes einen Parameter und nennen Sie ihn DatesFrom . Stellen Sie den Typ auf „Text“ ein und geben Sie das Datum in den Parameter „Aktueller Wert“ ein.

Gehen Sie nun zurück zur Best-Practice-Abfrage und ersetzen Sie das intrinsische #date durch Date.From(DatesFrom) .

Wenn Sie ohne die Funktion „Date.From“ auf das Häkchen klicken, wird ein Fehler zurückgegeben. Das liegt daran, dass „DatesFrom“ vom Typ „Text“ ist, während das Feld vom Typ „Datum“ ist. Die Date.From- Funktion wandelt Text in Datum um.

Benennen Sie alle Spalten um, die im Datenmodell nicht ausgeblendet werden. Der Name muss prägnant, selbstbeschreibend und benutzerfreundlich sein. Bedenken Sie, dass Sie ein Datenmodell für Personen entwerfen, die Ihren Bericht verwenden werden.

Konsolidieren Sie redundante Schritte im Power Query-Sprachfluss

Als Nächstes müssen redundante Schritte konsolidiert werden (z. B. Spaltendatentypen umbenennen, entfernen und ändern). Darüber hinaus sollten Sie bei der Suche nach Redundanzen auf Schritte wie das Neuanordnen von Spalten achten.

Die Tabellen, die Sie in das Datenmodell laden, werden nie in Ihrem Bericht angezeigt. Dadurch ist die Spaltenreihenfolge irrelevant.

Eine weitere bewährte Methode besteht darin, Ihre Schritte im Bereich „Angewandte Schritte“ umzubenennen. Die Namen der Schritte sind selbsterklärend und werden als Variablen im M- Code verwendet.

Namen, die Leerzeichen oder Sonderzeichen enthalten, werden in der Notation in Anführungszeichen geschrieben. Das bedeutet, dass die Namen in doppelte Anführungszeichen gesetzt werden und ein Raute- oder Nummernzeichen davor steht, was die Lesbarkeit des M- Codes erschwert. Sie können die Leerzeichen weglassen oder einen Unterstrich dazwischen setzen.

Das Hinzufügen zusätzlicher Dokumentationsdetails durch Einfügen von Kommentaren in das Fenster „Erweiterter Editor“ ist ebenfalls eine bewährte Methode in Power Query. Sie können dies auch in der Step-Eigenschaftsbeschreibung tun . Sie werden als QuickInfo-Anmerkungen angezeigt, wenn Sie im Bereich „Angewandte Schritte“ mit der Maus über einen Schritt mit einem Ausrufezeichen fahren.

Zu wissen, warum Sie bei der anfänglichen Entwicklung eine bestimmte Entscheidung getroffen haben, ist äußerst hilfreich, wenn Sie eine Datei nach einiger Zeit noch einmal durchsehen müssen. Um Dokumentationsdetails hinzuzufügen, klicken Sie mit der rechten Maustaste auf einen Schritt im Bereich „Angewandte Schritte“ und wählen Sie „Eigenschaften“ aus .

Es erscheint ein Dialogfeld mit den Schritteigenschaften , in dem Sie den Grund für die Filterung oder Transformation angeben können.

Organisieren Sie Abfragen für einen besseren Power Query-Sprachfluss

Eine der häufigsten Best Practices in Power Query ist das Organisieren Ihrer Abfragen . Erstellen Sie Ordner für Parameter, Funktionen, Staging-Abfragen und Abfragen, die in das Datenmodell geladen werden. Wählen Sie in diesem Beispiel die Abfragen „FileLocation“ und „DatesFrom“ aus und klicken Sie mit der rechten Maustaste darauf. Wählen Sie dann „ In Gruppe verschieben“ und klicken Sie auf „Neue Gruppe“ .

Fügen Sie als Nächstes einen Namen für die ausgewählten Abfragen hinzu und klicken Sie auf OK .

Nachdem Sie Ihre Abfragen gruppiert haben, sieht Ihr Abfragebereich folgendermaßen aus.

Stellen Sie bei allen Staging-Abfragen sicher, dass Sie die Last deaktivieren, indem Sie die Option „Laden aktivieren“ deaktivieren.

Eine weitere Sache, die in diesem Tutorial besprochen werden soll, ist der Sprachfluss. Jeder der Schritte im Bereich „Angewandte Schritte“ transformiert einen Wert, den Sie sehen können, wenn Sie darauf klicken.

Die ersten Daten kamen und begannen im Navigationsschritt, und die Spalten wurden ausgewählt. Als nächstes wurden die Datentypen geändert und ein Datumsbereich festgelegt. Auch die Spalten wurden umbenannt.

Alle Schritte geben einen Tabellentypwert zurück. Wenn Sie das Fenster „Erweiterter Editor“ öffnen, sehen Sie einen let- Ausdruck und eine in- Klausel. Dazwischen befindet sich eine Liste von Schritten oder Variablennamen mit ihnen zugeordneten Ausdrücken.

Die Abfrage gibt alles zurück, was auf die in- Klausel folgt und sich auf den letzten Schritt in Ihrer Variablenliste bezieht. Die M- Engine folgt dann der Abhängigkeitskette zurück von der In- Klausel, um alles Unnötige zu eliminieren und Transformationen nach Möglichkeit zurück zur Quelle zu verschieben.

Zusammenfassung des Power Query-Sprachflusses

Wenn Sie sich die Formel ansehen, können Sie den Ablauf der in der Abfrage ausgeführten Schritte erkennen. Außerdem sehen Sie in jedem Schritt die von der Benutzeroberfläche verwendeten Funktionen .

Der erste Schritt, der bei der Abfrage durchgeführt wurde, war die Spaltenauswahl. Als der Schritt über die Benutzeroberfläche ausgeführt wurde, wurde die Funktion Table.SelectColumns aufgerufen. Als ersten Parameter nahm es eine Tabelle, die auf den Variablennamen des vorherigen Schritts verwies. Anschließend wurden alle ausgewählten Spaltennamen aufgelistet.

Im zweiten Schritt wurden die Spaltentypen durch Aufrufen der Funktion Table.TransformColumnTypes transformiert . Sein erster Parameter bezeichnet die Ausgabe des vorherigen Schritts. Anschließend wurde eine Reihe von Transformationslisten aufgelistet.

Im dritten Schritt legen Sie mithilfe der Table.SelectRows -Funktion einen Datumsbereichsfilter fest . Als erstes Argument wurde eine Tabellentypabfrage verwendet. In diesem Beispiel wurde auf die Ausgabe des Schritts „Typ ändern“ verwiesen.

Im letzten Schritt wurden die Spalten mit der Funktion Table.RenameColumns umbenannt . Als erstes Argument wurde die Ausgabe des vorherigen Schritts verwendet. Anschließend wurde eine Reihe von Umbenennungslisten aufgelistet.

Alle über die Benutzeroberfläche angewendeten Funktionen beginnen mit dem Wort „ Tabelle“ . Sie alle nahmen einen Tabellenwert als ersten Parameter und transformierten diesen Wert dann.

Obwohl der Code sequentiell aussieht, da jeder Schritt auf den vorherigen Schritt verweist, ist die Reihenfolge nicht erforderlich. Wenn Sie einen Schritt weitergehen, wird die Abfrage weiterhin ausgeführt, da die M- Engine immer der Abhängigkeitskette folgt.

Die Benutzeroberfläche ist praktisch, aber es wird immer davon ausgegangen, dass Sie die Ergebnisse der vorherigen Transformation transformieren möchten. In den meisten Fällen wird dies wahrscheinlich der Fall sein. Sollte dies nicht der Fall sein, müssen Sie den referenzierten Wert im Code manuell aktualisieren.

Power Query-Datentypen und Konnektoren
LuckyTemplates-Datensätze: Typen und Namenskonventionen
Best Practices für das Laden und Transformieren von Daten

Abschluss

Die Datentransformation ist notwendig, um Ihre Daten gruppiert und organisiert zu halten. Dadurch wird die Datenentwicklung beschleunigt, da Sie problemlos Probleme im Power Query-Sprachfluss nachverfolgen und Änderungen in Ihrem Bericht ändern können.

Melissa