Inhaltsverzeichnis

Was ist Power Query?

Power Query ist ein in Excel verfügbares Business Intelligence-Tool, mit dem Sie Daten aus vielen verschiedenen Quellen importieren und Ihre Daten nach Bedarf bereinigen, transformieren und neu gestalten können.

Sie können eine Abfrage einmal einrichten und dann mit einer einfachen Aktualisierung wiederverwenden. Es ist auch ziemlich mächtig. Power Query kann Millionen von Zeilen anschließend zur Analyse in das Datenmodell importieren und bereinigen. Die Benutzeroberfläche ist intuitiv und gut angelegt, so dass es wirklich einfach zu holen., Es ist eine unglaublich kurze Lernkurve im Vergleich zu anderen Excel-tools wie Formeln oder VBA.

Das Beste daran ist, dass Sie keinen Code lernen oder verwenden müssen, um etwas davon zu tun. Der Power Query Editor zeichnet alle Ihre Transformationen Schritt für Schritt auf und konvertiert sie in den M-Code für Sie, ähnlich wie der Makrorekorder mit VBA.

Wenn Sie Ihren eigenen M-Code bearbeiten oder schreiben möchten, können Sie dies sicherlich, müssen es aber definitiv nicht.

Holen Sie sich die Daten in diesem post Folgen.

Was Kann Power Query Tun?,

Stellen Sie sich vor, Sie erhalten monatlich einen Verkaufsbericht in einer Textdatei von Ihrem System, der so aussieht.

Jeden Monat müssen Sie in den Ordner gehen, in den die Datei hochgeladen wird, und die Datei öffnen und den Inhalt in Excel kopieren.

Sie verwenden dann die Funktion Text zu Spalte, um die Daten in neue Spalten aufzuteilen.

Das System gibt nur die ID des Verkäufers aus, daher müssen Sie den Daten eine neue Spalte hinzufügen und mit einem VLOOKUP den Verkäufer ermitteln, der jeder ID zugeordnet ist., Dann müssen Sie die Verkäufe durch Verkäufer zusammenfassen und die Provision berechnen, um auszuzahlen.

Sie müssen auch die Produkt-ID mit der Produktkategorie verknüpfen, aber nur die ersten 4 Ziffern des Produktcodes beziehen sich auf die Produktkategorie. Sie erstellen eine weitere Spalte mit der LINKEN Funktion, um die ersten 4 Ziffern des Produktcodes abzurufen, und verwenden dann einen VLOOKUP, um die Produktkategorie abzurufen. Jetzt können Sie die Daten nach Kategorie zusammenfassen.,

Vielleicht dauert es nur eine Stunde im Monat, aber es ist ziemlich sinnlose Arbeit, die nicht Spaß macht und die Zeit in Anspruch nimmt, die Sie tatsächlich damit verbringen können, die Daten zu analysieren und aussagekräftige Einblicke zu erhalten.

Mit Power Query kann dies alles monatlich auf einen Klick auf die Schaltfläche Aktualisieren automatisiert werden. Sie müssen die Abfrage nur einmal erstellen und wiederverwenden, um jeden Monat eine Stunde Arbeit zu sparen!

Wo ist Power Query?,

Power Query ist als Add-In zum Herunterladen und Installieren für Excel 2010 und 2013 verfügbar und wird als neue Registerkarte im Menüband mit der Bezeichnung Power Query angezeigt. Im Jahr 2016 wurde es in Get & Transform umbenannt und erscheint auf der Registerkarte Daten, ohne dass ein Add-In installiert werden muss.

Importieren Sie Ihre Daten mit Power Query

Importieren Sie Ihre Daten mit Power Query ist einfach. Excel bietet viele gängige Datenverbindungen, auf die über die Registerkarte Daten zugegriffen werden kann und die über den Befehl Get Data gefunden werden können.,

  • Holen Sie sich Daten aus einer einzigen Datei wie eine Excel-Arbeitsmappe, Text – oder CSV-Datei, XML – und JSON-Dateien. Sie können auch mehrere Dateien aus einem bestimmten Ordner importieren.
  • Get-Daten aus verschiedenen Datenbanken wie SQL Server, Microsoft Access, Analysis Services, SQL Server Analysis Server, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase, Teradata und SAP HANA-Datenbanken.
  • Daten von Microsoft Azure abrufen
  • Daten von Onlinediensten wie Sharepoint, Microsoft Exchange, Dynamics 365, Facebook und Salesforce abrufen.,
  • Holen Sie sich Daten aus anderen Quellen wie einer Tabelle oder einem Bereich innerhalb der aktuellen Arbeitsmappe, aus dem Web, einer Microsoft-Abfrage, Hadoop, OData-Feed, ODBC und OLEDB.
  • Wir können zwei Abfragen zusammenführen, ähnlich wie beim Verbinden von zwei Abfragen in SQL.
  • Wir können eine Abfrage an eine andere Abfrage anhängen, ähnlich einer Vereinigung von zwei Abfragen in SQL.

Hinweis: Die verfügbaren Datenverbindungsoptionen hängen von Ihrer Excel-Version ab.,

In der obersten Ebene der Menübandbefehle im Abschnitt Get & Transform der Registerkarte Daten sind einige der häufigsten Abfragetypen verfügbar. Von hier aus können wir leicht aus Text/CSV, aus dem Web und aus Tabellen – /Bereichsabfragen auf die zugreifen. Diese werden nur außerhalb des Befehls Get Data dupliziert, um die Verwendung zu vereinfachen, da Sie diese wahrscheinlich häufiger verwenden werden.,

Je nachdem, für welche Art von Datenverbindung Sie sich entscheiden, führt Sie Excel durch die eingerichtete Verbindung, und während des Vorgangs können mehrere Optionen ausgewählt werden.

Am Ende des Setup-Vorgangs gelangen Sie zum Datenvorschaufenster. Sie können hier eine Vorschau der Daten anzeigen, um sicherzustellen, dass Sie dies erwarten. Sie können die Daten dann wie besehen laden, indem Sie die Schaltfläche Laden drücken, oder Sie können mit dem Abfrageeditor fortfahren, um alle Schritte zur Datentransformation anzuwenden, indem Sie die Schaltfläche Bearbeiten drücken.,

Ein einfaches Beispiel für den Import von Daten in eine Excel-Datei

Werfen wir einen Blick auf den Import einiger Daten aus einer Excel-Arbeitsmappe in Aktion. Wir werden eine Excel-Datei namens Office Supply Sales Data importieren.xlsx. Es enthält Verkaufsdaten auf einem Blatt namens Verkaufsdaten und Kundendaten auf einem anderen Blatt namens Kundendaten. Beide Datenblätter beginnen in Zelle A1 und die erste Zeile der Daten enthält Spaltenüberschriften.

Gehen Sie zur Registerkarte Daten und wählen Sie den Befehl Get Data im Abschnitt Get & Transform Data., Gehen Sie dann zu Aus Datei und wählen Sie Aus Arbeitsmappe.

Dadurch wird ein Dateiauswahl-Menü geöffnet, in dem Sie zu der Datei navigieren können, die Sie importieren möchten. Wählen Sie die Datei aus und drücken Sie die Import-Taste.

Nach Auswahl der zu importierenden Datei wird das Fenster data preview Navigator geöffnet. Dadurch erhalten Sie eine Liste aller Objekte, die aus der Arbeitsmappe importiert werden können. Aktivieren Sie das Kontrollkästchen, um mehrere Elemente auszuwählen, da Daten aus zwei verschiedenen Blättern importiert werden. Jetzt können wir sowohl die Kundendaten als auch die Verkaufsdaten überprüfen.,

Wenn Sie auf eines der Objekte in der Arbeitsmappe klicken, wird auf der rechten Seite des Navigatorfensters eine Vorschau der Daten angezeigt. Dies ist ideal für eine Sinnesüberprüfung, um sicherzustellen, dass Sie die richtige Datei haben.

Wenn Sie zufrieden sind, dass Sie alles, was Sie brauchen, aus der Arbeitsmappe haben, können Sie entweder die Schaltfläche Bearbeiten oder Laden drücken. Über die Schaltfläche Bearbeiten gelangen Sie zum Abfrageeditor, wo Sie Ihre Daten vor dem Laden transformieren können. Durch Drücken der Schaltfläche Laden werden die Daten in Tabellen in neuen Blättern in der Arbeitsmappe geladen.,

In diesem einfachen Beispiel umgehen wir den Editor und gehen direkt zum Laden der Daten in Excel. Drücken Sie den kleinen Pfeil neben der Schaltfläche Laden, um auf die Optionen zum Laden zuzugreifen. Dadurch erhalten Sie einige weitere Ladeoptionen.

Wir werden die Daten in eine Tabelle in einem neuen Blatt laden, aber es gibt mehrere andere Optionen. Sie können die Daten auch direkt in eine Pivot-Tabelle oder ein Pivot-Diagramm laden oder das Laden der Daten vermeiden und einfach eine Verbindung zu den Daten herstellen.,

Jetzt werden die Tabellen in Excel in neue Blätter geladen und wir haben auch zwei Abfragen, die schnell aktualisiert werden können, wenn die Daten in der ursprünglichen Arbeitsmappe jemals aktualisiert werden.

Der Abfrageeditor

Nachdem Sie die Anleitung zum Verbinden Ihrer Daten durchlaufen und die Option Bearbeiten ausgewählt haben, wird Ihnen der Abfrageeditor angezeigt. Hier werden alle Schritte zur Datentransformation erstellt oder bearbeitet. Es gibt 6 Hauptbereiche im Editor, mit denen Sie sich vertraut machen können.,

  1. Das Menüband – Die Benutzeroberfläche für den Editor ist Excel sehr ähnlich und verwendet ein Visual Ribbon style Command Center. Es organisiert Datentransformationsbefehle und andere Power Query-Optionen in 5 Hauptregisterkarten.
  2. Abfrageliste-Dieser Bereich listet alle Abfragen in der aktuellen Arbeitsmappe auf. Sie können zu jeder Abfrage in diesem Bereich navigieren, um mit der Bearbeitung zu beginnen.
  3. Datenvorschau – In diesem Bereich sehen Sie eine Vorschau der Daten mit allen aktuell angewendeten Transformationsschritten., Sie können hier auch auf viele Transformationsbefehle zugreifen, entweder über die Filtersymbole in den Spaltenüberschriften oder mit einem Rechtsklick auf die Spaltenüberschrift.
  4. Formelleiste-Hier können Sie den M-Code des aktuellen Transformationsschritts sehen und bearbeiten. Jede Transformation, die Sie an Ihren Daten vornehmen, wird aufgezeichnet und erscheint als Schritt im Bereich Angewandte Schritte.
  5. Eigenschaften-Hier können Sie Ihre Abfrage benennen. Wenn Sie die Abfrage schließen und in eine Excel-Tabelle laden, erstellt Power query eine Tabelle mit demselben Namen wie die Quellabfrage, wenn der Tabellenname noch nicht verwendet wurde., Der Abfragename ist auch, wie der M-Code auf diese Abfrage verweist, wenn wir sie in einer anderen Abfrage abfragen müssen.
  6. Angewandte Schritte-Dieser Bereich ist eine chronologische Liste aller Transformationsschritte, die auf die Daten angewendet wurden. Sie können die Schritte hier durchgehen und die Änderungen im Datenvorschaubereich anzeigen. Sie können hier auch alle Schritte in der Abfrage löschen, ändern oder neu anordnen.

Der Query-Liste

Der Liste „Abfrage“ hat andere Fähigkeiten, andere als nur die Auflistung Sie alle aktuellen Arbeitsmappe Abfragen.,

Eine der Hauptfunktionen der Abfrageliste ist die Navigation. Sie müssen den Abfrageeditor nicht verlassen, um zu wechseln, an welcher Abfrage Sie arbeiten. Sie können links auf jede Abfrage klicken, um zu wechseln. Die Abfrage, auf der Sie sich gerade befinden, wird hellgrün hervorgehoben.

Wenn Sie den Editor mit der Schaltfläche Schließen und Laden beenden, werden Änderungen in allen von Ihnen bearbeiteten Abfragen gespeichert.

Sie können die Abfrageliste ausblenden, um mehr Platz für die Datenvorschau zu schaffen., Klicken Sie mit der linken Maustaste auf den kleinen Pfeil in der oberen rechten Ecke, um die Liste zwischen ausgeblendet und sichtbar umzuschalten.

Wenn Sie mit der rechten Maustaste auf eine Abfrage in der Liste klicken, stehen verschiedene Optionen zur Verfügung.

  • Kopieren und einfügen-Kopieren und Einfügen einer Abfrage, um eine weitere Kopie davon zu erstellen.
  • Delete-Löschen Sie die Abfrage. Wenn Sie versehentlich eine Abfrage löschen, gibt es keine Schaltfläche zum Rückgängig machen, aber Sie können den Abfrageeditor verlassen, ohne über Schließen und Laden zu speichern, um Ihre Abfrage wiederherzustellen.
  • Umbenennen – Benennen Sie Ihre Abfrage., Dies entspricht dem Umbenennen im Abschnitt Eigenschaften auf der linken Seite des Editors.
  • Duplicate-Erstellen Sie eine weitere Kopie der Abfrage. Dies ist dasselbe wie Kopieren und Einfügen, macht den Vorgang jedoch zu einem Schritt.
  • In Gruppe verschieben-Platzieren Sie Ihre Abfragen in einer ordnerähnlichen Struktur, um sie zu organisieren, wenn die Liste groß wird.
  • Nach oben und nach unten-Ordnen Sie die Reihenfolge neu an, in der Ihre Abfragen in der Liste oder in den Ordnergruppen angezeigt werden, die Sie Ihren organisatorischen Bemühungen hinzufügen möchten. Dies kann auch durch Ziehen und Ablegen der Abfrage an einen neuen Speicherort erfolgen.,
  • Funktion erstellen-Verwandeln Sie Ihre Abfrage in eine Abfragefunktion. Sie ermöglichen es Ihnen, einen Parameter an die Abfrage zu übergeben und Ergebnisse basierend auf dem übergebenen Parameter zurückzugeben.
  • In Parameter konvertieren-Ermöglicht das Konvertieren von Parametern in Abfragen oder Abfragen in Parameter.
  • Erweiterter Editor-Öffnen Sie den erweiterten Editor, um den M-Code für die Abfrage zu bearbeiten.
  • Eigenschaften-Ermöglicht es Ihnen, den Abfragenamen zu ändern, einen Beschreibungstext hinzuzufügen und die schnelle Datenladeoption für die Abfrage zu aktivieren.,

Wenn Sie mit der rechten Maustaste auf einen leeren Bereich in der Abfrageliste klicken, können Sie eine neue Abfrage erstellen.

Die Datenvorschau

Die Hauptaufgabe des Datenvorschaubereichs besteht darin, Transformationsschritte auf Ihre Daten anzuwenden und eine Vorschau dieser Schritte anzuzeigen, die Sie anwenden.

Im Bereich Datenvorschau können Sie Spalten mit verschiedenen Methoden auswählen. Eine Spalte wird bei der Auswahl hellgrün hervorgehoben.

  • Wählen Sie mit einem Linksklick auf die Spaltenüberschrift eine einzelne Spalte aus.,
  • Wählen Sie mehrere benachbarte Spalten mit einem Linksklick auf die erste Spaltenüberschrift aus, halten Sie dann die Umschalttaste gedrückt und klicken Sie mit der linken Maustaste auf die letzte Spaltenüberschrift.
  • Wählen Sie mehrere nicht benachbarte Spalten aus, indem Sie Strg gedrückt halten und dann mit der linken Maustaste auf eine beliebige Spaltenüberschrift klicken.

Sie können dann alle relevanten Datentransformationsschritte auf ausgewählte Spalten aus dem Menüband anwenden oder bestimmte Schritte können mit einem Rechtsklick auf die Spaltenüberschrift aufgerufen werden. Befehle, die für die ausgewählte Spalte oder Spalte nicht verfügbar sind, werden im Menüband ausgegraut angezeigt.,

Jede Spalte hat ein Datentypsymbol auf der linken Seite der Spaltenüberschrift. Sie können mit der linken Maustaste darauf klicken, um den Datentyp der Spalte zu ändern.

Mit der Option Gebietsschema können Sie das Datentypformat mithilfe der Konvention von verschiedenen Orten aus festlegen. Wenn Sie beispielsweise das Datum im amerikanischen m/d/JJJJ-Format anstelle des üblichen TT/MM/JJJJ anzeigen möchten, können Sie die Vereinigten Staaten als Gebietsschema auswählen.,

In der oberen linken Ecke der Datenvorschau befindet sich ein kleines Tabellensymbol, mit dem Sie mit der rechten Maustaste oder mit der linken Maustaste auf verschiedene Aktionen zugreifen können, die sich auf die gesamte Tabelle auswirken.

das Umbenennen einer Spalte ist wirklich einfach. Doppelklicken Sie mit der linken Maustaste auf eine Spaltenüberschrift, geben Sie dann Ihren neuen Namen ein und drücken Sie die Eingabetaste, wenn Sie fertig sind.

Sie können die Reihenfolge einer der Spalten mit einem Linksklick und Ziehen Aktion ändern., Der grüne Rand zwischen zwei Spalten wird zum neuen Speicherort der gezogenen Spalte, wenn Sie den Linksklick loslassen.

Jede Spalte hat auch eine filter-Schalter auf der rechten Seite. Klicken Sie mit der linken Maustaste darauf, um Ihre Daten zu sortieren und zu filtern. Dieses Filtermenü ist den Filtern in einer normalen Tabelle sehr ähnlich und funktioniert genauso.

Die Liste der angezeigten Elemente basiert auf einem Beispiel der Daten und enthält daher möglicherweise nicht alle verfügbaren Elemente in den Daten. Sie können mehr laden, indem Sie auf Mehr Text in Blau laden klicken.,

Viele Transformationen im Menübandmenü sind auch über den Datenvorschaubereich mit einem Rechtsklick auf die Spaltenüberschrift zugänglich. Einige der Aktionen, die Sie in diesem Rechtsklick-Menü auswählen, ersetzen die aktuelle Spalte. Wenn Sie eine neue Spalte basierend erstellen möchten, verwenden Sie stattdessen einen Befehl auf der Registerkarte Spalte hinzufügen.

Die angewendeten Schritte

Jede Transformation, die Sie an Ihre Daten vornehmen, wird als Schritt im Bereich Angewendete Schritte angezeigt. Außerdem können Sie durch Ihre Abfrage navigieren., Klicken Sie mit der linken Maustaste auf einen beliebigen Schritt, und die Datenvorschau wird aktualisiert, um alle Transformationen bis einschließlich dieses Schritts anzuzeigen.

Sie können jederzeit neue Schritte in die Abfrage einfügen, indem Sie den vorherigen Schritt auswählen und dann die Transformation in der Datenvorschau erstellen. Power Query fragt dann, ob Sie diesen neuen Schritt einfügen möchten. Seien Sie jedoch vorsichtig, da dies die folgenden Schritte unterbrechen kann, die sich auf etwas beziehen, das Sie geändert haben.

Sie können alle Schritte löschen, die mit dem X auf der linken Seite des Schrittnamens im Bereich Angewandte Schritte angewendet wurden., Vorsicht ist jedoch geboten, da, wenn einer der folgenden Schritte von dem Schritt abhängt, den Sie löschen möchten, Ihre Abfrage unterbrochen wird. Dies ist, wo Löschen bis zum Ende aus dem Rechtsklick-Menü kann praktisch sein.

Vielen Transformationsschritten, die in Power query verfügbar sind, sind verschiedene Benutzereingabeparameter und andere Einstellungen zugeordnet. Wenn Sie einen Filter auf die Produktspalte anwenden, um alle Elemente anzuzeigen, die nicht mit Pen beginnen, können Sie später entscheiden, dass Sie diesen Filterschritt ändern müssen, um alle Elemente anzuzeigen, die nicht Pen entsprechen., Sie können diese Änderungen aus dem angewendeten Schrittbereich vornehmen.

Einige der Schritte haben ein kleines Zahnradsymbol auf der rechten Seite. Auf diese Weise können Sie die Eingaben und Einstellungen dieses Schritts bearbeiten.

Sie können die Reihenfolge neu anordnen, in der die Schritte in Ihrer Abfrage ausgeführt werden. Klicken Sie einfach mit der linken Maustaste auf einen beliebigen Schritt und ziehen Sie ihn an einen neuen Ort. Eine grüne Linie zwischen den Schritten zeigt den neuen Standort an. Dies ist eine weitere, mit der Sie vorsichtig sein müssen, da viele Schritte von früheren Schritten abhängen und das Ändern der Reihenfolge zu Fehlern führen kann.,

Klicken Sie mit der rechten Maustaste auf einen beliebigen Schritt, um auf ein Optionsmenü zuzugreifen.

  • Einstellungen bearbeiten-Auf diese Weise können Sie die Einstellungen des Schritts ähnlich dem Zahnradsymbol auf der rechten Seite des Schritts bearbeiten.
  • Umbenennen-Hiermit können Sie die Steps-Bezeichnung umbenennen. Anstatt den generischen Namen wie „Gefilterte Zeilen“ anzuzeigen, können Sie auch so etwas wie „Gefilterte Produktzeilen auf Stiften“ anzeigen, damit Sie leicht erkennen können, was der Schritt tut.
  • Delete-Hiermit wird der aktuelle Schritt ähnlich dem X auf der linken Seite des Schritts gelöscht.,
  • Delete Until End-Hiermit können Sie den aktuellen Schritt plus alle Schritte bis zum Ende löschen. Da Schritte von vorherigen Schritten abhängen können, ist das Löschen aller Schritte nach einem Schritt eine gute Möglichkeit, Fehler zu vermeiden.
  • Schritt nach einfügen – Hiermit können Sie einen neuen Schritt nach dem aktuellen Schritt einfügen.
  • Nach oben und nach unten-So können Sie die Abfrageschritte ähnlich der Drag & Drop-Methode neu anordnen.
  • Extract Previous-Dies kann eine wirklich nützliche Option sein. Sie können eine neue Kopie der Abfrage bis zum ausgewählten Schritt erstellen.,

Die Formelleiste

Wenn Sie im Bereich Angewandte Schritte auf verschiedene Schritte des Transformationsprozesses klicken, wird die Formelleiste aktualisiert, um den M-Code anzuzeigen, der für diesen Schritt erstellt wurde. Wenn der generierte M-Code länger als die Formelleiste ist, können Sie die Formelleiste mit dem Pfeilschalter auf der rechten Seite erweitern.

Sie können den M-Code für einen Schritt direkt aus der Formelleiste bearbeiten, ohne den erweiterten Editor öffnen zu müssen., In diesem Beispiel haben wir unseren Filter von „Stift“ auf „Stuhl“ geändert, indem wir die Formelleiste eingegeben und dann die Eingabetaste gedrückt haben oder das Häkchen links verwenden, um die Änderung zu bestätigen. Drücken Sie Esc oder verwenden Sie das X links
, um Änderungen zu verwerfen.

Die Registerkarte Datei

Die Registerkarte Datei enthält verschiedene Optionen zum Speichern von Änderungen an Ihren Abfragen sowie Power-Abfrage-Optionen und Einstellungen.

  • Close & Load – Dies speichert Ihre Abfragen und lädt Ihre aktuelle Abfrage in eine Excel-Tabelle in der Arbeitsmappe.,
  • Close & Load To – Dies öffnet das Menü Daten importieren mit verschiedenen Optionen zum Laden von Daten zur Auswahl.
  • Discard & Close – Dadurch werden alle Änderungen, die Sie während Ihrer Sitzung im Editor vorgenommen haben, verworfen und der Editor geschlossen.

Beachten Sie, dass Sie die Arbeitsmappe weiterhin regelmäßig speichern müssen, um Änderungen an Abfragen beizubehalten, wenn Sie die Arbeitsmappe schließen.

& Laden und Schließen & Laden, Um Befehle sind auch über die Registerkarte „Startseite“.,

Optionen zum Laden von Daten

Wenn Sie die Option Schließen & Laden zum Beenden des Editors verwenden, wird das Menü Daten importieren geöffnet.

Sie können die Abfrage in eine Tabelle, eine Pivot-Tabelle oder ein Pivot-Diagramm laden oder nur eine Verbindung für die Abfrage erstellen. Die Option Nur Verbindung bedeutet, dass keine Datenausgabe an die Arbeitsmappe erfolgt, Sie können diese Abfrage jedoch weiterhin in anderen Abfragen verwenden. Dies ist eine gute Option, wenn die Abfrage ein Zwischenschritt in einem Datentransformationsprozess ist.,

Sie können auch den Speicherort auswählen, an den Sie in Ihre Arbeitsmappe laden möchten, wenn Sie im vorherigen Abschnitt entweder eine Tabelle, eine Pivot-Tabelle oder ein Pivot-Diagramm ausgewählt haben. Sie können eine Zelle in einem vorhandenen Arbeitsblatt auswählen oder in ein neues Blatt laden, das Excel automatisch für Sie erstellt.

Die andere Option, die Sie erhalten, ist das Hinzufügen dieser Daten zum Datenmodell. Auf diese Weise können Sie die Datenausgabe in Power Pivot verwenden und andere Datenmodellfunktionen wie das Erstellen von Beziehungen zwischen Tabellen verwenden. Das Datenmodell Excel neue effiziente Art und Weise der Speicherung und Verwendung großer Datenmengen.,

Die Abfragen & Verbindungsfenster

Wenn Sie außerhalb des Power Query Editors arbeiten, können Sie alle Abfragen in der Arbeitsmappe über die Abfragen & Verbindungsfenster anzeigen und mit ihnen interagieren. Um dies zu öffnen, wechseln Sie zur Registerkarte Daten im regulären Excel-Menüband und drücken Sie die Befehlsschaltfläche Queries & Connections im Abschnitt Queries & Connections.

Beim Öffnen wird es an die rechte Seite der Arbeitsmappe angedockt., Sie können es abdocken, indem Sie mit der linken Maustaste auf den Titel klicken und ihn ziehen. Sie können es auf die linke Seite ziehen und dort andocken oder schweben lassen. Sie können die Größe des Fensters auch ändern, indem Sie mit der linken Maustaste klicken und die Kanten ziehen.

Dies ist der Abfrageliste im Editor sehr ähnlich und Sie können viele der gleichen Aktionen mit einem Rechtsklick auf eine beliebige Abfrage ausführen.

Eine Option erwähnenswert, die nicht in der Abfrageliste Rechtsklick-Menü ist, ist die Load To Option., Auf diese Weise können Sie die Ladeoption für jede Abfrage ändern, sodass Sie alle Verbindungsabfragen nur zum Laden in eine Excel-Tabelle in der Arbeitsmappe ändern können.

Wenn Sie mit dem Mauszeiger über eine Abfrage fahren, generiert Excel eine Peek-Datenvorschau. Dies zeigt Ihnen einige grundlegende Informationen über die Abfrage.

  • Datenvorschau-Dies ist eine Live-Vorschau der Daten, ähnlich wie beim ersten Einrichten einer Abfrage.,
  • Spalten – Dadurch erhalten Sie eine Liste aller Spalten, die in den Endergebnissen der Abfrage enthalten sind, zusammen mit der Anzahl der Spalten. Wenn Sie auf eine davon klicken, wird die Spalte in der Datenvorschau hervorgehoben.
  • Zuletzt aktualisiert – Dies wird Ihnen sagen, wann die Daten zuletzt aktualisiert wurden.
  • Ladestatus-Hier wird angezeigt, ob die Daten in eine Tabelle, eine Pivot-Tabelle, ein Pivot-Diagramm oder nur in eine Verbindung geladen werden.
  • Datenquellen-Dies zeigt Ihnen die Quelle der Daten zusammen mit einer Zählung der Anzahl der Dateien, wenn es sich um eine from Folder-Abfrage handelt.,
  • Ansicht im Arbeitsblatt-Wenn Sie darauf klicken, gelangen Sie zur Ausgabetabelle, wenn die Abfrage in eine Tabelle, eine Pivot-Tabelle oder ein Pivot-Diagramm geladen wird.

Sie können auch auf diese Peek-Ansicht zugreifen, indem Sie mit der rechten Maustaste auf die Abfrage klicken und Peek anzeigen auswählen.

Es werden auch einige nützliche Meldungen in den Abfragen angezeigt & Verbindungsfenster für jede Abfrage. Es zeigt Ihnen, ob es sich bei der Abfrage nur um eine Verbindung handelt, ob beim letzten Ausführen der Abfrage Fehler aufgetreten sind oder wie viele Zeilen geladen wurden.,

Die Registerkarte Startseite

Die Registerkarte Startseite enthält alle Aktionen, Transformationen und Einstellungen, die sich auf die gesamte Tabelle auswirken.

  1. Close-Sie können auf die Close & Load und Close & Load To options von hier aus zugreifen. Diese sind auch im Menü der Registerkarte Datei verfügbar.
  2. Abfrage-Sie können die Datenvorschau für die aktuelle Abfrage oder alle Abfrageverbindungen aktualisieren., Sie können auch die Eigenschaftseinstellungen und den erweiterten Editor für die aktuelle Abfrage öffnen, und unter der Schaltfläche Verwalten können Sie die aktuelle Abfrage löschen, duplizieren oder referenzieren.
  3. Spalten verwalten-Sie können zu bestimmten Spalten navigieren und Spalten beibehalten oder entfernen.
  4. Zeilen reduzieren-Sie können die Datenzeilen in diesem Abschnitt verwalten. Es gibt viele Optionen, um bestimmte Zeilen beizubehalten oder bestimmte Zeilen zu entfernen. Behalten oder entfernen Sie die oberen N Zeilen, die unteren N Zeilen, einen bestimmten Zeilenbereich, abwechselnde Zeilen, doppelte Zeilen oder Zeilen mit Fehlern., Eine Option, die nur zum Entfernen von Zeilen verfügbar ist, besteht darin, leere Zeilen zu entfernen.
  5. Sortieren – Sie können jede Spalte in aufsteigender oder absteigender Reihenfolge sortieren.
  6. Transform-Dieser Abschnitt enthält eine Mischung nützlicher Transformationsoptionen.
    • Spalten teilen-Auf diese Weise können Sie die Daten in einer Spalte basierend auf einem Trennzeichen oder einer Zeichenlänge aufteilen.
    • Group By-Auf diese Weise können Sie Ihre Daten ähnlich einer Group By in SQL gruppieren und zusammenfassen.
    • Datentyp – Hiermit können Sie den Datentyp einer beliebigen Spalte ändern.,
    • Erste Zeile als Kopfzeile verwenden-Auf diese Weise können Sie die erste Datenzeile in Spaltenüberschriften promoten oder die Spaltenüberschriften in eine Datenzeile degradieren.
    • Werte ersetzen-Auf diese Weise können Sie jeden Wert aus einer Spalte finden und ersetzen.
  7. Kombinieren-Dieser Abschnitt enthält alle Befehle zum Verbinden Ihrer Abfrage mit anderen Abfragen. Sie können Dateien zusammenführen, anhängen oder kombinieren, wenn Sie mit einer from Folder-Abfrage arbeiten.
  8. Parameter-Mit Power Query können Sie Parameter für Ihre Abfragen erstellen., Wenn Sie beispielsweise eine from folder-Abfrage einrichten, möchten Sie möglicherweise, dass der Ordnerpfad ein Parameter ist, damit Sie den Speicherort einfach ändern können. Sie können vorhandene Parameter in diesem Abschnitt erstellen und verwalten.
  9. Datenquellen-Dieser Abschnitt enthält die Datenquelleneinstellungen einschließlich der Berechtigungsverwaltung für alle Datenquellen, auf die Kennwörter zugreifen müssen.
  10. Neue Abfrage-Sie können neue Abfragen aus neuen Datenquellen oder zuvor verwendeten Datenquellen aus diesem Abschnitt erstellen.,

Der Unterschied zwischen den Registerkarten Transform und Spalte hinzufügen

Der Großteil aller in Power query verfügbaren Transformationen kann entweder über die Registerkarte Transform oder über die Registerkarte Spalte hinzufügen zugegriffen werden.

Sie könnten denken, dass es zwischen diesen beiden Registerkarten viele Duplikationen gibt. Zum Beispiel enthalten beide Registerkarten einen From Text-Abschnitt mit vielen der gleichen Befehle. Es ist nicht wirklich der Fall, es gibt einen subtilen Unterschied!,

Wenn Sie einen Befehl auf der Registerkarte Spalte hinzufügen verwenden, der sich auf beiden Registerkarten befindet, wird eine neue Spalte mit den transformierten Daten erstellt, und die ursprüngliche Spalte bleibt intakt. Während die Verwendung des äquivalenten Befehls auf der Registerkarte Transform die ursprüngliche Spalte ändert und keine neue Spalte erstellt wird.

Dies ist ein kritischer Punkt zu beachten!

Die Registerkarte Transformieren

Die Abschnitte der Registerkarte Transformieren.

  1. Tabelle-Dieser Abschnitt enthält Befehle, die die gesamte Tabelle transformieren., Sie können Ihre Abfrage gruppieren und aggregieren, Zeilen zu Kopfzeilen promoten, Kopfzeilen zu Zeilen degradieren, Daten transponieren, Zeilenreihenfolge umkehren und Zeilen zählen.
  2. Any Column-Dieser Abschnitt enthält Befehle, die für jede Datenspalte unabhängig vom Datentyp funktionieren. Sie können den Datentyp ändern, den Datentyp automatisch erkennen und ändern, die Spaltenüberschrift umbenennen, Werte suchen und ersetzen, Werte nach unten (oder oben) füllen, um Leerzeichen oder Nullen durch den Wert darüber (oder darunter) zu ersetzen, Pivot-oder Unpivot-Spalten, Verschieben von Spalten an einen neuen Speicherort oder Konvertieren einer Spalte in eine Liste.,
  3. Textspalte-Dieser Abschnitt enthält Befehle für Textdaten. Sie können Spalten mit einem Trennzeichen aufteilen, den Fall formatieren, zuschneiden und bereinigen, zwei oder mehr Spalten zusammenführen, Text extrahieren und XML-oder JSON-Objekte analysieren.
  4. Zahlenspalte-Dieser Abschnitt enthält Befehle für numerische Daten. Sie können verschiedene Aggregationen wie Summen und Mittelwerte durchführen, Standardalgebra-Operationen oder Trigonometrie durchführen und Zahlen auf oder ab runden.
  5. Datum & Zeitspalte – Dieser Abschnitt enthält Befehle für Datums-und Zeitdaten., Sie können Informationen aus Ihren Daten, Zeiten und Dauerdaten extrahieren.
  6. Strukturierte Spalte-Dieser Abschnitt enthält Befehle zum Arbeiten mit verschachtelten Datenstrukturen, z. B. wenn Ihre Spalte Tabellen enthält.

Die Registerkarte Spalte hinzufügen

Die Registerkarte Spalte hinzufügen enthält viele Befehle ähnlich der Registerkarte Transformation, aber der Hauptunterschied besteht darin, dass sie mit der Transformation eine neue Spalte erstellen.

  1. Allgemein-In diesem Abschnitt können Sie neue Spalten basierend auf Formeln oder benutzerdefinierten Funktionen hinzufügen., Sie können hier auch Indexcolummns hinzufügen oder eine Spalte duplizieren.
  2. From Text-Sehr ähnlich dem Abschnitt From Text auf der Registerkarte Transform, aber diese Befehle erstellen eine neue Spalte mit der Transformation.
  3. From Number-Sehr ähnlich dem Abschnitt From Number auf der Registerkarte Transform, aber diese Befehle erstellen eine neue Spalte mit der Transformation.
  4. From Date & Time – Sehr ähnlich dem Abschnitt From Date & Time auf der Registerkarte Transform, aber diese Befehle erstellen eine neue Spalte mit der Transformation.,

Die Registerkarte Ansicht

Die Registerkarte Ansicht ist im Vergleich zu den anderen Registerkarten recht spärlich. Es sind keine Transformationsbefehle darin zu finden. Die meisten Power Query-Benutzer müssen diesen Bereich selten nutzen, aber es gibt noch einige wissenswerte Dinge.

  1. Layout-In diesem Abschnitt können Sie den Abfrageeinstellungsbereich (der die Eigenschaften und die angewendeten Schritte enthält) und die Formelleiste ein-oder ausblenden.,
  2. Datenvorschau – In diesem Abschnitt können Sie Leerzeichen ein-oder ausblenden oder die Schriftart im Datenvorschaubereich in eine Monospace-Schriftart umwandeln. Dies ist praktisch, wenn es um Daten geht, die durch eine bestimmte Anzahl von Zeichen begrenzt sind.
  3. Spalten – Auf diese Weise können Sie eine bestimmte Spalte in der Datenvorschau auswählen. Dieser Befehl ist auch auf der Registerkarte Startseite verfügbar.
  4. Parameter – Damit können Sie die Parametrierung in Datenquellen und Transformationsschritten aktivieren.
  5. Erweitert-Dadurch wird der erweiterte Abfrage-Editor geöffnet, der den M-Code für die Abfrage anzeigt., Dies ist auch auf der Registerkarte Startseite verfügbar.
  6. Abhängigkeiten-Dadurch wird eine Diagrammansicht der Abfrageabhängigkeiten in der Arbeitsmappe geöffnet.

Insbesondere die Ansicht Abfrageabhängigkeiten ist eine nützliche Ressource, mit der Sie eine visuelle Darstellung des Datentransformationsprozessflusses anzeigen können.

Power Query kann für jemanden, der neu in allem ist, zunächst überwältigend erscheinen, aber die Benutzeroberfläche ist sehr gut angelegt und leicht zu erreichen. Während es für einen Benutzer neu sein könnte, sollten viele Konzepte einem Excel-Benutzer bereits vertraut sein.,

Sich mit allen Teilen des Editors und dem Layout der Ribbon-Registerkarten vertraut zu machen, ist ein wesentlicher erster Schritt, um Power Query zu erkunden und in Ihre tägliche Arbeit zu integrieren.

Während es eine Menge über Power Query zu lernen, ist es wert, in der Zeit setzen zu lernen. Es besteht ein enormes Potenzial, Zeit bei sich wiederholenden Datenbereinigungs-und Formatierungsaufgaben zu sparen. Es ist eines der leistungsstärksten und nützlichsten Tools, das Excel seit Pivot-Tabellen hinzugefügt wurde.

Willst du noch mehr Power und Güte?, Dann schauen Sie sich diese erstaunlichen Power Query-Tipps an, um das Beste daraus zu machen!