Fazit: Lernen Sie, wie diese großartige Funktion von Excel und Power BI namens Power Query Ihnen hilft, den Prozess des Importierens, Transformierens und Bereinigens Ihrer Daten zu automatisieren, um TONNEN von Zeit bei Ihrer Arbeit zu sparen.
Skill level: Anfänger
Video-Tutorial
Download der Beispieldateien
Die CSV-Dateien, die ich im Video verwende, stehen unten zum Download bereit. Sie müssen die Dateien aus der Zip-Datei extrahieren.
CSV Order Files For Power Query Overview.zip (337.0 KB)
Einführung in Power Query
In diesem Tutorial gebe ich eine einführende Erklärung zu Power Query. Sie werden erfahren, warum dies mein neues Lieblings-Excel-Tool für die Arbeit mit Daten ist und wie es Ihnen helfen kann, Prozesse zu automatisieren und Zeit zu sparen!
Die Power Query Datenmaschine
Ich sah mir eine Fernsehsendung über die Herstellung von Dingen an, und sie erklärten mir, wie eine Gießmaschine in einer Konditorei funktioniert.
Die Grundlagen einer Gießmaschine sind:
- Sie geben Zutaten hinein.
- Ändern Sie einige Einstellungen.
- Und es zaubert Gebäck (Kekse, Donuts, Kekse), die bereit zum Backen sind.
Wenn die Regler einmal eingestellt sind, kann der Prozess immer und immer wieder wiederholt werden, um jedes Mal perfektes Gebäck herzustellen. Appetit bekommen…? 🙂
Power Query funktioniert auf eine ganz ähnliche Weise!
- Sie fügen Ihre Datenquellen hinzu (Excel-Tabellen, CSV-Dateien, Datenbanktabellen, Webseiten, etc.)
- Durch Drücken von Schaltflächen im Power Query Editor-Fenster können Sie Ihre Daten transformieren.
- Die Daten werden in Ihr Arbeitsblatt oder Datenmodell (PowerPivot) ausgegeben, das für Pivot-Tabellen oder Berichte bereit ist.
Power Query ist wie eine Maschine, denn sobald Sie Ihre Abfrage eingerichtet haben, kann der Prozess mit einem Mausklick (Aktualisieren) jedes Mal wiederholt werden, wenn sich Ihre Daten ändern.
Wenn Sie Makros verwendet haben, um Ihre Daten umzuwandeln, können Sie dies als eine viel einfachere Alternative zu VBA betrachten, die KEINE Codierung erfordert.
Gängige Datenaufgaben leicht gemacht
Arbeiten Sie mit Daten, die aus einem Aufzeichnungssystem exportiert wurden? Das kann ein Hauptbuch, eine Buchhaltung, ein ERP, ein CRM, Salesforce.com oder ein beliebiges Berichtssystem sein, das Daten enthält.
Wenn ja, verbringen Sie wahrscheinlich viel Zeit damit, Ihre Daten zu transformieren oder neu zu gestalten, um zusätzliche Berichte, Pivot-Tabellen oder Diagramme zu erstellen.
Diese Datentransformationen können Aufgaben umfassen wie:
- Entfernen von Spalten, Zeilen, Leerzeichen
- Konvertieren von Datentypen – Text, Zahlen, Datumsangaben
- Spalten aufteilen oder zusammenführen
- Sortieren &Filtern von Spalten
- Berechnete Spalten hinzufügen
- Aggregieren oder Zusammenfassen Daten
- Suchen & Text ersetzen
- Daten für Pivot-Tabellen verwenden
Kommt Ihnen eine dieser Aufgaben bekannt vor? Wenn ja, dann klingen sie wahrscheinlich auch langweilig, repetitiv und zeitaufwändig 🙂 Glauben Sie mir, ich habe den größten Teil meiner Karriere damit verbracht, diese Aufgaben zu erledigen und versucht, schnellere Wege zu finden, um sie zu erledigen.
Glücklicherweise hat Power Query Schaltflächen, die all diese Aufgaben automatisieren!
Übersicht über das Power Query Ribbon
Beginnend in Excel 2016 für Windows wurde Power Query vollständig in Excel integriert. Es befindet sich jetzt auf der Registerkarte „Daten“ des Ribbons in der Gruppe „Get & Transform“.
In Excel 2010 und 2013 für Windows ist Power Query ein kostenloses Add-In. Nach der Installation ist die Registerkarte Power Query im Excel-Ribbon sichtbar.
Sie verwenden die Schaltflächen im Register Daten oder Power Query, um Ihre Quelldaten zu erhalten. Auch hier können Ihre Daten in Excel-Dateien, csv-Dateien, Access, SQL-Server-Datenbanken, SharePoint, Salesforce.com, Dynamics CRM, Facebook, Wikipedia, Websites und mehr gespeichert sein.
Nachdem Sie festgelegt haben, woher Ihre Daten kommen, verwenden Sie das Fenster Power Query Editor, um Transformationen an den Daten vorzunehmen.
Mit den Schaltflächen im Power Query Editor-Fenster können Sie Ihre Daten transformieren.
Denken Sie an einige der Aufgaben, die Sie immer wieder ausführen, wenn Sie die Schaltflächen in der obigen Abbildung betrachten. Jedes Mal, wenn Sie eine Schaltfläche drücken, werden Ihre Aktionen (Schritte) aufgezeichnet, und Sie können die Schritte schnell wieder anwenden, wenn Sie neue Daten erhalten, indem Sie die Abfrage aktualisieren.
Nachdem Sie Ihre Schritte abgeschlossen haben, können Sie die Daten in eine Tabelle in Ihrer Excel-Arbeitsmappe ausgeben, indem Sie auf die Schaltfläche Schließen & Laden klicken.
Sie können auch bestehende Abfragen modifizieren und Ihre Ausgabetabellen mit den geänderten oder aktualisierten Daten aktualisieren.
Beispiele für Datentransformationen
Hier sind ein paar Beispiele dafür, was Power Query mit Ihren Daten machen kann.
Unpivot-Daten für Pivot-Tabellen
Meine Lieblingsfunktion von Power Query ist die Fähigkeit, Daten zu unpivotieren. Dies ist eine Technik, mit der Sie Ihre Daten für die Quelle einer Pivot-Tabelle vorbereiten können. Dies wird auch als Normalisierung der Daten bezeichnet, um sie in ein tabellarisches Format zu bringen.
Die Daten könnten zu Beginn etwa wie folgt aussehen.
Und das Endergebnis soll so aussehen.
Power Query kann dies mit wenigen Klicks erledigen und Ihre Daten für die Verwendung in einer Pivot-Tabelle aufbereiten.
Hier finden Sie einen Artikel und ein Video darüber, wie Sie Ihre Daten mit Power Query entpivotieren können.
Sehen Sie sich meinen Artikel darüber an, wie Sie Ihre Quelldaten für eine Pivot-Tabelle strukturieren, wenn Sie nicht wissen, warum Ihre Daten für eine Pivot-Tabelle so aussehen müssen.
Tabellen mit Power Query anhängen (kombinieren)
Mit der Funktion „Anhängen“ von Power Query können Sie mehrere Tabellen kombinieren (sie vertikal stapeln), um eine große Tabelle zu erstellen. Dies kann mit mehreren Tabellen in einer Datei geschehen oder mit Daten aus verschiedenen Dateien/Quellen.
Angenommen, Sie haben einen Ordner, der CSV- oder Excel-Dateien mit Berichtsdaten für jeden Monat enthält. Werfen Sie all diese Dateien in die Power Query-Maschine, und sie wird eine schöne Tabelle ausspucken, die Sie dann zum Erstellen von Pivot-Tabellen und Diagrammen verwenden können.
Wenn die Daten in diesen Berichten auch transformiert werden müssen (Zeilen entfernen, Spalten aufteilen, Pivot-Tabellen aufheben usw.), dann kann Power Query das erledigen.), dann kann Power Query das im selben Prozess erledigen.
Sobald es eingerichtet ist, müssen Sie nur noch jeden Monat die Aktualisierungsschaltfläche drücken, wenn eine neue Datei zum Ordner hinzugefügt wird, und die Zeilen werden Ihrer Ausgabetabelle hinzugefügt.
Wie genial ist das denn! 🙂
Tabellen zusammenführen – eine VLOOKUP-Alternative
Power Query hat die Fähigkeit, Tabellen zusammenzuführen oder zu verbinden. Dies kann als Alternative zu VLOOKUP- oder INDEX/MATCH-Formeln verwendet werden.
Angenommen, Sie haben diese Datentabelle mit Verkaufsdatensätzen, und Sie verwenden ein VLOOKUP, um Informationen über das Produkt basierend auf dem Namen des verkauften Produkts einzubringen. Ihre Produktgruppeninformationen befinden sich in einer anderen Tabelle auf einem anderen Blatt oder in einer anderen Arbeitsmappe.
Die Verwendung von VLOOKUP-Formeln ist großartig, aber es kann oft bedeuten, dass Sie Tausende von Formeln zu Ihrer Arbeitsmappe hinzufügen. Das erhöht die Dateigröße und die Berechnungszeit.
Power Query macht es sehr schnell und einfach, zwei Tabellen mit nur wenigen Klicks zusammenzuführen. Es verwendet im Wesentlichen SQL-Joins, so dass Sie sogar fortgeschrittenere Zusammenführungen wie innere, äußere, linke, rechte, vollständige und Anti-Joins durchführen können.
Benutzerdefinierte Funktionen erstellen
Power Query wurde so konzipiert, dass Sie NICHT wissen müssen, wie man programmiert, um es zu benutzen. Es ist sehr einfach zu bedienen, weil Sie einfach auf Schaltflächen klicken und Filter anwenden können, wie Sie es normalerweise in Excel tun würden.
Allerdings kann Power Query programmiert werden, um benutzerdefinierte Funktionen zu erstellen. Das gibt Ihnen ein scheinbar unbegrenztes Potenzial, Ihre Daten auf fast jede erdenkliche Weise zu transformieren.
Es basiert auf der Sprache M, und die meisten Funktionen sind dem Schreiben einer Formel in Excel sehr ähnlich. Das macht es auch benutzerfreundlicher und einfacher, den Code zu erlernen.
Diese neue Sprache und der neue Satz an Funktionen bedeutet, dass es eine Menge zu lernen gibt, aber ich betrachte das als den lustigen und herausfordernden Teil. Außerdem werden die Arbeitgeber der Zukunft definitiv nach Mitarbeitern mit Power Query-Kenntnissen suchen.
Power Query zeichnet Ihre Schritte auf & Automatisiert Prozesse
Power Query erleichtert nicht nur all diese Aufgaben, sondern zeichnet auch Ihre Schritte auf, damit Sie sie NICHT immer wieder machen müssen. Das spart Ihnen eine Menge Zeit, wenn Sie jeden Tag, jede Woche oder jeden Monat die gleichen Daten aufbereiten.
Es leistet auch eine ziemlich gute Arbeit bei der Behandlung von Fehlern. Wenn sich die Struktur Ihrer Quelldaten ändert, teilt Power Query Ihnen mit, bei welchem Schritt der Fehler aufgetreten ist, und erlaubt Ihnen, ihn zu beheben. Das macht die Wartung einfach und Sie müssen Ihren Prozess nicht komplett neu machen, wenn sich etwas ändert.
Sie können Power Query verwenden, um Ihre Daten für die Verwendung in Pivot-Tabellen, Diagrammen und Dashboard-Berichten vorzubereiten. Dies ist ein wichtiger Schritt im Prozess der Zusammenfassung und Analyse von Daten.
Die Power Query Maschine & Power BI
Nun, es kann nicht gerade Kekse backen, aber Power Query ist ein ziemlich geniales Werkzeug! Es wird Ihnen eine Menge Zeit sparen, wenn Sie Ihre Daten transformieren.
Power Query ist nur ein Teil der Suite von Power BI (Business Intelligence) Produkten von Microsoft.
Wenn wir auf die Analogie des Backens von Keksen in einer Fabrik zurückgehen, können Sie sich Power Query als den ersten Schritt am Fließband vorstellen. Sobald die Kekse geformt sind, müssen wir sie backen (Pivot-Tabellen, PowerPivot) und dann für die Präsentation verpacken (Power View, Power Map, Charts, Dashboards, etc.)
Sie können sich Excel als die Fabrikhalle vorstellen, in der all diese Werkzeuge untergebracht sind. Das Spannende daran ist, dass Excel eine sehr große Zukunft vor sich hat! All diese technologischen Fortschritte werden uns helfen, unsere Daten auf neue Art und Weise sinnvoll zu nutzen, uns Zeit zu sparen und unsere Chefs zu beeindrucken 🙂
Wie bekomme ich Power Query?
Der andere schöne Teil ist, dass Power Query jetzt in Excel integriert ist, beginnend mit Excel 2016 für Windows. Wenn Sie Excel 2010 oder 2013 verwenden, ist Power Query ein kostenloses Add-in.
Ich habe eine spezielle Seite, die Ihnen dabei hilft, herauszufinden, ob Sie die richtige Version von Excel haben, um Power Query zu bekommen. Dort finden Sie auch eine vollständige Installationsanleitung und den Download-Link.
Komplette Anleitung zur Installation von Power Query
Um Ihnen eine Vorstellung von der Bedeutung dieses Tools zu geben, Power Query wurde in Excel 2016 für Windows vollständig in Excel integriert und befindet sich auf der Registerkarte „Daten“ der Multifunktionsleiste.
Es ist auch als Get & Transform bekannt, obwohl die Bezeichnung Power Query am gebräuchlichsten ist.
Zusätzliche Ressourcen
Dieser Artikel hat einen Überblick über die Grundlagen von Power Query gegeben, der Ihnen helfen sollte, einige der wichtigsten Funktionen zu verstehen. Power Query hat eine Menge Funktionen und es gibt definitiv eine Menge zu lernen.
Ich werde in den kommenden Wochen weitere How-to-Artikel und Videos veröffentlichen. Hier sind ein paar Ressourcen, die Ihnen den Einstieg erleichtern werden.
Wie Sie Ihre Daten mit Power Query unpivotieren + Video-Tutorial
Kostenloses Trainings-Webinar zu den Power Tools
Im Moment biete ich ein kostenloses Trainings-Webinar zu allen Power Tools in Excel an. Dazu gehören Power Query, Power Pivot, Power BI, Pivot-Tabellen, Makros & VBA und mehr.
Es heißt „The Modern Excel Blueprint“. Im Webinar erkläre ich, was diese Tools sind und wie sie sich in Ihren Arbeitsablauf einfügen können.
Sie werden auch lernen, wie Sie der Excel-Held Ihrer Organisation werden können, das Mädchen oder der Mann, auf den sich jeder verlässt, wenn es um Excel-Hilfe und Spaßprojekte geht.
Das Webinar findet an mehreren Tagen und zu verschiedenen Zeiten statt. Bitte klicken Sie auf den untenstehenden Link, um sich zu registrieren und Ihren Platz zu sichern.
Klicken Sie hier, um sich für das kostenlose Webinar anzumelden