Power Query in Excel und Power BI: Der ultimative Datentransformator

Power Query ist Microsofts leistungsstarkes Tool für Datentransformation und -automatisierung, integriert in Excel und Power BI. Wenn Sie schon einmal Stunden damit verbracht haben, Daten manuell zu kopieren und einzufügen (und seien wir ehrlich, das kennen die meisten von uns), ist Power Query die Rettung. Dieser Blogbeitrag bietet einen informellen, aber dennoch detaillierten Einblick in Power Query, seine Vorteile und seine Anwendung – von einfachen bis hin zu fortgeschrittenen Szenarien. Wir gehen praktische Beispiele mit Verkaufs-, Handels- und technischen Daten durch, einschließlich einiger Power Query M-Codeausschnitte (der Formelsprache hinter Power Query in Excel & Power BI: Der ultimative Datentransformator). Wir zeigen Ihnen sogar einige Screenshots des Power Query-Editors in Aktion. Am Ende haben Sie, egal ob Anfänger oder Profi, ein solides Verständnis dafür, wie Sie Ihre Datenaufgaben mit Power Query beschleunigen können.

Was ist Power Query (und warum sollte es Sie interessieren)?

Power Query ist eine Engine zur Datentransformation und -aufbereitung mit benutzerfreundlicher Oberfläche, mit der Sie Daten aus verschiedenen Quellen abrufen und an Ihre Bedürfnisse anpassen können. Sie ist in Excel (Sie kennen sie vielleicht als „Abrufen und Transformieren“ auf der Registerkarte „Daten“) und im Power Query-Editor von Power BI Desktop verfügbar. Kurz gesagt: Mit Power Query können Sie Daten verbinden , transformieren und bereinigen , Daten aus verschiedenen Quellen kombinieren und die optimierten Daten anschließend dort laden , wo Sie sie benötigen. Dieser Prozess wird oft als ETL (Extrahieren, Transformieren, Laden) bezeichnet.

Warum Power Query? Weil es den Zeitaufwand für die Bearbeitung unübersichtlicher Daten drastisch reduziert. Tatsächlich verbringen Geschäftsanwender oft bis zu 80 % ihrer Zeit mit der Datenaufbereitung (Bereinigung, Umstrukturierung, Fehlerbehebung), was die eigentliche Analyse verzögert. Power Query löst dieses Problem, indem es Ihnen einen wiederholbaren Datenbereinigungsprozess ermöglicht , den Sie bei jeder Datenaktualisierung mit einem Klick aktualisieren können. Schluss mit der wöchentlichen manuellen Bereinigung neuer Datenexporte! Es bietet außerdem eine riesige Bibliothek mit Transformationen (über 350 Funktionen) und Konnektoren zu Hunderten von Quellen, sodass Sie mit nahezu allen Daten in jeder Form arbeiten können.

Vereinfacht ausgedrückt ist Power Query wie eine Zauberküche für Daten : Sie geben Rohzutaten (Rohdaten aus Dateien, Datenbanken usw.) ein, folgen einem Rezept zum Zerkleinern und Kochen (transformieren die Daten durch Filtern, Pivotieren, Zusammenführen usw.) und servieren anschließend das fertige Gericht (laden die bereinigten Daten in Excel oder Power BI). Und das Beste daran: Das Rezept wird gespeichert, sodass Sie es jederzeit für neue Rohdatenchargen wiederverwenden können.

Logo from Power Query principle

Wichtige Phasen der Verwendung von Power Query

1. Verbinden – Stellen Sie eine Verbindung zu einer oder mehreren Datenquellen her (Excel-Dateien, CSVs, Datenbanken, Onlinedienste usw.).

2. Transformieren – Bereinigen und formen Sie die Daten (z. B. entfernen Sie unerwünschte Spalten, filtern Sie Zeilen, ändern Sie Datentypen, teilen oder führen Sie Spalten zusammen), während die Quelldaten unberührt bleiben .

3. Kombinieren – Kombinieren Sie bei Bedarf Daten aus mehreren Quellen, z. B. durch Zusammenführen von Abfragen (Verknüpfen von Tabellen) oder Anhängen von Abfragen (Stapeln von Datensätzen). So erhalten Sie eine einheitliche Ansicht Ihrer Daten.

4. Laden – Sobald die Daten Ihren Wünschen entsprechen, laden Sie sie in Excel (als Tabelle oder in das Datenmodell) oder in das Power BI-Modell. Anschließend können Sie Pivot-Tabellen, Diagramme oder Berichte darauf erstellen. Klicken Sie nach jeder Aktualisierung der Quelldaten auf „Aktualisieren“ , um alle Schritte erneut auszuführen und die endgültige Ausgabe zu aktualisieren.

Diese Phasen machen Power Query zu einem bahnbrechenden Werkzeug für die Automatisierung : Sie definieren Ihre Abfrageschritte einmal und können dann wiederholt aktualisieren, um aktuelle Daten zu erhalten, ohne manuelle Arbeit wiederholen zu müssen. Es eignet sich für alles, von einmaligen Datenbereinigungsaufgaben bis hin zu robusten, automatisierten Datenpipelines, die Ihre Dashboards versorgen.

Erste Schritte: Power Query in Excel und Power BI

Der Einstieg in Power Query ist einfach, da es bereits in Excel und Power BI integriert ist. Sie finden es unter der Registerkarte „Daten“ im Abschnitt „Daten abrufen und transformieren“ von Excel. Klicken Sie beispielsweise auf „Daten abrufen“ -> „Aus Datei“ -> „Aus Excel-Arbeitsmappe“ (oder CSV, Datenbank usw.), um Daten zu importieren, und wählen Sie dann „Daten transformieren“ , um sie im Power Query-Editor zu öffnen. In Power BI Desktop starten Sie Power Query, indem Sie im Menüband „Start“ auf die Schaltfläche „Daten transformieren “ klicken (dadurch wird das Fenster des Power Query-Editors geöffnet).

Power Query-Editor-Oberfläche: Sobald Sie sich im Power Query-Editor befinden, wird Ihnen eine benutzerfreundliche Oberfläche mit einigen wichtigen Teilen angezeigt:

Das Power Query-Editor-Fenster in Power BI Desktop (und ähnlich in Excel) verfügt über ein Menüband mit Transformationsbefehlen (oben), einen Abfragebereich , in dem alle Abfragen aufgelistet sind (links, mit 1 gekennzeichnet), die Datenvorschau der ausgewählten Abfrage (Mitte, mit 3 gekennzeichnet) und den Abfrageeinstellungsbereich (rechts, mit 4 gekennzeichnet), der den Namen, die Eigenschaften und, am wichtigsten, die angewendeten Schritte für diese Abfrage enthält. Jede Transformation, die Sie anwenden (wie ein Filter oder eine Spaltenumbenennung), wird als Schritt in der Liste „Angewandte Schritte“ aufgezeichnet. Sie können auf diese Schritte klicken, um den Datenstatus bei diesem Schritt anzuzeigen, Schritte neu anzuordnen oder zu löschen und Schritte der Übersichtlichkeit halber umzubenennen. Dieser visuelle Schritt-für-Schritt-Verlauf ist eine der Superkräfte von Power Query – es ist, als ob ein automatisch aufgezeichnetes Makro für Ihre Datentransformationen vorhanden wäre.

The Power Query editor is the same in both Excel & Power BI

Beachten Sie, dass über der Datenvorschau eine Formelleiste mit dem M-Code für den ausgewählten Schritt angezeigt wird. Power Query generiert diesen M-Code automatisch im Hintergrund, während Sie herumklicken und Transformationen anwenden. Sie müssen keinen Code schreiben, um Power Query zu verwenden. Sie können den Code jedoch im erweiterten Editor anzeigen oder optimieren, wenn Sie technischere Details wünschen. (Pro-Benutzer können über die Registerkarte „Start“ auf den erweiterten Editor zugreifen und das vollständige Skript ihrer Abfrage anzeigen. Er eignet sich hervorragend zum Erlernen der M-Sprache oder zum Optimieren komplexer Transformationen.)

Tipp: Die Benutzeroberfläche von Power Query ist größtenteils identisch mit der von Excel und Power BI. Die in einem der beiden Programme erworbenen Kenntnisse sind daher auch auf das andere Programm übertragbar. Der Hauptunterschied besteht darin, wohin die Daten nach dem Schließen und Laden gelangen: In Excel laden Sie sie möglicherweise in eine Tabelle in einem Arbeitsblatt oder in das Datenmodell, während die Daten in Power BI immer in das Datenmodell zur Erstellung von Visualisierungen gelangen.

Nachdem wir nun wissen, wo wir Power Query finden und wie der Editor aussieht, können wir mit den eigentlichen Transformationen beginnen!

Power Query-Grundlagen: Bereinigen und Formen von Daten

Mit Power Query können Sie in seiner grundlegenden Anwendung alle Arten von Datenbereinigungs- und -strukturierungsaufgaben mit nur wenigen Klicks durchführen. Viele Aufgaben, die in Excel mühsam wären (oder das Schreiben von Formeln/VBA erfordern), sind mit Power Query kinderleicht. Hier sind einige alltägliche Aufgaben, die Sie im Power Query-Editor erledigen können:

• Zeilen filtern – Entfernen Sie unerwünschte Zeilen oder behalten Sie nur diejenigen, die bestimmte Kriterien erfüllen (z. B. Nullen herausfiltern oder Zeilen behalten, bei denen Region = „EU“).

• Spalten sortieren oder umbenennen – Sortieren Sie Ihre Daten oder benennen Sie Spalten in benutzerfreundliche Namen um.

• Spalten entfernen oder auswählen : Sie können nicht benötigte Spalten entfernen oder nur die Spalten auswählen, die Sie benötigen (dies trägt zur Reduzierung der Datenflut bei).

• Datentypen ändern – Stellen Sie sicher, dass eine Spalte den richtigen Datentyp (Text, Zahl, Datum usw.) für eine korrekte Analyse aufweist. Konvertieren Sie beispielsweise eine Textdatumsspalte in einen tatsächlichen Datumstyp.

• Spalten teilen oder zusammenführen – Teilen Sie eine Spalte in mehrere auf (z. B. „Vollständiger Name“ in Vorname und Nachname aufteilen) oder führen Sie mehrere Spalten zu einer zusammen (z. B. Jahr und Monat zu einer einzigen Datumszeichenfolge kombinieren).

• Berechnete Spalten hinzufügen – Erstellen Sie neue Spalten basierend auf Formeln (Power Query verfügt über eine umfangreiche Funktionsbibliothek). Fügen Sie beispielsweise eine Spalte hinzu, die Gewinn = Umsatz – Kosten berechnet.

• Duplikate entfernen – Entfernen Sie einfach doppelte Zeilen basierend auf einer oder mehreren Spalten.

• Daten pivotieren oder unpivotieren – (Mehr dazu später) Wandeln Sie Zeilen in Spalten um oder umgekehrt, um die Organisation Ihrer Daten neu zu gestalten.

Die meisten dieser Transformationen können über das Menüband oder Rechtsklickmenü durchgeführt werden. Power Query wendet den Schritt an und zeigt Ihnen das Ergebnis sofort in der Vorschau an. Sollte es nicht stimmen, können Sie den Schritt anpassen oder entfernen.

Sehen wir uns ein einfaches Beispiel an. Angenommen, wir haben eine Umsatztabelle und möchten alle Transaktionen herausfiltern, deren Umsatz unter 1000 liegt (möglicherweise handelt es sich dabei um kleine Deals, die wir in unserer Zusammenfassung nicht benötigen). Im Power Query-Editor klicken wir auf das Filter-Dropdown-Menü in der Spalte „Umsatz“, legen einen Zahlenfilter für größer oder gleich 1000 fest und drücken OK. Das war‘s – die Tabellenvorschau zeigt nun nur noch die Zeilen mit Umsätzen ≥ 1000 an, und unter „Angewandte Schritte“ erscheint ein neuer Schritt „Gefilterte Zeilen“ .

Im Hintergrund wurde mithilfe der Funktion Table.SelectRows eine M-Formel generiert, die wir in der Formelleiste oder im Erweiterten Editor sehen können. Sie sieht folgendermaßen aus:

#"Filtered Rows" = Table.SelectRows(#"PreviousStep", each [Sales] >= 1000)

Hier bezieht sich #"PreviousStep" auf den Schritt vor dem Filter (Power Query benennt ihn automatisch basierend auf der letzten Aktion, z. B. einem Import- oder Änderungsschritt), und jeder [Sales] >= 1000 ist die Bedingung zum Beibehalten von Zeilen. Sie müssen diesen Code nicht manuell schreiben – er wurde für Sie erstellt, als Sie den Filter über die Benutzeroberfläche eingerichtet haben –, aber es ist gut zu wissen, was im Hintergrund passiert.

Dieses Konzept lässt sich auf alle grundlegenden Transformationen übertragen: Klicken Sie auf die UI-Option, und Power Query schreibt den M-Code für diesen Schritt. Anschließend können Sie weitere Schritte ausführen. Beispielsweise können Sie nach dem Filtern einige unnötige Spalten entfernen und einen Datentyp von Text in Zahl ändern. Jede Aktion wird zu einer neuen Zeile im Skript Ihrer Abfrage und zu einem neuen Schritt in den angewendeten Schritten.

Durch die Kombination dieser einfachen Transformationen können Sie Ihre Daten schrittweise optimieren. Schon auf dieser grundlegenden Ebene erreichen Sie viel:

• Automatisierung – Die Schritte werden beim Aktualisieren erneut ausgeführt. Wenn Sie also morgen einen neuen Datensatz erhalten, aktualisieren Sie ihn und alle Filter, Entfernungen usw. werden automatisch erneut angewendet.

• Keine Codierung erforderlich – Sie verwenden eine benutzerfreundliche Oberfläche, profitieren aber dennoch von der Robustheit einer geskripteten Transformation (die Sie jederzeit überprüfen oder ändern können).

• Jederzeit rückgängig machen/anpassen – Da die Schritte aufgezeichnet werden, können Sie einen vorherigen Schritt bearbeiten (z. B. den Filterschwellenwert auf 500 ändern), und die nachfolgenden Schritte werden entsprechend aktualisiert. Das ist wie eine zerstörungsfreie Bearbeitung Ihrer Daten.

Nachdem wir nun die Grundlagen behandelt haben, gehen wir zu fortgeschritteneren Szenarien und Transformationen über, die die Leistungsfähigkeit von Power Query demonstrieren.

Erweiterte Transformationen mit Power Query

Power Query glänzt besonders bei anspruchsvolleren Datenaufgaben. Hier untersuchen wir einige Techniken – das Zusammenführen von Abfragen, das Pivotieren von Daten und die Verwendung von Parametern –, die in realen Datenprojekten Standard sind. Diese entsprechen komplexeren Aufgaben, mit denen Sie möglicherweise schon konfrontiert waren: z. B. das Zusammenführen zweier Tabellen (anstatt zahlreicher SVERWEISE in Excel), das Umgestalten von Datenlayouts oder das Dynamisieren Ihrer Abfragen. Keine Sorge, wir erläutern jede dieser Techniken anhand eines Beispiels.

Die Power Query-Engine verfügt über integrierte Funktionen für all diese Aufgaben. Zu den erweiterten Transformationsoptionen von Microsoft zählen Zusammenführen, Anhängen, Gruppieren nach, Pivotieren und Unpivotieren . Sehen wir uns an, wie sich einige davon in der Praxis anwenden lassen.

Zusammenführen von Abfragen (Verknüpfen von Tabellen)

Das Zusammenführen in Power Query entspricht einem JOIN zwischen zwei Tabellen in einer Datenbank oder einer Suche (z. B. SVERWEIS) in Excel. Es ermöglicht Ihnen, zwei Datensätze basierend auf einem gemeinsamen Feld zu kombinieren . Dies ist besonders nützlich in Szenarien wie einer Verkaufstabelle und einer Produkttabelle , wenn Sie Produktnamen aus der Produkttabelle in die Verkaufstabelle übertragen möchten (Abgleich nach Produkt-ID). Oder nehmen wir an, Sie haben eine Liste mit Handelstransaktionen und möchten diese mit einer Tabelle mit Wechselkursen zusammenführen, um eine Währungsumrechnung hinzuzufügen – wann immer Sie Spalten aus einer anderen Tabelle basierend auf übereinstimmenden Schlüsseln übernehmen müssen, ist das Zusammenführen Ihr Favorit.

In Power Query müssen Sie beide Tabellen/Datenquellen als Abfragen laden (sie können aus unterschiedlichen Quellen stammen, z. B. eine aus einer CSV-Datei, eine andere aus einer Datenbank – das spielt keine Rolle). Anschließend führen Sie einen Merge-Vorgang durch:

1. Wählen Sie im Power Query-Editor die primäre Abfrage aus (z. B. „Sales“ ). Klicken Sie auf der Registerkarte „Start“ auf „Abfragen zusammenführen “ (oder auf „Abfragen als neu zusammenführen “, wenn Sie eine neue kombinierte Abfrage erstellen möchten, ohne die Originale zu ändern).

2. Das Dialogfeld „Zusammenführen“ wird angezeigt. Wählen Sie die zweite Abfrage (z. B. „Produkte “) zum Zusammenführen aus.

3. Wählen Sie in jeder Tabelle die entsprechende Spalte aus. Klicken Sie beispielsweise in der Tabellenvorschau „Sales“ (oberer Teil des Dialogfelds) auf die Spalte „ProductID “ und in der Tabelle „Products“ (unterer Teil des Dialogfelds) auf die Spalte „ProductID “.

4. Wählen Sie die Verknüpfungsart (Verknüpfungstyp). Die Standardeinstellung ist „Links außen“ (alle Zeilen der ersten Tabelle werden beibehalten und nur die der zweiten Tabelle werden zugeordnet), ähnlich wie bei der SVERWEIS-Funktion von Excel, die alle Verkäufe speichert und Produktinformationen anzeigt, sofern verfügbar. Je nach Szenario können Sie andere Verknüpfungsarten wählen (Rechts-, Innen-, Voll- und Anti-Verknüpfung).

5. Klicken Sie auf OK . Power Query fügt der Sales-Abfrage eine neue Spalte hinzu – eine strukturierte Spalte mit den übereinstimmenden Zeilen aus der Products-Tabelle. (Bei einer Übereinstimmung wird ein Datensatz oder eine Tabelle angezeigt. Bei keiner Übereinstimmung wird je nach Verknüpfungstyp möglicherweise „Null“ angezeigt.)

6. Um die eigentlichen Spalten aus der Tabelle „Produkte“ zu übernehmen, klicken Sie auf das Erweiterungssymbol (den kleinen Doppelpfeil oben in der neuen Spalte). Wählen Sie die Felder aus der Tabelle „Produkte“ aus, die Sie hinzufügen möchten (z. B. „Produktname“ , „Kategorie “ usw.) und deaktivieren Sie „Ursprünglichen Spaltennamen als Präfix verwenden“, um übersichtlichere Spaltennamen zu erhalten. Klicken Sie auf „OK“. Diese Spalten werden nun in die Tabelle „Verkäufe“ übernommen.

7. Benennen Sie die neuen Spalten bei Bedarf um. Sie haben die beiden Abfragen erfolgreich zu einer zusammengeführt! Die Abfrage „Sales“ enthält nun zusätzliche Spalten aus der Produktsuche.

The Merge dialog in Power Query joins a Sales table (top) with a Countries table (bottom) on a matching CountryID field. The join kind selected is Left Outer, which will keep all rows from Sales and bring in data from Countries when IDs match. The preview at the bottom shows a green check indicating that all four rows in Sales found a match in Countries.

Nach der Zusammenführung generiert Power Query einige Schritte im M-Code . Zuerst führt ein Table.NestedJoin-Schritt die Verknüpfung durch, und anschließend ein Table.ExpandTableColumn-Schritt erweitert die Ergebnisse. So könnte der M-Code für ein Zusammenführungsbeispiel aussehen (Zusammenführung einer Sales-Tabelle mit einer Country-Tabelle nach CountryID):

Lassen Sie uns das genauer analysieren: Table.NestedJoin(Source, {"CountryID"}, Countries, {"CountryID"}, "CountryData", JoinKind.LeftOuter) verwendet die Quelle (unsere Tabelle „Sales“) und verknüpft sie mit Ländern, die jeweils der Spalte „CountryID“ entsprechen. Es wird eine neue Spalte namens „CountryData“ mit übereinstimmenden Zeilen aus der Länderabfrage erstellt. Anschließend erweitert Table.ExpandTableColumn(... "CountryData", {"CountryName", "Region"}, {...}) diese Spalte „CountryData“ und extrahiert die Felder „CountryName“ und „Region“ daraus in die Haupttabelle. Unsere Sales-Daten enthalten nun diese beiden neuen Spalten (CountryName, Region).

Aus Benutzersicht mussten Sie keinen Code schreiben – Sie haben einfach den Dialog durchlaufen und Spalten ausgewählt. Es ist jedoch toll zu sehen, wie Power Query dies formalisiert, insbesondere wenn Sie Fehler beheben oder erweiterte Optimierungen vornehmen möchten.

Beispiel aus der Praxis (Verkaufs-/Handelsdaten) : Stellen Sie sich vor, Sie haben eine CSV-Datei mit Verkaufstransaktionen , die eine Spalte für den Ländercode und eine separate Länderinfotabelle enthält, die Ländercodes Ländernamen und Regionen zuordnet. Mit Merge in Power Query können Sie die Ländernamen innerhalb von Minuten in die Transaktionstabelle einfügen. Oder Sie führen für Handelsdaten beispielsweise eine Versandtabelle mit einer Zolltabelle nach Produktkategorie zusammen, um Gebühren zu berechnen. Die Kombination von Daten aus verschiedenen Quellen in einer wiederholbaren Abfrage ist äußerst leistungsstark.

Pivotieren und Entpivotieren von Daten

Hatten Sie schon einmal Daten, die für Ihre Analyse falsch ausgerichtet waren? Beispielsweise erhalten Sie möglicherweise eine Tabelle, in der jede Spalte einen Monat (Januar, Februar, März usw.) und jede Zeile ein Produkt darstellt – Sie müssen diese jedoch normalisiert analysieren (z. B. eine Zeile pro Transaktion oder eine Zeile pro Monat). Oder umgekehrt: Sie haben eine sehr detaillierte Transaktionstabelle (mit einer Zeile für jedes Produkt * jeden Monat) und möchten eine Übersichtstabelle erstellen, in der jedes Produkt eine Zeile darstellt und Sie separate Spalten für die Umsätze im Januar, Februar, März usw. haben. Hier kommen Pivoting und Unpivoting ins Spiel.

• Durch das Unpivotieren (auch als Abflachen oder Zusammenführen von Daten bezeichnet) werden Spalten in Zeilen umgewandelt. Verwenden Sie diese Option, wenn Ihre Daten über Spalten verteilt sind, die variable Werte darstellen. Wenn Sie beispielsweise Spalten für Januar, Februar und März haben, würde das Unpivotieren eine neue Spalte „Monat“ und eine entsprechende Spalte „ Wert “ erstellen, sodass Ihre Daten für jeden Monat in eine Zeile umgewandelt werden. Dies eignet sich hervorragend zum Normalisieren von Kreuztabellenberichten in ein tabellarisches Format.

• Pivotieren (das Gegenteil von Unpivotieren) wandelt Zeilen in Spalten um und aggregiert dabei. Verwenden Sie diese Option, wenn Sie Ihre Daten zusammenfassen oder in Kreuztabellen darstellen möchten. Beispiel: Wenn Sie eine Tabelle mit [Land, Datum, Umsatz] haben, in der jede Zeile einen täglichen Umsatz darstellt, können Sie nach Datum pivotieren, um Spalten für jedes Datum zu erstellen. Das Ergebnis ist eine Zeile pro Land mit dem Gesamtumsatz für das jeweilige Datum in jeder Spalte.

In Power Query sind diese Transformationen unkompliziert:

• So entpivotieren Sie Spalten: Wählen Sie die Spalten mit den Werten aus und wählen Sie „Andere Spalten entpivotieren“ (oder wählen Sie die zu entpivotierenden Spalten aus und wählen Sie „Nur ausgewählte entpivotieren“). Das Ergebnis sind zwei neue Spalten (standardmäßig „Attribut“ und „Wert“), wobei „Attribut“ die vorherige Spaltenüberschrift (z. B. „Monat“) und „Wert“ den Wert dieser Spalte für jede Zeile enthält.

• So pivotieren Sie Spalten: Wählen Sie die Spalte mit den Werten aus, die Sie in neue Spalten umwandeln möchten. Klicken Sie auf der Registerkarte „Transformieren“ auf „Spalte pivotieren“ . Wählen Sie im Dialogfeld die Spalte „Wert“ mit den Werten aus, die in die pivotierten Spalten eingefügt werden sollen. Wählen Sie eine Aggregation aus, falls für jeden Pivot-Schlüssel mehrere Zeilen vorhanden sind (z. B. Summe, Anzahl usw.). Power Query erstellt für jeden eindeutigen Wert in der Pivot-Schlüsselspalte neue Spalten und platziert die aggregierten Werte entsprechend.

Betrachten Sie beispielsweise einen kleinen Datensatz des Handelsvolumens , der wie folgt aussieht (vor der Pivotierung):

Land	Monat	Volumen
USA	Jan	500
USA	Februar	450
USA	Beschädigen	567
Kanada	Jan	300
Kanada	Februar	420
Kanada	Beschädigen	254
Panama	Jan	100
Panama	Februar	40
Panama	Beschädigen	80

Diese Daten sind „normalisiert“ (jede Zeile entspricht einem Land/Monat). Wenn wir sie so pivotieren, dass sie eine Spalte pro Monat enthalten, erhalten wir eine Tabelle mit einer Zeile pro Land und Spalten für die Umsätze von Januar, Februar und März:

Land	Jan	Februar	Beschädigen
Kanada	300	420	254
Panama	100	40	80
USA	500	450	567

Dies wurde durch die Pivotierung der Spalte „Monat“ erreicht, wobei „Volumen“ als Werte und „Summe“ als Aggregat verwendet wurden (in diesem Fall war jeder Ländermonat eindeutig, sodass die Summe trivial ist). Im M von Power Query könnte der Pivot-Schritt folgendermaßen aussehen:

#"Pivoted Table" = Table.Pivot(#"PreviousStep", List.Distinct(#"PreviousStep"[Month]), "Month", "Volume", List.Sum)

Hier hat Table.Pivot die Tabelle aus dem vorherigen Schritt übernommen, List.Distinct(...[Month]) lieferte die Liste der eindeutigen Monatswerte, die in Spalten umgewandelt werden sollen, „Month“ ist die Pivot-Schlüsselspalte, „Volume“ die zu aggregierenden Werte und List.Sum gibt an, wie bei mehreren Einträgen aggregiert werden soll (in diesem Fall durch Summieren). Nach diesem Schritt werden unsere Daten wie oben gezeigt pivotiert.

Ebenso würde eine Unpivot-Operation in M die Funktionen Table.UnpivotOtherColumns oder Table.Unpivot verwenden. Wenn wir beispielsweise die pivotierte Tabelle zurück in das Format mit drei Zeilen pro Land unpivotieren möchten, könnten wir die Monatsspalten unpivotieren:

#"Unpivoted Columns" = Table.UnpivotOtherColumns(PivotedTable, {"Country"}, "Month", "Volume")

Dies bedeutet, dass die PivotTable übernommen wird, die Spalte „Land“ unverändert bleibt und alle anderen Spalten in zwei Spalten mit den Namen „Monat“ (mit dem alten Spaltennamen) und „Volumen“ (mit dem Wert) aufgeteilt werden. Wir erhalten dann die ursprüngliche Liste der Zeilen mit den Daten „Land, Monat und Volumen“ zurück.

Wann sollte Pivot verwendet werden, wann Unpivot? Wenn Ihre Daten zu breit sind (viele Spalten mit Werten wie Umfrageantworten, Monaten oder Kategorien), sollten Sie häufig Unpivot verwenden, um die Analyse zu vereinfachen (insbesondere in PivotTables oder Power BI-Visuals). Sind Ihre Daten zu groß und granular, können Sie pivotieren, um eine Übersichtsansicht zu erstellen (obwohl Sie zum Zusammenfassen auch eine PivotTable oder DAX verwenden können, formt das Pivotieren in Power Query die Daten selbst um).

Power Query macht diese Transformationen einfach und wiederholbar. Beispiel: Ein technisches Datenszenario: Sie haben Sensormesswerte mit separaten Spalten für jeden Sensor. Sie können die Pivotierung aufheben, um ein Modellierungstool zu versorgen, das eine Spalte „Sensorname“ und „Messwert“ erwartet. Oder umgekehrt: Wenn Sie Protokolle mit Einträgen haben und eine Matrix mit Zählungen nach Kategorie benötigen, können Sie die Kategoriespalte pivotieren, um eine umfassende Zusammenfassung zu erhalten.

Verwenden von Parametern für dynamische Abfragen

Parameter in Power Query sind wie benutzerdefinierte Variablen, die Ihre Abfragen dynamisch und wiederverwendbar machen. Durch das Erstellen eines Parameters können Sie bestimmte Werte (z. B. ein Filterkriterium, einen Dateipfad oder einen Schwellenwert) einfach ändern, ohne die Abfrageschritte manuell bearbeiten zu müssen. Dies ist besonders praktisch, wenn Sie dieselbe Datentransformation für verschiedene Fälle ausführen möchten – beispielsweise Verkaufsdaten für verschiedene Regionen basierend auf einem ausgewählten Parameter importieren oder einen Bericht für einen bestimmten Datumsbereich filtern, ohne die Abfrage zu duplizieren.

Erstellen eines Parameters: Im Power Query-Editor (Excel oder Power BI) können Sie Parameter über „Parameter verwalten“ -> „Neuer Parameter“ im Menüband „Start“ erstellen. Sie geben ihm einen Namen, einen Datentyp und einen aktuellen Wert (Sie können auch eine Liste zulässiger Werte oder einen Bereich angeben, wenn Sie möchten). Sie könnten beispielsweise einen Parameter namens „RegionChoice“ vom Typ „Text“ erstellen und seinen aktuellen Wert auf „Europa“ festlegen.

Sobald Sie einen Parameter haben, wird dieser im Abfragebereich (mit einem speziellen Parametersymbol) angezeigt. Sie können ihn dann in Ihren anderen Abfragen verwenden. Wie? Sie können Ihren Parameter praktisch überall dort einsetzen, wo im M-Code ein Literalwert verwendet werden könnte. Die Benutzeroberfläche ermöglicht Ihnen außerdem häufig die Referenzierung von Parametern: Wenn Sie beispielsweise den Schritt „Zeilen filtern“ auf eine Spalte anwenden, können Sie statt einer Zahl oder eines Textes zum Filtern „Parameter“ wählen und Ihren definierten Parameter als Filterwert festlegen.

Angenommen, wir haben eine Abfrage „SalesData“ , die die Spalte „Region“ enthält. Wir möchten den Regionsfilter mithilfe unseres Parameters „RegionChoice“ dynamisch gestalten. Wir filtern die Spalte „Region“ nach „RegionChoice“ (aktuell „Europe“). Im erweiterten Editor könnte dieser Schritt folgendermaßen aussehen:

#"Filtered by Region" = Table.SelectRows(SalesData, each [Region] = RegionChoice)

Die Abfrage gibt nur europäische Umsätze zurück, da RegionChoice ein Parameter ist (Wert „Europa“). Wenn wir den aktuellen Wert des Parameters in „Asien“ ändern und aktualisieren, gibt die Abfrage asiatische Umsätze zurück, ohne dass wir den Filterschritt bearbeiten müssen. Dadurch lässt sich die gleiche Logik ganz einfach für verschiedene Eingaben wiederverwenden.

Parameter können für mehr als nur Filter verwendet werden:

• Dynamische Datenquelle : z. B. ein Parameter für Dateipfad oder URL, den Sie im Quellschritt einer Abfrage verwenden. Durch Ändern des Parameters kann Ihre Abfrage auf eine andere Datei oder einen anderen API-Endpunkt verweisen. (Dies erfordert manchmal Berechtigungen für jede neue Quelle, ist aber machbar.)

• Schwellenwerte : z. B. ein Parameter zur Steuerung einer Ausreißer-Grenze (möglicherweise zum Herausfiltern von Werten über einer bestimmten Zahl) oder zum Wechseln zwischen verschiedenen Modi in einer Formel.

• Was-wäre-wenn-Analyse : In Power BI können Parameter (insbesondere mit der Funktion „Dynamische M-Abfrageparameter “) an Slicer gebunden werden, sodass Endbenutzer eines Berichts die Parameter über eine Slicer-Auswahl ändern können. Dies kann direkte Abfrageänderungen auslösen. (Das ist ein fortgeschritteneres Thema, aber für Power BI-Profis erwähnenswert – die Datenquelle muss Query Folding und den DirectQuery-Modus unterstützen.)

Ein toller Trick in Excel besteht darin, einen Power Query-Parameter sogar mit einem Zellenwert zu verknüpfen. Beispielsweise könnte eine Zelle, in die ein Benutzer eine Region eingibt, von Power Query als Parameter verwendet werden, um Daten für diese Region abzurufen. So können Sie effektiv einen einfachen interaktiven Bericht erstellen, ohne VBA schreiben zu müssen.

Technisches Beispiel: Angenommen, Sie verwalten eine Protokolldatei mit Serverereignissen und verfügen über eine Abfrage, die dieses Protokoll verarbeitet (Spalten aufteilt, Fehler filtert usw.). Sie könnten einen Parameter für „Umgebung“ (z. B. Entwicklung vs. Produktion) erstellen und damit dynamisch auswählen, welche Protokolldatei (Entwicklungsserverprotokoll vs. Produktionsserverprotokoll) verarbeitet werden soll, oder die Protokolldatensätze nach Umgebung filtern. Anschließend wird dieselbe Transformationslogik auf die Daten beider Umgebungen angewendet, indem der Parameter umgeschaltet wird.

Insgesamt verleihen Parameter Power Query mehr Flexibilität. Sie machen Ihre Abfragen anpassbarer und einfacher zu warten. Anstatt für jedes Szenario eine Abfrage zu duplizieren, parametrisieren Sie eine Abfrage und geben ihr unterschiedliche Werte.

Praxisbeispiele: Vertrieb, Handel und technische Daten

Lassen Sie uns kurz anhand einiger Beispiele aus der Praxis zusammenfassen, wie alles zusammenhängt. Diese Beispiele veranschaulichen, wie Power Query auf verschiedene Datendomänen angewendet werden kann:

• Automatisierung von Vertriebsdaten : Stellen Sie sich vor, Sie erhalten monatliche Vertriebsdaten für jede Region. Mit Power Query können Sie eine Verbindung zu einem Ordner mit all diesen CSV-Dateien herstellen und sie zu einer großen Tabelle anhängen (mithilfe von Anfügeabfragen ). Anschließend wenden Sie Transformationen an: Bereinigen Sie Produktnamen, fügen Sie eine Produktsuche hinzu, um Produktkategorien zu erhalten, filtern Sie Test- oder Dummy-Vertriebsdatensätze heraus und pivotieren Sie die Daten, um die Umsätze nach Region und Monat zusammenzufassen. Nach der Einrichtung kann diese Abfrage jeden Monat aktualisiert werden – ziehen Sie die neue Datei in den Ordner und klicken Sie auf „Aktualisieren“, um sie automatisch zu übernehmen. Schluss mit dem manuellen Konsolidieren von Excel-Dateien! Dies geschieht alles mithilfe der Abfrage (mit leicht nachvollziehbaren und überprüfbaren Schritten). Das Ergebnis könnte in ein ansprechendes Excel-Dashboard oder einen Power BI-Bericht für das Vertriebsteam einfließen.

• Handels- und Finanzdaten : Denken Sie an Handelsdaten oder Finanztransaktionen, an denen oft mehrere Systeme beteiligt sind. Sie können beispielsweise Handelsdaten aus System A und Referenzdaten aus System B abrufen. Mit Power Query können Sie beide importieren, zusammenführen (beispielsweise anhand einer Handels-ID oder eines Datums verknüpfen) und eine einheitliche Ansicht erstellen. Wenn Sie Wechselkurse oder Währungsumrechnungen anwenden müssen, können Sie die Währung per Parameter auswählen oder die neuesten Kurse sogar automatisch über einen Web-Connector abrufen. Sie können Daten auch pivotieren , um Summen nach Handelstyp zu analysieren, oder einige Berichtsdaten entpivotieren, um sie in eine nachgelagerte Berechnung einfließen zu lassen. Alle diese Transformationen werden zu aktualisierbaren Prozessen. Ein spezieller Fall: ein Rohstoffhandelsbericht, der Liefermengen (möglicherweise pivotiert nach Rohstoff) mit Preisdaten kombiniert, um einen Rentabilitätsbericht zu berechnen – Power Query kann alle erforderlichen Daten erfassen, integrieren und aufbereiten, sodass Ihr endgültiges Excel oder Power BI eine einfache Berechnung der bereinigten Daten durchführt.

• Technische Daten und Protokolle : Angenommen, Sie sind ein IT-Experte, der mit Serverprotokollen oder Leistungsmetriken arbeitet. Diese liegen oft in halbstrukturierten Textdateien vor oder benötigen eine Rauschfilterung. Mit Power Query können Sie eine Verbindung zu einer Protokolldatei herstellen (sogar zu mehreren Protokolldateien über den Ordner-Connector), Texttransformationen zum Parsen der Protokolle verwenden (nach Leerzeichen trennen, Datum/Uhrzeit extrahieren usw.), auf nur die relevanten Ereignisse filtern (z. B. Fehler oder bestimmte Ereignistypen) und eventuell Spalten hinzufügen , um Zeitunterschiede zu berechnen oder Einträge zu kategorisieren. Wenn Sie einen Parameter für die Umgebung oder das Datum haben, können Sie die Analyse einfach umschalten. Die bereinigten Protokolldaten können dann für ein Audit in Excel oder zur Visualisierung der Systemleistung in Power BI geladen werden. Wenn Sie das nächste Mal neue Protokolldateien erhalten, ersetzen Sie die Quelle oder aktualisieren den Parameter und aktualisieren – voilà, die Transformationspipeline wird erneut ausgeführt. Das ist besser, als jedes Mal ein einmaliges Skript zu schreiben.

Diese Szenarien zeigen, dass Power Query nicht an einen einzigen Datentyp gebunden ist – ob Verkaufszahlen in Excel, Handelsdaten in Datenbanken oder textbasierte Protokolle – der Workflow zum Verbinden, Transformieren und Laden bleibt konsistent. Sie definieren, was mit den Daten geschehen soll , und Power Query übernimmt die wiederholte und effiziente Ausführung .

Tipps und bewährte Methoden

Bevor wir zum Schluss kommen, hier noch einige Tipps und Best Practices, die Ihnen helfen, Power Query optimal zu nutzen:

• Planen und benennen Sie Ihre Schritte : Geben Sie Ihren Abfragen und Schritten aussagekräftige Namen. Anstatt „Geänderter Typ“ oder „Benutzerdefiniert1“ können Sie die Schritte per Doppelklick in „Herausgefilterte Nullen“ oder „Jahresspalte hinzugefügt“ umbenennen. So sind die angewendeten Schritte später leichter verständlich, wenn Sie oder jemand anderes die Abfrage überprüft.

• Frühzeitiges Filtern : Wenn Sie nur eine Teilmenge der Daten benötigen, wenden Sie Filter so früh wie möglich in Ihrer Abfrage an. Dies reduziert die Datenlast und beschleunigt die Verarbeitung. Wenn Sie beispielsweise nur die Daten der letzten 12 Monate benötigen, filtern Sie das Datum in Power Query im Voraus. (Bonus: Wenn Ihre Datenquelle eine Datenbank oder eine andere geeignete Quelle ist, kann Power Query diesen Filter in eine Abfrage der Quelle übersetzen – dies wird als Query Folding bezeichnet, d. h. es werden nur die benötigten Daten abgerufen.)

• Nicht verwendete Spalten entfernen : Entfernen Sie nicht benötigte Spalten so schnell wie möglich. Das reduziert den Datenfluss und reduziert die Datenmenge. Dies verbessert die Leistung und Übersichtlichkeit.

• Korrekte Datentypen : Überprüfen Sie stets, ob Ihre Spalten die richtigen Datentypen haben (Power Query weist die Typen möglicherweise automatisch zu, üblicherweise im Schritt „Typ ändern“). Korrekte Typen (Zahl, Datum usw.) gewährleisten die korrekte Funktion nachfolgender Operationen (wie Berechnungen oder Zusammenführungen). Wenn eine Spalte Text enthält, aber numerisch sein sollte, korrigieren Sie den Typ in Power Query, anstatt Fehler in Ihren Excel-Formeln oder -Grafiken zu entdecken.

• Komplexe Logik in Schritte aufteilen : Scheuen Sie sich nicht, Transformationen in mehrere Schritte aufzuteilen. Jeder Schritt transformiert lediglich die Daten, doch gemeinsam erzielen sie komplexe Ergebnisse. Dieser schrittweise Ansatz erleichtert das Debuggen. Sie können Schritte später bei Bedarf jederzeit kombinieren oder vereinfachen.

• Anhängen statt Zusammenführen : Beachten Sie den Unterschied: Zusammenführen fügt durch das Verbinden zweier Tabellen mehr Spalten hinzu, während Anhängen durch das Stapeln von Tabellen mehr Zeilen hinzufügt. Verwenden Sie Zusammenführen für Nachschlagevorgänge (Datenanreicherung) und Anhängen zum Konsolidieren von Datensätzen (z. B. „Alle vereinigen“). Wenn Sie viele Dateien haben (z. B. eine pro Monat), kombinieren Sie diese mit Anhängen.

• Vorsicht vor übermäßigen Schritten bei großen Datenmengen : Obwohl Power Query relativ große Datensätze verarbeiten kann, sollten Sie nur die notwendigen Transformationen durchführen und auf möglicherweise aufwändige Vorgänge (wie das Sortieren großer Datenmengen, das Zusammenführen nicht indizierter Felder usw.) achten. Wenn Sie mit umfangreichen Daten arbeiten, aktivieren Sie in den Optionen „Daten laden“ -> „Datenvorschau für Download im Hintergrund aktivieren “ (oder deaktivieren Sie diese Option), um die Leistung zu steuern. Erwägen Sie die Verwendung von Filtern, um beim Erstellen der Abfrage eine kleine Stichprobe in der Vorschau anzuzeigen.

• Aktualisieren und Testen : Testen Sie die Aktualisierung mit aktuellen Datenaktualisierungen, nachdem Sie eine Abfrage eingerichtet haben. Stellen Sie sicher, dass alle neuen oder unerwarteten Werte (z. B. neu erscheinende Spalten oder neue Kategorien) verarbeitet werden (Power Query kann Fehler ausgeben, wenn sich etwas im Quellschema ändert). Sie können eine bedingte Verarbeitung hinzufügen oder sicherstellen, dass Ihr M-Code für solche Änderungen robust ist.

• Dokumentieren und Speichern : Es empfiehlt sich, knifflige Teile Ihrer Abfrage zu dokumentieren. Sie können einen beschreibenden Schritt hinzufügen (es gibt eine Funktion zum Einfügen eines Schritts mit einer Notiz oder zum Kommentieren in einem separaten Dokument). Wenn Sie Ihre Arbeit (Excel-Arbeitsmappe oder Power BI-Datei) speichern, werden auch die Abfragen gespeichert. Sie können den M-Code (Inhalt des erweiterten Editors) zur Sicherung oder Versionskontrolle kopieren und als Text speichern.

• Lernen Sie die M-Sprache (schrittweise) : Obwohl Sie vieles auch ohne Programmierkenntnisse erledigen können, kann das Erlernen von M in fortgeschrittenen Szenarien hilfreich sein. Wenn Sie wissen, wie Sie eine generierte Formel optimieren oder eine einfache if-else-Logik in eine benutzerdefinierte Spalte schreiben, können Sie die Funktionen von Power Query erweitern. Je häufiger Sie den Abfrage-Editor verwenden, desto besser lernen Sie M, da die Formelleiste den Code jedes Schritts anzeigt.

• Unabhängige Abfragen (falls zutreffend) : Wenn Sie mehrere Abfragen erstellen, können Sie eine Abfrage von einer anderen referenzieren (z. B. eine Basisabfrage erstellen und diese dann in einer anderen referenzieren, um zusätzliche Transformationen durchzuführen). Das ist in Ordnung, aber bedenken Sie, dass die Basisabfrage, wenn Sie sie referenzieren, möglicherweise trotzdem Daten lädt, auch wenn Sie sie nicht direkt verwenden. Bei Staging-Abfragen sollten Sie „Laden aktivieren“ für Zwischenabfragen deaktivieren (Rechtsklick auf Abfrage -> „Laden aktivieren“ deaktivieren), damit diese Ihr Datenmodell oder Ihre Arbeitsmappe nicht überladen. Auf diese Weise dienen sie nur als Hilfsmittel für andere Abfragen.

Mit diesen Vorgehensweisen steigern Sie die Effizienz Ihrer Power Query-Lösungen, verbessern deren Wartung und verringern die Fehleranfälligkeit. Sie nutzen Power Query nicht nur – Sie beherrschen es!

Fazit und weitere Ressourcen

Microsoft Power Query ist ein vielseitiges Tool, das Datenaufgaben deutlich vereinfacht. Wir haben mit dem Verbinden und Bereinigen von Daten begonnen und sind dann zu erweiterten Transformationen wie Zusammenführen und Pivotieren übergegangen – alles aus einer praktischen Perspektive. Sie sollten jetzt verstehen, wie Power Query mühsame Arbeitsabläufe automatisiert, egal ob Sie Verkaufsberichte erstellen, Handelsdaten analysieren oder technische Protokolle analysieren. Am besten lernen Sie, indem Sie es an Ihren Daten ausprobieren . Fangen Sie klein an, dokumentieren Sie die Schritte und steigern Sie sich. Sie werden feststellen, dass sich Aufgaben, die früher stundenlange manuelle Arbeit erforderten, mit einer gut gestalteten Power Query in Sekundenschnelle erledigen lassen.

Weitere Informationen finden Sie in den unten stehenden Ressourcen. Viel Spaß beim Abfragen!

• Offizielle Dokumentation – Power Query-Übersicht (Microsoft Learn) – Umfassende Dokumentation zu Power Query für Excel und Power BI, einschließlich Anleitungen und Konzepten. Sie ist ein idealer Ausgangspunkt für die Erkundung spezifischer Themen (wie Konnektoren oder Datenflüsse).

• Microsoft Learn-Modul: Automatisieren Sie die Datenbereinigung mit Power Query – In diesem anfängerfreundlichen Onlinekurs lernen Sie anhand praktischer Übungen und Beispiele, wie Sie Power Query in Excel verwenden, um Daten zu verbinden, zu bereinigen und zu formen.

• Power BI-Tutorial: Daten formen und kombinieren – Eine Schritt-für-Schritt-Anleitung zur Verwendung von Power Query in Power BI Desktop zum Importieren, Transformieren und Kombinieren von Daten aus verschiedenen Quellen. Praktische Beispiele für Transformationen (die meisten Techniken gelten auch für Excel).

• Power Query M-Referenz – Referenzhandbuch für die M-Formelsprache mit einer Auflistung aller verfügbaren Funktionen (nützlich beim Schreiben oder Bearbeiten von M-Code, um fortgeschrittenere Aufgaben auszuführen, die von der Benutzeroberfläche möglicherweise nicht direkt abgedeckt werden).

• Community & Blogs – Der Power Query-Tag in der Microsoft Tech Community und Blogs von Datenexperten (z. B. Ken Puls, Chris Webb) enthalten eine Fülle von Tipps, von Anfängertricks bis hin zu fortgeschrittenen Hacks. Diese können hilfreich sein, um zu sehen, wie andere echte Probleme mit Power Query lösen.

  
  

Mit diesen Ressourcen und regelmäßigem Üben werden Sie schnell vom Power Query-Neuling zum Datentransformations-Ninja. Viel Spaß beim Datenverarbeiten!