Power Query: Der endgültige Anfängerleitfaden für 2021

Hallo, willkommen zum Anfängerleitfaden für Power Query von UpSlide.
Haben Sie sich jemals gefragt, was Power Query ist? Wie es funktioniert? Wenn ja, sind Sie an der richtigen Stelle!
Pauline, unsere Power Query-Expertin aus Finance 3.1, gab uns ihre besten Tipps und Ratschläge zum Schreiben dieses Artikels.
Hier lernst du die Grundlagen der Software und kannst sogar ein bisschen üben! Bist du bereit?

Wenn Sie keine Zeit haben, es jetzt zu lesen, laden Sie die PDF-Version herunter und genießen Sie es später!

Was ist Power Query? Wann sollte ich es verwenden?

Power Query ist ein Excel-Add-In, mit dem Benutzer Daten importieren, bereinigen und konsolidieren können. Sie können Power Query verwenden, wenn:

• Verarbeitung großer Datenmengen (wobei Excel auf eine Million Zeilen beschränkt ist)
• Importieren von Daten aus verschiedenen Quellen (z. B. Excel, CSV, SharePoint, SQL und Salesforce)
• Bearbeiten, Bereinigen und Kombinieren mehrerer Tabellen, Dateien oder sogar Ordner!

Power Query ist ein benutzerfreundliches und leistungsstarkes Tool, auch für Anfänger, da Sie keine Codierungserfahrung benötigen, um loszulegen. Darüber hinaus ändert es nicht die ursprüngliche Datenquelle. Das Ergebnis ist wie eine Momentaufnahme neu gestalteter Daten, die nach Excel gebracht werden. Was ist daran so toll? Nun, da die Quelldaten nicht direkt importiert werden, wird Ihre Excel-Datei dadurch nicht schwerer (dh Ihr Computer wird nicht langsamer!).

Zusammenfassend lässt sich sagen, dass Power Query ein Datenanalysetool ist, das ETL-Prozesse (Extract-Transform-Load) einfacher und effektiver macht.

Was ist der Unterschied zwischen Power Query und Power Pivot?

Power Pivot wird häufig mit Power Query verknüpft, da es sich um ein Microsoft Office-Add-In handelt, das die Datenanalyse erleichtert. Interessanterweise können diese beiden Tools jedoch zusammenarbeiten! Power Pivot ist einfach der nächste Schritt der Datentransformation: es ermöglicht Benutzern, Datenmodelle aus Tabellen zu erstellen, die zuvor von Power Query bereinigt wurden.

Power Pivot ist zwar ähnlich, wird jedoch mehr für die Datenanalyse, zeitbezogene Berechnungen und die Generierung von Links verwendet. Einfach gesagt:

• Mit Power Query können Sie Dateien automatisch konsolidieren und erneut verarbeiten (d. H. die Standardisierung von Berichtsprozessen oder die Konsolidierung mehrerer Dateien mit demselben Format).
• Mit Power Pivot können Sie Datenbanken mit mehreren Millionen Zeilen analysieren, indem Sie aggregierte Informationen gemäß den von Ihrem Unternehmen festgelegten Regeln extrahieren.

Was ist der Unterschied zwischen Power Query und Power BI?

Die zugrunde liegende Abfragetechnologie von Power Query ist auch in Power BI enthalten.

Die Benutzeroberfläche der Power BI Desktop-Software ermöglicht es Ihnen, von den drei Technologien (Power Query, Power Pivot und Power View) zu profitieren. Mit Power BI können Sie alle Schritte der Datenanalysekette ausführen, vom Extrahieren von Daten bis zum Erstellen von Grafiken in einem interaktiven Dashboard, das Sie freigeben und analysieren können.

> Lesen Sie unseren Artikel zum Exportieren von Power BI nach Excel

Warum ist Power Query eine großartige Alternative zu Excel VBA?

Power Query kann VBA (Visual Basic for Applications) problemlos ersetzen, da Sie:

• Verarbeiten Sie Ihre Tabellen einfach durch Klicken auf Schaltflächen. Keine Programmierkenntnisse erforderlich!
• Visualisieren Sie Ihre Vorgänge Schritt für Schritt, ohne ein einziges Makro auszuführen.
• Bearbeiten Sie einfach die Reihenfolge der Schritte verschiedener Schritte durch Ziehen und Ablegen! Außerdem können Sie unnötige Schritte mit einem einfachen Klick hinzufügen und löschen.

( siehe Teil 2 – Abschnitt 3: Erste Schritte mit Power Query?)

Power Query kann VBA jedoch nicht ersetzen, wenn es um die Interaktion mit der Excel-Oberfläche, die Automatisierung von Prozessen, das Generieren neuer Dateien, das Senden von E-Mails usw. geht.

Ist Microsoft Power Query kostenlos? Gibt es eine Power Query für Mac?

JA. Power Query ist eine kostenlose Erweiterung von Excel. Das ist ein weiterer großer Vorteil des Add-Ins.
(Lesen Sie die nächste Frage, um herauszufinden, wie Sie sie auf Ihrem Excel-Desktop abrufen können)

Power Query für Mac? JA, große Neuigkeiten: Query Editor ist jetzt mit Excel für Mac kompatibel! Die Integration ist derzeit jedoch recht eingeschränkt, da Sie nur den Dateipfad ändern und vorhandene Abfragen aktualisieren können.

Teil 2: Erste Schritte mit Power Query!

Wie lade ich Power Query herunter?

Power Query ist seit 2010 für Excel verfügbar.

So fügen Sie Power Query entsprechend Ihrer Excel-Version hinzu*:

• Gute Nachrichten, Sie müssen Power Query für Excel 2019, 2016 oder Office 365 nicht herunterladen. Sie finden es im Menüband „Daten“ unter der Schaltfläche „Get & Transform“.
• Für Excel 2013 und 2010 müssen Sie das Power Query-Add-In herunterladen. Es wird auf einer neuen Registerkarte mit dem Namen „Power Query“ angezeigt (in Excel 2010 benötigen Sie eine Professional Plus mit Software Assurance-Lizenz).

* So überprüfen Sie Ihre Excel-Version:

Gehe zu Datei > Konto > Über Excel. Dann erscheint ein Fenster. Lesen Sie die ersten beiden Zahlen in der Klammer. Im folgenden Beispiel ist die Excel-Version 2016.

Weitere Informationen finden Sie in einem weiteren ausführlichen Artikel zum Installieren und Aktivieren von Power Query in Excel.

Wie aktiviere ich Power Query in Excel?

Abhängig von Ihrer Excel-Version (siehe obige Frage: Wo ist Power Query in Excel?) Sie müssen nur auf die Schaltfläche „Daten abrufen“ im Abschnitt „Get & Transform“ auf der Registerkarte „Daten“ für Excel 2016 und darüber hinaus klicken. Wechseln Sie für Excel-Versionen vor 2013 einfach zum Power Query-Menüband.

Bitte beachten Sie, dass Sie beim Aufrufen der Power Query-Oberfläche nicht mehr mit Excel interagieren können. Wenn Sie zu Excel zurückkehren müssen, müssen Sie den Power Query-Editor schließen.

Wie funktioniert Power Query?

In Excel haben Sie nur dann Zugriff auf die folgenden Funktionen, wenn Sie auf „Abfragen & Verbindungen“ klicken“:

Datenband: so suchen Sie nach Datenquellen (Menüband „Power Query“ für Excel 2010 und 2013).

Abfragen & Verbindungsbereich: Um einen schnellen Überblick über alle Verbindungen (Abfragen) zu erhalten, die Sie hergestellt haben.

Sobald Sie Power Query gestartet haben, landen Sie im Power Query-Editor, der aus 4 verschiedenen Elementen besteht:

Hauptband: Ermöglicht das Erstellen von Abfragen von Grund auf dank der Schaltflächen (wenn Sie mit der M-Sprache vertraut sind, können Sie auch auf den erweiterten Editor zugreifen, um sie manuell zu optimieren).

Abfragen: um alle Verbindungen zu Datenquellen und -streams zu verfolgen.

Datentabellenvorschau: zur Visualisierung des Datenergebnisses nach einem bestimmten Schritt.

Abfrageeinstellungen (Angewendete Schritte): um durch alle von Ihnen vorgenommenen Schritte zu navigieren. Power Query zeichnet automatisch alle vorgenommenen Transformationen auf.

• In Abschnitt 4 erfahren Sie, wie Power Query grundsätzlich funktioniert, indem Sie eine Datentabelle transformieren.
• In Abschnitt 5 wird es noch interessanter, da Sie erfahren, wie Sie Daten aus verschiedenen Quellen verarbeiten und Abfragen zusammenführen.

Wie fange ich mit Power Query an?

Stellen Sie sich die folgende Situation vor:

Sie sind der Finanzkontrolleur eines Technologieunternehmens, das Computer und Smartphones verkauft. Derzeit sind Sie in 3 Ländern tätig: Frankreich, Großbritannien und China. Jeden Monat erstellen Sie einen konsolidierten Bericht für Ihren Manager mit KPIs wie Gesamtumsatz, Umsatz nach Land oder Umsatz nach Produkt. Dazu sendet Ihnen jeder Country Manager – Dennis, Douglas und Karen – eine Datei.

Problem: Jede Datei listet die Verkäufe in der landeseigenen Stückelung auf. Es ist zu zeitaufwändig, sie in Euro umzurechnen und dann in den Abschlussbericht zu konsolidieren.

Was wäre, wenn Sie den Prozess automatisieren und Ihren Bericht in wenigen Minuten berechnen könnten, indem Sie nur Power Query verwenden?

Wäre das nicht genial? Nun, mit Power Query können Sie! Beginnen wir mit der Vorbereitung Ihrer ersten Tabelle 🙂

Zuerst benötigen Sie Daten. Laden Sie unser Power Query-Beispiel herunter und speichern Sie es auf Ihrem lokalen Server.

Befolgen Sie dann diese einfachen Schritte:

• Entpacken Sie die Datei und speichern Sie sie auf Ihrem lokalen Server.
• Öffnen Sie nun ein neues Arbeitsblatt in Excel und wechseln Sie zur Registerkarte Daten > Holen Sie sich Daten > Aus der Datei > Aus der Arbeitsmappe, wie unten gezeigt.

( wenn Sie über Excel Version 2013 oder 2010 verfügen, wechseln Sie einfach zur Registerkarte Power Query)

• Gehen Sie zu dem Pfad, in dem Sie die gerade heruntergeladenen entpackten Dateien gespeichert haben, und wählen Sie die Excel-Datei mit dem Namen „Wechselkurse“.

• Klicken Sie auf Daten transformieren.

Möglicherweise haben Sie auch bemerkt, dass einige angewendete Schritte bereits im Bereich Abfrageeinstellungen angezeigt wurden. Diese werden automatisch durchgeführt.

Lassen Sie uns nun ein wenig üben, indem wir die Datentabelle bereinigen:

• Entfernen Sie die oberen 3 Zeilen, gehen Sie zu Zeilen entfernen > Entfernen Sie die oberen Zeilen und geben Sie „3“ ein. Sie sehen die Transformation sofort in der Datentabellenvorschau sowie in den angewendeten Schritten (Abfrageeinstellungen).

• Leere Zeilen entfernen: Zeilen entfernen > Leere Zeilen entfernen (wie unten gezeigt).

• Bewerben Sie Ihre erste Zeile als Kopfzeile: Klicken Sie auf „Erste Zeile als Kopfzeile verwenden“. Wie Sie vielleicht bemerkt haben, hat Power Query automatisch einen Schritt hinzugefügt, um den Typ einiger Daten zu ändern (die Werte in der ersten Spalte sind jetzt als Datumsangaben formatiert).

• Leere Spalten entfernen: Klicken Sie auf „Spalten auswählen“ und deaktivieren Sie die Spalten 7, 8, 9 und 10.

Du machst das gut! Noch drei Schritte und Sie sind fertig!

• Spalten entfernen: Wählen Sie die Spalte „Wechselkurs“ und gehen Sie dann auf der Registerkarte „Transformieren“ zu Unpivot columns > Unpivot Other columns (Sie können auch die fünf anderen Spalten auswählen und „Unpivot columns“ auswählen).

• Benennen Sie die Spalten um: Klicken Sie auf die Kopfzellen, geben Sie in die erste „Datum“, in die zweite „Währung“ und in die dritte „Kurs“ ein.

• Filter anwenden: Klicken Sie wie in Excel auf das Dropdown-Menü der Spalte „Datum“ und wählen Sie „Dez-18“.

• Klicken Sie auf der Registerkarte Start auf Schließen und Laden, um die Abschlusstabelle in ein neues Blatt zu laden.

Gut gemacht! Sie haben Ihre erste Datentabelle geformt.

Gehen wir mit Power Query weiter

Stellen Sie sich die folgende Situation vor:

Dank Ihrer Power Query-Kenntnisse sind Sie jetzt SENIOR Financial Controller des Technologieunternehmens. Jetzt müssen Sie die Daten vom Dezember 2018 konsolidieren, die Ihnen die Ländermanager gesendet haben.

Problem: Jede Datei listet die Verkäufe in der landeseigenen Stückelung auf. Es ist zeitaufwendig, sie in Euro umzurechnen, noch mehr, um sie dann in den Abschlussbericht zu konsolidieren.

Was wäre, wenn Sie den Prozess automatisieren und Ihren Bericht nur mit Power Query in wenigen Minuten berechnen könnten?

Jetzt werden wir sehen, wie verschiedene Datenquellen konsolidiert und mit der Tabelle zusammengeführt werden, die wir im vorherigen Abschnitt vorbereitet haben.

Importieren wir zunächst den gesamten Ordner einschließlich der drei verschiedenen Quelldateien:

• Wenn Sie sich noch in Power Query befinden, wechseln Sie zum Ordner New source > File >.

( Wenn Sie wieder in Excel sind, rufen Sie Daten > aus dem Ordner ab)

• Wählen Sie den Ordner „Sales_201812“ aus, den Sie zuvor heruntergeladen und gespeichert haben. Danach klicken Sie auf OK.

Ein neues Fenster sollte angezeigt werden.

Dann müssen Sie die Abfragen zusammenführen:

• Wählen Sie „Kombinieren & Bearbeiten“ im Dropdown-Menü (unten im neuen Fenster).

• Wählen Sie die Beispieltabelle „Dec-18“ (bitte beachten Sie, dass alle Blätter gleich benannt werden müssen).

• Sie können in der ersten Spalte überprüfen, ob alle Quellen enthalten sind (klicken Sie auf Dropdown-Menü), und klicken Sie dann auf OK.

Sie befinden sich jetzt im Power Query-Editor.

• Gehen Sie zur Registerkarte Transformieren > Werte ersetzen.

• Ersetzen Sie nun „€“ durch „EUR“.

• Gehen Sie zur Registerkarte Start > Abfragen zusammenführen.

Ein neues Fenster wird angezeigt:

1. Wählen Sie die Spalte „Währung“.
2. Wählen Sie die Datei „Wechselkurse“ im Dropdown-Menü.
3. Wählen Sie die Spalte „Währung“.
4. Klicken Sie auf OK.

• Jetzt müssen Sie die Wechselkursdaten anzeigen, die Sie gerade abgerufen haben:
  1. Wählen Sie die Spalte „Wechselkurs“.
  2. Klicken Sie auf die Schaltfläche Erweitern.
  3. Überprüfen Sie nur „Rate“.
  4. Deaktivieren Sie „Original verwenden …“.
  5. Klicken Sie auf OK.

Herzlichen Glückwunsch! Sie haben gerade eine andere Datenquelle geladen, Abfragen zusammengeführt und den entsprechenden Wechselkurs abgerufen. Kein VLOOKUP oder INDEX + MATCH mehr!

Zum Abschluss erstellen wir eine Spalte „Einheitspreis EUR“, in der alle Preise in Euro umgerechnet werden:

• Wechseln Sie zur Registerkarte Spalte hinzufügen > Benutzerdefinierte Spalte.

• Ein neues Fenster erscheint. Lassen Sie uns die Formel des Preises in Euro schreiben:
  1. Nennen Sie es „Stückpreis EUR“.
  2. Doppelklicken Sie auf „Einheitspreis Lokale Währung“ (oder wählen Sie es aus und klicken Sie dann auf Einfügen).
  3. Geben Sie „/“ ein.
  4. Doppelklicken Sie auf „Bewerten“.
  5. Klicken Sie auf OK.

• Sobald die Spalte angezeigt wird, klicken Sie auf das Typensymbol (kurz vor dem Namen) und wählen Sie „Dezimalzahl“.

Wir müssen dasselbe tun, um die Spalte „Umsatz“ zu generieren:

• Wechseln Sie zur Registerkarte Spalte hinzufügen > Benutzerdefinierte Spalte.
• Ein neues Fenster erscheint. Geben wir die Umsatzformel ein:
  1. Nennen Sie es „Umsatz“.
  2. Doppelklicken Sie auf „Einheitspreis EUR“ (oder wählen Sie es aus und klicken Sie dann auf Einfügen).
  3. Geben Sie „*“ ein.
  4. Doppelklicken Sie auf „Menge“.
  5. Klicken Sie auf OK.

• Ändern Sie den Spaltentyp erneut in „Dezimalzahl“.

Zum Abschluss exportieren wir es in eine neue Datentabelle in Excel!

• Gehen Sie zur Registerkarte Start > Schließen & Laden > Schließen & Laden.

Jetzt können Sie vollständig von Excel-Funktionen profitieren, um die Daten zu analysieren, eine Pivot-Tabelle zu erstellen usw.

Wow! Wenn Sie es bis zum Ende geschafft haben, herzlichen Glückwunsch! Sie wissen jetzt, wie Power Query funktioniert.

Jetzt bist du dran!

Ich möchte es Ihnen übergeben: Haben Sie alle oben genannten Fragen zu Power Query verstanden?
Haben Sie unsere Tutorial-Dateien heruntergeladen und die obigen Richtlinien befolgt, um es selbst auszuprobieren?
Lassen Sie es uns wissen, indem Sie unten einen kurzen Kommentar hinterlassen!

Weitere Informationen zu Power Query: