Bewährte Methoden beim Arbeiten mit Power Query

In diesem Artikel finden Sie einige Tipps und Tricks, mit denen Sie das Beste aus Ihrer Datenverarbeitung in Power Query herausholen können.

Auswahl des richtigen Connectors

Power Query bietet eine große Anzahl von Daten-Connectors. 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 jederzeit den ODBC- oder OLEDB-Connector verwenden, um eine Verbindung zu Ihrer Datenquelle herzustellen.

Die Verwendung des besten Connectors für die jeweilige Aufgabe wird Ihnen die beste Erfahrung und Leistung bieten. Wenn Sie beispielsweise für die Verbindung zu einer SQL Server-Datenbank den SQL Server Connector anstelle des ODBC-Connectors verwenden, erhalten Sie nicht nur ein wesentlich besseres Get Data-Erlebnis, sondern der SQL Server Connector bietet Ihnen auch Funktionen, die Ihr Erlebnis und Ihre Leistung verbessern können, wie z. B. das Folding von Abfragen. Wenn Sie mehr über Abfragefaltung erfahren möchten, gehen Sie zu Power Query Abfragefaltung.

Jeder Daten-Connector folgt einem Standardverfahren, wie unter Daten abrufen erläutert. Diese standardisierte Erfahrung hat eine Phase namens Datenvorschau. In dieser Phase steht Ihnen ein benutzerfreundliches Fenster zur Verfügung, in dem Sie die Daten auswählen können, die Sie aus Ihrer Datenquelle abrufen möchten, sofern der Connector dies zulässt, sowie eine einfache Datenvorschau dieser Daten. Sie können sogar mehrere Datensätze aus Ihrer Datenquelle über das Navigator-Fenster auswählen, wie in der folgenden Abbildung gezeigt.

Hinweis

Die vollständige Liste der in Power Query verfügbaren Connectors finden Sie unter Connectors in Power Query.

Früh filtern

Es ist immer empfehlenswert, Ihre Daten in den frühen Phasen Ihrer Abfrage oder so früh wie möglich zu filtern. Einige Connectoren nutzen die Vorteile Ihrer Filter durch Abfragefaltung, wie in Power Query Abfragefaltung beschrieben. Es ist auch eine bewährte Methode, Daten herauszufiltern, die für Ihren Fall nicht relevant sind. So können Sie sich besser auf Ihre Aufgabe konzentrieren, da in der Datenvorschau nur Daten angezeigt werden, die relevant sind.

Sie können das Autofilter-Menü verwenden, das eine eindeutige Liste der in Ihrer Spalte gefundenen Werte anzeigt, um die Werte auszuwählen, die Sie behalten oder herausfiltern möchten. Sie können auch die Suchleiste verwenden, um die Werte in Ihrer Spalte zu finden.

Sie können auch die Vorteile der typspezifischen Filter nutzen, wie z. B. In der vorherigen für eine Spalte mit Datum, Datumszeit oder sogar Datumszeitzone.

Mit diesen typspezifischen Filtern können Sie einen dynamischen Filter erstellen, der immer Daten abruft, die in den letzten x Sekunden, Minuten, Stunden, Tagen, Wochen, Monaten, Quartalen oder Jahren liegen, wie in der folgenden Abbildung zu sehen ist.

Hinweis

Wenn Sie mehr darüber erfahren möchten, wie Sie Ihre Daten anhand von Werten aus einer Spalte filtern können, gehen Sie zu Nach Werten filtern.

Kostenintensive Operationen zuletzt ausführen

Bestimmte Operationen erfordern das Lesen der gesamten Datenquelle, um irgendein Ergebnisse zu liefern, und sind daher in der Vorschau im Power Query Editor langsam. Wenn Sie z. B. eine Sortierung durchführen, ist es möglich, dass die ersten sortierten Zeilen am Ende der Quelldaten liegen. Um ein Ergebnis zu erhalten, muss der Sortiervorgang also zunächst alle Zeilen lesen.

Andere Operationen (z. B. Filter) müssen nicht alle Daten lesen, bevor sie Ergebnisse liefern. Stattdessen arbeiten sie mit den Daten auf eine Art und Weise, die als „Streaming“ bezeichnet wird. Die Daten „strömen“ vorbei und die Ergebnisse werden auf dem Weg zurückgegeben. Im Power Query Editor müssen solche Operationen nur so viel von den Quelldaten lesen, dass die Vorschau gefüllt wird.

Wenn möglich, führen Sie solche Streaming-Operationen zuerst durch und alle teureren Operationen zuletzt. Auf diese Weise können Sie die Zeit, die Sie mit dem Warten auf die Vorschau verbringen, jedes Mal minimieren, wenn Sie einen neuen Schritt zu Ihrer Abfrage hinzufügen.

Arbeiten Sie vorübergehend mit einer Teilmenge Ihrer Daten

Wenn das Hinzufügen neuer Schritte zu Ihrer Abfrage im Power Query Editor zu langsam ist, sollten Sie zunächst eine „Keep First Rows“-Operation (Erste Zeilen beibehalten) durchführen und die Anzahl der Zeilen, mit denen Sie arbeiten, begrenzen. Wenn Sie dann alle Schritte hinzugefügt haben, die Sie benötigen, entfernen Sie den Schritt „Erste Zeilen behalten“.

Verwendung der richtigen Datentypen

Einige Funktionen in Power Query sind kontextabhängig vom Datentyp der ausgewählten Spalte. Wenn Sie z. B. eine Datumsspalte auswählen, stehen Ihnen im Menü Spalte hinzufügen die verfügbaren Optionen unter der Spaltengruppe Datum und Uhrzeit zur Verfügung. Wenn für die Spalte jedoch kein Datentyp festgelegt ist, sind diese Optionen ausgegraut.

Ähnlich verhält es sich mit den typspezifischen Filtern, da sie für bestimmte Datentypen spezifisch sind. Wenn für Ihre Spalte nicht der richtige Datentyp definiert ist, 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 Informationen zum Datentyp aus dem Tabellenschema der Datenbank übernommen. Bei unstrukturierten Datenquellen wie TXT- und CSV-Dateien ist es jedoch wichtig, dass Sie die richtigen Datentypen für die Spalten aus dieser Datenquelle festlegen. Power Query bietet standardmäßig eine automatische Datentyperkennung für unstrukturierte Datenquellen. Mehr über diese Funktion und wie sie Ihnen helfen kann, erfahren Sie unter Datentypen.

Hinweis

Mehr über die Bedeutung von Datentypen und wie Sie mit ihnen arbeiten können, erfahren Sie unter Datentypen.

Untersuchen Ihrer Daten

Bevor Sie mit der Vorbereitung Ihrer Daten und dem Hinzufügen neuer Transformationsschritte beginnen, empfehlen wir Ihnen, die Power Query Tools zur Datenprofilerstellung zu aktivieren, um auf einfache Weise Informationen über Ihre Daten zu ermitteln.

Diese Tools zur Erstellung von Datenprofilen helfen Ihnen, Ihre Daten besser zu verstehen. Die Tools bieten Ihnen kleine Visualisierungen, die Ihnen Informationen pro Spalte anzeigen, wie z. B:

• Spaltenqualität – Liefert ein kleines Balkendiagramm und drei Indikatoren, die anzeigen, wie viele Werte in der Spalte in die Kategorien gültige, fehlerhafte oder leere Werte fallen.
• Spaltenverteilung – Bietet eine Reihe von Grafiken unter den Namen der Spalten, die die Häufigkeit und die Verteilung der Werte in jeder der Spalten zeigen.
• Profil der Spalte– Ermöglicht einen genaueren Blick auf Ihre Spalte und die dazugehörigen Statistiken.

Sie können auch mit diesen Funktionen interagieren, die Ihnen bei der Aufbereitung Ihrer Daten helfen werden.

Hinweis

Um mehr über die Tools zur Datenprofilerstellung zu erfahren, gehen Sie zu Tools zur Datenprofilerstellung.

Dokumentieren Sie Ihre Arbeit

Wir empfehlen Ihnen, Ihre Abfragen zu dokumentieren, indem Sie Ihre Schritte, Abfragen oder Gruppen je nach Bedarf umbenennen oder mit einer Beschreibung versehen.

Power Query erstellt zwar automatisch einen Schrittnamen für Sie im Bereich Angewandte Schritte, aber Sie können Ihre Schritte auch umbenennen oder ihnen eine Beschreibung hinzufügen.

Hinweis

Wenn Sie mehr über die verfügbaren Funktionen und Komponenten im Bereich Angewandte Schritte erfahren möchten, lesen Sie den Abschnitt Verwendung der Liste Angewandte Schritte.

Wählen Sie einen modularen Ansatz

Es ist durchaus möglich, eine einzige Abfrage zu erstellen, die alle Transformationen und Berechnungen enthält, die Sie eventuell benötigen. Wenn die Abfrage jedoch eine große Anzahl von Schritten enthält, ist es vielleicht eine gute Idee, die Abfrage in mehrere Abfragen aufzuteilen, wobei eine Abfrage auf die nächste verweist. Das Ziel dieses Ansatzes ist es, die Transformationsphasen zu vereinfachen und in kleinere Teile zu zerlegen, damit sie leichter zu verstehen sind.

Nehmen wir an, Sie haben eine Abfrage mit den neun Schritten, die in der folgenden Abbildung gezeigt werden.

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

Sie werden dann in einem Dialog aufgefordert, Ihrer neuen Abfrage einen Namen zu geben. Dadurch wird Ihre Abfrage effektiv in zwei Abfragen aufgeteilt. Eine Abfrage enthält alle Abfragen vor der Zusammenführung. Die andere Abfrage enthält einen ersten Schritt, der auf Ihre neue Abfrage verweist, und die restlichen Schritte, die Sie in Ihrer ursprünglichen Abfrage hatten, ab dem Schritt Zusammenführen mit Preistabelle abwärts.

Sie können auch die Abfrage-Referenzierung nutzen, wenn Sie dies für sinnvoll halten. Aber es ist eine gute Idee, Ihre Abfragen auf einem Niveau zu halten, das auf den ersten Blick nicht durch so viele Schritte abschreckend wirkt.

Hinweis

Um mehr über Abfrageverweise zu erfahren, gehen Sie zum Bereich Abfragen verstehen.

Erstellen von Gruppen

Eine gute Möglichkeit, Ihre Arbeit zu organisieren, ist die Verwendung von Gruppen im Abfragebereich.

Der einzige Zweck von Gruppen ist es, Ihnen zu helfen, Ihre Arbeit zu organisieren, indem sie als Ordner für Ihre Abfragen dienen. Sie können bei Bedarf auch Gruppen innerhalb von Gruppen erstellen. Das Verschieben von Abfragen zwischen Gruppen ist so einfach wie Ziehen und Ablegen (Drag and Drop).

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

Hinweis

Wenn Sie mehr über die verfügbaren Funktionen und Komponenten im Abfragebereich erfahren möchten, gehen Sie zu Verstehen des Abfragebereichs.

Zukunftssichere Abfragen

Stellen Sie sicher, dass Sie eine Abfrage erstellen, die bei einer zukünftigen Aktualisierung keine Probleme macht. Es gibt mehrere Funktionen in Power Query, die dafür sorgen, dass Ihre Abfrage auf Änderungen reagiert und aktualisiert werden kann, selbst wenn sich einige Komponenten Ihrer Datenquelle ändern.

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

Einige Beispiele für Transformationen, die Ihnen dabei helfen können, Ihre Abfrage widerstandsfähig gegen Änderungen 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 und entfernt werden sollen, können Sie die Funktion Untere Zeilen entfernen verwenden.

  Hinweis

  Um mehr über das Filtern Ihrer Daten nach Zeilenposition zu erfahren, gehen Sie zu Filtern einer Tabelle nach Zeilenposition.

• Wenn Ihre Abfrage eine dynamische Anzahl von Spalten hat, Sie aber nur bestimmte Spalten aus Ihrem Datensatz auswählen müssen, können Sie die Funktion Spalten auswählen verwenden.

  Hinweis

  Wenn Sie mehr über das Auswählen oder Entfernen von Spalten erfahren möchten, gehen Sie zu Auswählen oder Entfernen von Spalten.

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

  Hinweis

  Wenn Sie mehr über die Optionen zum Aufheben der Pivotierung Ihrer Spalten erfahren möchten, gehen Sie zu Spalten aufheben.

• Wenn Ihre Abfrage einen Schritt enthält, 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 geliefert haben.

  Hinweis

  Mehr über die Arbeit und den Umgang mit Fehlern finden Sie unter Umgang mit Fehlern.

Parameter verwenden

Die Erstellung von Abfragen, die dynamisch und flexibel sind, ist eine bewährte Praxis. Mit den Parametern in Power Query können Sie Ihre Abfragen dynamischer und flexibler gestalten. Ein Parameter dient zum einfachen Speichern und Verwalten eines Wertes, der auf viele verschiedene Arten wiederverwendet werden kann. Allerdings wird es häufiger in zwei Szenarien verwendet:

• Schrittargument – Sie können einen Parameter als Argument für mehrere von der Benutzeroberfläche aus gesteuerte Transformationen verwenden.

  

• Benutzerdefinierte Funktionsargumente – Sie können eine neue Funktion aus einer Abfrage erstellen und Parameter als Argumente für Ihre benutzerdefinierte Funktion referenzieren.

  

Die wichtigsten Vorteile der Erstellung und Verwendung von Parametern sind:

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

  

• Wiederverwendbarkeit des Parameters in mehreren Schritten oder Abfragen.

• Macht die Erstellung von benutzerdefinierten Funktionen einfach und unkompliziert.

Sie können sogar Parameter in einigen der Argumente der Data Connectors verwenden. Sie könnten zum Beispiel einen Parameter für Ihren Servernamen erstellen, wenn Sie sich mit Ihrer SQL Server-Datenbank verbinden. Dann können Sie diesen Parameter im SQL Server-Datenbankdialog verwenden.

Wenn Sie den Standort Ihres Servers ändern, brauchen Sie nur den Parameter für Ihren Servernamen zu aktualisieren und Ihre Abfragen werden aktualisiert.

Hinweis

Um mehr über die Erstellung und Verwendung von Parametern zu erfahren, gehen Sie zu Verwendung von Parametern.

Erstellen von wiederverwendbaren Funktionen

Wenn Sie sich in einer Situation befinden, in der Sie dieselbe Reihe von Transformationen auf verschiedene Abfragen oder Werte anwenden müssen, kann es von Vorteil sein, eine benutzerdefinierte Power Query-Funktion zu erstellen, die so oft wie nötig wiederverwendet werden kann. Eine benutzerdefinierte Power Query-Funktion ist eine Zuordnung von einer Reihe von Eingabewerten zu einem einzelnen Ausgabewert und wird aus nativen M-Funktionen und Operatoren erstellt.

Nehmen wir an, Sie haben mehrere Abfragen oder Werte, die denselben Satz von Transformationen erfordern. Sie könnten eine benutzerdefinierte Funktion erstellen, die später mit den Abfragen oder Werten Ihrer Wahl aufgerufen werden kann. Diese benutzerdefinierte Funktion spart Ihnen Zeit und hilft Ihnen bei der Verwaltung Ihrer Transformationen an einer zentralen Stelle, die Sie jederzeit ändern können.

Benutzerdefinierte Power Query-Funktionen können aus vorhandenen Abfragen und Parametern erstellt werden. Stellen Sie sich zum Beispiel eine Abfrage vor, die mehrere Codes als Textzeichenfolge enthält, und Sie möchten eine Funktion erstellen, die diese Werte entschlüsselt.

Sie beginnen mit einem Parameter, der einen Wert hat, der als Beispiel dient.

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

• Ursprung = PTY
• Ziel = LAX
• Airline = CM
• Flugnummer = 1090

Sie können diese Abfrage dann in eine Funktion umwandeln, indem Sie mit der rechten Maustaste auf die Abfrage klicken und Funktion erstellen wählen. Schließlich können Sie Ihre benutzerdefinierte Funktion in jeder Ihrer Abfragen oder Werte aufrufen, wie in der folgenden Abbildung gezeigt.

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

Hinweis

Um mehr darüber zu erfahren, wie Sie benutzerdefinierte Funktionen in Power Query erstellen und verwenden können, lesen Sie den Artikel Benutzerdefinierte Funktionen.