Bewährte Methoden beim Arbeiten mit Power Query

Diese Power Query bewährten Methoden helfen Ihnen, die Abfrageleistung zu verbessern, Abfragefaltung zu nutzen, korrekte Datentypen auszuwählen, Transformationen zu organisieren und Logik mit Parametern und benutzerdefinierten Funktionen wiederzuverwenden. Sie gelten sowohl für Power Query Desktop- als auch für Power Query Onlineerfahrungen.

Wählen Sie den richtigen Verbinder aus.

Power Query bietet viele Datenkonnektoren. Diese Connectors reichen von Datenquellen wie TXT, CSV und Excel Dateien bis hin zu Datenbanken wie Microsoft SQL Server und beliebten Software-as-a-Service-Produkten wie Microsoft Dynamics 365 und Salesforce. Wenn im Fenster " Daten abrufen " kein zweckbasierter Connector verfügbar ist, verwenden Sie einen generischen Connector wie ODBC oder OLE DB.

Wählen Sie den speziell für Ihre Datenquelle entwickelten Connector aus, wenn ein solcher verfügbar ist. Beispielsweise bietet der SQL Server Connector eine bessere Benutzererfahrung zum Abrufen von Daten als der generische ODBC-Connector, wenn Sie eine Verbindung mit einer SQL Server-Datenbank herstellen. Der SQL Server Connector unterstützt auch Leistungsfeatures wie z. B. Abfragefaltung. Weitere Informationen finden Sie unter "Übersicht über die Abfrageauswertung und Abfragefaltung in Power Query".

Jeder einzelne Datenconnector folgt einem Standardablauf, wie im Abrufen von Daten erläutert. Diese standardisierte Oberfläche hat eine Phase namens "Datenvorschau". In dieser Phase erhalten Sie ein benutzerfreundliches Fenster, um die Daten auszuwählen, die Sie aus Ihrer Datenquelle abrufen möchten, wenn der Connector dies zulässt, und eine einfache Datenvorschau dieser Daten. Sie können sogar mehrere Datasets aus Ihrer Datenquelle über das Navigator-Fenster auswählen.

Frühzeitiges Filtern von Daten zur Verbesserung der Leistung

Filtern Sie die Daten so früh wie möglich, um die Anzahl der Zeilen zu reduzieren, die Power Query bei späteren Transformationen verarbeitet. Für Connectors, die die Abfragefaltung unterstützen, können Power Query Filter zurück an die Datenquelle übertragen, wie in der Übersicht über die Abfrageauswertung und Abfragefaltung in Power Query beschrieben. Durch das Filtern irrelevanter Daten werden auch die in der Datenvorschau angezeigten Daten begrenzt.

Verwenden Sie das Menü "Automatischer Filter", in dem eine eindeutige Liste der In Ihrer Spalte gefundenen Werte angezeigt wird, um die Werte auszuwählen, die Sie beibehalten oder herausfiltern möchten. Verwenden Sie die Suchleiste, um die Werte in Ihrer Spalte zu finden.

Sie können auch die typspezifischen Filter wie In the previous für eine Datum-, Datum-Zeit- oder sogar Datumszeitzonen-Spalte nutzen.

Mithilfe dieser typspezifischen Filter können Sie einen dynamischen Filter erstellen, der immer Daten abruft, die sich in der vorherigen x-Anzahl von Sekunden, Minuten, Stunden, Tagen, Wochen, Monaten, Quartalen oder Jahren befinden.

Führen Sie teure Vorgänge zuletzt aus, um die Leistung zu verbessern.

Um die Vorschauleistung im Power Query-Editor zu verbessern, führen Sie teure Vorgänge zuletzt aus. Bestimmte Vorgänge erfordern das Lesen der vollständigen Datenquelle, um überhaupt Ergebnisse zurückzugeben, und lassen sich daher nur langsam in der Vorschau anzeigen. Wenn Sie beispielsweise eine Sortierung durchführen, ist es möglich, dass sich die ersten sortierten Zeilen am Ende der Quelldaten befinden. Um ergebnisse zurückzugeben, muss der Sortiervorgang zuerst alle Zeilen lesen.

Andere Vorgänge (z. B. Filter) müssen nicht alle Daten lesen, bevor Ergebnisse zurückgegeben werden. Stattdessen verarbeiten sie die Daten auf eine Art und Weise, die als "Streaming" bezeichnet wird. Die Daten strömen kontinuierlich, und die Ergebnisse werden währenddessen zurückgegeben. Im Power Query-Editor müssen solche Vorgänge nur genügend Quelldaten lesen, um die Vorschau aufzufüllen.

Führen Sie nach Möglichkeit zuerst solche Streamingvorgänge aus und erledigen Sie die teureren Vorgänge zuletzt. Das Ausführen von Vorgängen in dieser Reihenfolge trägt dazu bei, den Zeitraum zu minimieren, in dem Sie warten, bis die Vorschau jedes Mal gerendert wird, wenn Sie Ihrer Abfrage einen neuen Schritt hinzufügen.

Verwenden einer Datenuntermenge beim Entwickeln einer Abfrage

Wenn das Hinzufügen neuer Schritte im Power Query-Editor langsam ist, verwenden Sie "Erste Zeilen beibehalten", um die beim Entwickeln der Abfrage verarbeiteten Daten einzuschränken. Nachdem Sie alle erforderlichen Schritte hinzugefügt haben, entfernen Sie den Schritt "Erste Zeilen beibehalten ", damit die abgeschlossene Abfrage den vollständigen Datensatz verarbeitet.

Verwenden der richtigen Datentypen

Legen Sie den richtigen Datentyp für jede Spalte fest, damit Power Query typspezifische Transformationen und Filter verfügbar machen können. Wenn Sie beispielsweise eine Datumsspalte auswählen, können Sie die Optionen unter der Gruppe " Datums- und Uhrzeitspalte " im Menü " Spalte hinzufügen " verwenden. Wenn die Spalte keinen Datentypsatz aufweist, sind diese Optionen abgeblentet.

Eine ähnliche Situation tritt für die typspezifischen Filter auf, da sie für bestimmte Datentypen spezifisch sind. Wenn Ihre Spalte nicht den richtigen Datentyp definiert hat, sind diese typspezifischen Filter nicht verfügbar.

Es ist wichtig, dass Sie immer mit den richtigen Datentypen für Ihre Spalten arbeiten. Wenn Sie mit strukturierten Datenquellen wie Datenbanken arbeiten, werden die Datentypinformationen aus dem Tabellenschema der Datenbank abgerufen. Bei unstrukturierten Datenquellen wie TXT- und CSV-Dateien ist es jedoch wichtig, dass Sie die richtigen Datentypen für die Spalten festlegen, die aus dieser Datenquelle stammen. Power Query bietet standardmäßig eine automatische Datentyperkennung für unstrukturierte Datenquellen. Weitere Informationen zu dieser Funktion und wie sie Sie unterstützen kann, finden Sie in Datentypen.

Ihre Daten profilieren und untersuchen

Bevor Sie Ihre Daten vorbereiten und Transformationsschritte hinzufügen, aktivieren Sie die Power Query Datenprofilerstellungstools, um Informationen zu Ihren Daten zu ermitteln.

Power Query bietet drei Tools zur Datenprofilerstellung:

Sie können auch mit diesen Features interagieren, mit denen Sie Ihre Daten vorbereiten können.

Dokumentieren Sie Ihre Arbeit

Dokumentieren Sie eine Power Query-Lösung, indem Sie Schritten, Abfragen und Gruppen aussagekräftige Namen und Beschreibungen vergeben. Diese Details machen den Zweck jeder Transformation einfacher zu verstehen und aufrechtzuerhalten.

Während Power Query automatisch einen Schrittnamen für Sie im bereich angewendeter Schritte erstellt, können Sie Ihre Schritte auch umbenennen oder eine Beschreibung hinzufügen.

Aufteilen großer Abfragen in Module

Teilen Sie eine große Power Query Abfrage in kleinere abfragen, auf die verwiesen wird, um ihre Transformationsphasen einfacher zu verstehen und zu verwalten. Obwohl eine einzelne Abfrage alle benötigten Transformationen und Berechnungen enthalten kann, ist eine Abfrage mit vielen Schritten einfacher zu verwalten, wenn eine Abfrage auf die nächste verweist.

Beispielsweise umfasst die folgende Abfrage neun Schritte, darunter den Schritt „Mit der Tabelle ,Preise‘ zusammenführen“.

Sie können diese Abfrage an "Zusammenführen mit Preisen" im Schritt in zwei Teile aufteilen. Auf diese Weise ist es einfacher, die Schritte zu verstehen, die vor dem Zusammenführen auf die Verkaufsabfrage angewendet wurden. Um diesen Vorgang auszuführen, klicken Sie mit der rechten Maustaste auf den Schritt der Tabelle 'Mit Preisen zusammenführen', und wählen Sie die Option 'Vorheriges extrahieren' aus.

Sie werden dann mit einem Dialogfeld aufgefordert, Ihrer neuen Abfrage einen Namen zu geben. Dieser Schritt teilt Ihre Abfrage effektiv in zwei Abfragen auf. Eine Abfrage verfügt über alle Schritte vor dem Zusammenführen. Die andere Abfrage hat einen ersten Schritt, der auf Ihre neue Abfrage verweist, gefolgt von den restlichen Schritten, die Sie in der ursprünglichen Abfrage von dem Schritt 'Mit Preisen zusammenführen' an abwärts hatten.

Sie können auch Abfragereferenzen nach Bedarf verwenden. Es ist jedoch eine gute Idee, Ihre Abfragen auf einem Niveau zu halten, das nicht sofort entmutigend wirkt, insbesondere bei der Vielzahl an Schritten.

Organisieren von Abfragen in Gruppen

Verwenden Sie Gruppen im Abfragebereich, um Ihre Arbeit organisiert zu halten.

Der einzige Zweck von Gruppen besteht darin, Ihre Arbeit zu organisieren, indem Sie als Ordner für Ihre Abfragen dienen. Sie können Gruppen innerhalb von Gruppen erstellen, wenn Sie es jemals benötigen. Das Verschieben von Abfragen über Gruppen hinweg ist so einfach wie ziehen und ablegen.

Versuchen Sie, Ihren Gruppen einen aussagekräftigen Namen zu geben, der für Sie und Ihren Fall sinnvoll ist.

Zukunftssichere Abfragen

Entwerfen Sie Abfragen, um erwartete Änderungen in den Quelldaten zu verarbeiten, damit zukünftige Aktualisierungen weiterhin erfolgreich sind. Power Query stellt Transformationen bereit, die eine Abfrage robust machen, wenn sich die Zeilen, Spalten oder Werte in einer Datenquelle ändern.

Definieren Sie den Gültigkeitsbereich Ihrer Abfrage, einschließlich ihrer Aufgaben und ihrer Bedeutung in Bezug auf Struktur, Layout, Spaltennamen, Datentypen und alle anderen relevanten Komponenten.

Die folgenden Transformationen können dazu beitragen, dass eine Abfrage stabil für Änderungen bleibt:

Parameter verwenden

Verwenden Sie Power Query Parameter, um Werte zu speichern und zu verwalten, die Sie in Transformationen, Datenquellenfunktionen und benutzerdefinierten Funktionen wiederverwenden können. Parameter erleichtern das Aktualisieren von Abfragen, da Sie einen Wert an einem Ort ändern können, anstatt jede Abfrage zu bearbeiten, die sie verwendet. Zwei häufige Szenarien sind:

• Schrittargument: Verwenden Sie einen Parameter als Argument mehrerer Transformationen, die von der Benutzeroberfläche gesteuert werden.

  

• Argument "Benutzerdefinierte Funktion": Erstellen Sie eine neue Funktion aus einer Abfrage, und verweisen Sie als Argumente der benutzerdefinierten Funktion auf Parameter.

  

Die wichtigsten Vorteile beim Erstellen und Verwenden von Parametern sind:

• Zentrale Ansicht aller Parameter über das Fenster " Parameter verwalten ".

  

• Wiederverwendbarkeit des Parameters in mehreren Schritten oder Abfragen.

• Erleichtert die Erstellung benutzerdefinierter Funktionen.

Sie können sogar Parameter in einigen der Argumente der Datenkonnektoren verwenden. Sie können beispielsweise einen Parameter für den Servernamen erstellen, wenn Sie eine Verbindung mit Ihrer SQL Server-Datenbank herstellen. Anschließend können Sie diesen Parameter im SQL Server-Datenbankdialogfeld verwenden.

Wenn Sie den Serverspeicherort ändern, müssen Sie lediglich den Parameter für den Servernamen aktualisieren, und Ihre Abfragen werden aktualisiert.

Erstellen wiederverwendbarer Funktionen

Erstellen Sie eine Power Query benutzerdefinierte Funktion, wenn Sie denselben Satz von Transformationen auf verschiedene Abfragen oder Werte anwenden müssen. Eine Power Query benutzerdefinierte Funktion ordnet einen Satz von Eingabewerten einem einzelnen Ausgabewert zu und wird aus systemeigenen Power Query M-Formelsprachenfunktionen und -operatoren erstellt.

Angenommen, Sie haben mehrere Abfragen oder Werte, die denselben Satz von Transformationen erfordern. Sie können eine benutzerdefinierte Funktion erstellen, die Sie später für die Abfragen oder Werte Ihrer Wahl aufrufen. Diese benutzerdefinierte Funktion spart Ihnen Zeit und hilft Ihnen bei der Verwaltung Ihrer Transformationen an einem zentralen Ort, die Sie jederzeit ändern können.

Benutzerdefinierte Power Query-Funktionen können aus vorhandenen Abfragen und Parametern erstellt werden. Stellen Sie sich beispielsweise eine Abfrage mit mehreren Codes als Textzeichenfolge vor, und Sie möchten eine Funktion erstellen, die diese Werte decodiert.

Zunächst haben Sie einen Parameter mit einem Wert, der als Beispiel dient.

Aus diesem Parameter erstellen Sie eine neue Abfrage, in der Sie die benötigten Transformationen anwenden. In diesem Fall möchten Sie den Code PTY-CM1090-LAX- in mehrere Komponenten aufteilen:

• Ursprung = PTY
• Ziel- = LAX
• Airline = CM
• FlightID = 1090

Sie können diese Abfrage dann in eine Funktion umwandeln, indem Sie mit der rechten Maustaste auf die Abfrage klicken und "Funktion erstellen" auswählen. Schließlich können Sie Ihre benutzerdefinierte Funktion in einer ihrer Abfragen oder Werte aufrufen.

Nach einigen weiteren Transformationen können Sie sehen, dass Sie die gewünschte Ausgabe erreicht und die Logik für eine solche Transformation aus einer benutzerdefinierten Funktion angewendet haben.