Bewährte Methoden beim Arbeiten mit Power Query

Dieser Artikel enthält einige Tipps und Tricks, um Ihre Daten-Wrangling-Erfahrung in Power Query optimal zu nutzen.

Wählen Sie den richtigen Verbinder aus.

Power Query bietet zahlreiche Datenkonnektoren. Diese Connectors reichen von Datenquellen wie TXT-, CSV- und Excel-Dateien bis hin zu Datenbanken wie Microsoft SQL Server und beliebten SaaS-Diensten wie Microsoft Dynamics 365 und Salesforce. Wenn Ihre Datenquelle nicht im Fenster " Daten abrufen " aufgeführt ist, können Sie immer den ODBC- oder OLEDB-Connector verwenden, um eine Verbindung mit Ihrer Datenquelle herzustellen.

Die Verwendung des besten Connectors für die Aufgabe bietet Ihnen die beste Erfahrung und Leistung. Wenn Sie beispielsweise den SQL Server-Connector anstelle des ODBC-Connectors verwenden, wenn Sie eine Verbindung mit einer SQL Server-Datenbank herstellen, erhalten Sie nicht nur eine bessere Benutzererfahrung zum Abrufen von Daten , sondern auch der SQL Server-Connector bietet Ihnen Features, die Ihre Erfahrung und Leistung verbessern können, z. B. Abfragefaltung. Weitere Informationen zur Abfragefaltung finden Sie in Power Query im Überblick über die Abfrageauswertung und Abfragefaltung.

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.

Hinweis

Um die vollständige Liste der verfügbaren Connectors in Power Query anzuzeigen, wechseln Sie zu Connectors in Power Query.

Frühzeitig filtern

Es wird immer empfohlen, Ihre Daten in den frühen Phasen Ihrer Abfrage oder so früh wie möglich zu filtern. Einige Connectors nutzen Ihre Filter durch Abfragefaltung, wie in der Übersicht über die Abfrageauswertung und Abfragefaltung in Power Query beschrieben. Außerdem empfiehlt es sich, Daten herauszufiltern, die für Ihren Fall nicht relevant sind. Mit dieser Filterung können Sie sich besser auf Ihre aufgabe konzentrieren, indem Sie nur Daten anzeigen, die im Abschnitt "Datenvorschau" relevant sind.

Sie können das Menü "Automatischer Filter" verwenden, in dem eine unterschiedliche Liste der in Ihrer Spalte gefundenen Werte angezeigt wird, um die Werte auszuwählen, die Sie beibehalten oder herausfiltern möchten. Sie können auch die Suchleiste verwenden, 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.

Hinweis

Weitere Informationen zum Filtern Ihrer Daten basierend auf Werten aus einer Spalte finden Sie unter "Filtern nach Werten".

Teure Operationen zuletzt durchführen

Bestimmte Vorgänge erfordern das Lesen der vollständigen Datenquelle, um irgendwelche Ergebnisse zurückzugeben. Daher ist die Vorschau im Power Query-Editor langsam. Wenn Sie beispielsweise eine Sortierung durchführen, ist es möglich, dass sich die ersten sortierten Zeilen am Ende der Quelldaten befinden. Damit alle Ergebnisse zurückgegeben werden können, muss der Sortiervorgang zunächst 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.

Vorübergehendes Arbeiten mit einer Teilmenge Ihrer Daten

Wenn das Hinzufügen neuer Schritte zu Ihrer Abfrage im Power Query-Editor langsam ist, sollten Sie zuerst einen Vorgang "Erste Zeilen beibehalten" ausführen und die Anzahl der Zeilen einschränken, für die Sie arbeiten. Nachdem Sie dann alle erforderlichen Schritte hinzugefügt haben, entfernen Sie den Schritt "Erste Zeilen beibehalten".

Verwenden der richtigen Datentypen

Einige Features in Power Query sind kontextbezogen mit dem Datentyp der ausgewählten Spalte. Wenn Sie beispielsweise eine Datumsspalte auswählen, können die verfügbaren Optionen unter der Gruppe "Datum und Uhrzeit" im Menü " Spalte hinzufügen " verwendet werden. Wenn die Spalte jedoch keinen Datentypsatz aufweist, werden diese Optionen abgeblendigt.

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.

Hinweis

Weitere Informationen zur Bedeutung von Datentypen und deren Verwendung finden Sie unter "Datentypen".

Erkunden Sie Ihre Daten

Bevor Sie mit der Vorbereitung Ihrer Daten und dem Hinzufügen neuer Transformationsschritte beginnen, empfehlen wir Ihnen, die Power Query-Tools für die Datenprofilerstellung zu aktivieren, um informationen zu Ihren Daten einfach zu ermitteln.

Diese Datenprofilerstellungstools helfen Ihnen, Ihre Daten besser zu verstehen. Die Tools bieten Ihnen kleine Visualisierungen, die Ihnen Informationen pro Spalte anzeigen, z. B.:

• Spaltenqualität – Stellt ein kleines Balkendiagramm und drei Indikatoren dar, die zeigen, wie viele Werte in der Spalte auf die Kategorien gültig, Fehler oder leer fallen.
• Spaltenverteilung – Stellt eine Reihe von visuellen Elementen unter den Namen der Spalten bereit, die die Häufigkeit und Verteilung der Werte in den einzelnen Spalten präsentieren.
• Spaltenprofil – Bietet eine ausführlichere Ansicht Ihrer Spalte und der ihnen zugeordneten Statistiken.

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

Hinweis

Weitere Informationen zu den Datenprofilerstellungstools finden Sie unter "Datenprofilerstellungstools".

Dokumentieren Sie Ihre Arbeit

Es wird empfohlen, Ihre Abfragen zu dokumentieren, indem Sie Ihre Schritte, Abfragen oder Gruppen nach Bedarf umbenennen oder eine Beschreibung hinzufügen.

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.

Hinweis

Weitere Informationen zu allen verfügbaren Features und Komponenten im Bereich "Angewendete Schritte" finden Sie unter "Verwenden der Liste "Angewendete Schritte".

Nehmen Sie einen modularen Ansatz

Es ist völlig möglich, eine einzelne Abfrage zu erstellen, die alle transformationen und Berechnungen enthält, die Sie möglicherweise benötigen. Wenn die Abfrage jedoch eine große Anzahl von Schritten enthält, ist es möglicherweise sinnvoll, die Abfrage in mehrere Abfragen aufzuteilen, wobei eine Abfrage auf die nächste verweist. Ziel dieses Ansatzes ist es, Transformationsphasen in kleinere Teile zu vereinfachen und zu entkoppeln, damit sie leichter zu verstehen sind.

Angenommen, Sie haben eine Abfrage mit den neun Schritten in der folgenden Abbildung.

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 weist alle Abfragen vor dem Zusammenführen auf. 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 die Abfragenreferenzierung nach Belieben nutzen. Es ist jedoch eine gute Idee, Ihre Abfragen auf einem Niveau zu halten, das nicht sofort entmutigend wirkt, insbesondere bei der Vielzahl an Schritten.

Hinweis

Weitere Informationen zum Verweisen auf Abfragen finden Sie im Bereich "Grundlegendes zum Abfragebereich".

Erstellen von Gruppen

Eine hervorragende Möglichkeit, Ihre Arbeit organisiert zu halten, besteht darin, Gruppen im Abfragebereich zu verwenden.

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.

Hinweis

Weitere Informationen zu allen verfügbaren Features und Komponenten im Abfragebereich finden Sie unter "Grundlegendes zum Abfragebereich".

Zukunftssichere Abfragen

Es hat oberste Priorität, sicherzustellen, dass Sie eine Abfrage erstellen, die während einer zukünftigen Aktualisierung keine Probleme hat. Es gibt mehrere Features in Power Query, um Ihre Abfrage robust für Änderungen zu machen und auch dann zu aktualisieren, wenn sich einige Komponenten Ihrer Datenquelle ändern.

Es empfiehlt sich, den Umfang Ihrer Abfrage hinsichtlich ihrer Aufgaben zu definieren und zu berücksichtigen, was sie in Bezug auf Struktur, Layout, Spaltennamen, Datentypen und alle anderen Komponenten berücksichtigen soll, die Sie für den Bereich als relevant erachten.

Einige Beispiele für Transformationen, die Ihnen dabei helfen können, Ihre Abfrage für Änderungen widerstandsfähig zu machen, sind:

• Wenn Ihre Abfrage eine dynamische Anzahl von Zeilen mit Daten enthält, aber eine feste Anzahl von Zeilen, die als Fußzeile dienen, die entfernt werden soll, können Sie das Feature " Untere Zeilen entfernen " verwenden.

  Hinweis

  Weitere Informationen zum Filtern ihrer Daten nach Zeilenposition finden Sie unter "Filtern einer Tabelle nach Zeilenposition".

• Wenn Ihre Abfrage über eine dynamische Anzahl von Spalten verfügt, Sie jedoch nur bestimmte Spalten aus Ihrem Dataset auswählen müssen, können Sie das Feature "Spalten auswählen " verwenden.

  Hinweis

  Weitere Informationen zum Auswählen oder Entfernen von Spalten finden Sie unter " Auswählen oder Entfernen von Spalten".

• Wenn Ihre Abfrage eine dynamische Anzahl von Spalten aufweist und Sie nur eine Teilmenge Ihrer Spalten aufheben müssen, können Sie das Feature "Nur ausgewählte Spalten aufheben " verwenden.

  Hinweis

  Weitere Informationen zu den Optionen zum Aufheben der Pivotierung Ihrer Spalten finden Sie unter "Unpivot columns".

• Wenn Ihre Abfrage einen Schritt aufweist, der den Datentyp einer Spalte ändert, aber einige Zellen Fehler liefern, da die Werte nicht dem gewünschten Datentyp entsprechen, können Sie die Zeilen entfernen, die Fehlerwerte zurückgegeben haben.

  Hinweis

  Weitere Informationen zum Arbeiten und Umgang mit Fehlern erhalten Sie unter "Umgang mit Fehlern".

Parameter verwenden

Das Erstellen dynamischer und flexibler Abfragen ist eine bewährte Methode. Mithilfe von Parametern in Power Query können Sie Ihre Abfragen dynamischer und flexibler gestalten. Ein Parameter dient als Möglichkeit zum einfachen Speichern und Verwalten eines Werts, der auf vielfältige Weise wiederverwendet werden kann. Es wird jedoch häufiger in zwei Szenarien verwendet:

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

  

• Argument "Benutzerdefinierte Funktion": Sie können eine neue Funktion aus einer Abfrage erstellen und parameter als Argumente der benutzerdefinierten Funktion referenzieren.

  

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.

Hinweis

Weitere Informationen zum Erstellen und Verwenden von Parametern finden Sie unter "Verwenden von Parametern".

Erstellen wiederverwendbarer Funktionen

Möglicherweise befinden Sie sich in einer Situation, in der Sie denselben Satz von Transformationen auf verschiedene Abfragen oder Werte anwenden müssen. In diesem Fall kann das Erstellen einer benutzerdefinierten Power Query-Funktion, die so oft wie nötig wiederverwendet werden kann, von Vorteil sein. Eine benutzerdefinierte Power Query-Funktion ist eine Zuordnung aus einer Reihe von Eingabewerten zu einem einzelnen Ausgabewert und wird aus systemeigenen M-Funktionen und Operatoren erstellt.

Angenommen, Sie haben mehrere Abfragen oder Werte, die denselben Satz von Transformationen erfordern. Sie können eine benutzerdefinierte Funktion erstellen, die später für die Abfragen oder Werte Ihrer Wahl aufgerufen werden kann. 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.

Hinweis

Weitere Informationen zum Erstellen und Verwenden von benutzerdefinierten Funktionen in Power Query finden Sie im Artikel "Benutzerdefinierte Funktionen".