Wie verwende ich Power Query in Excel?
Excel Power Query wird zum Durchsuchen von Datenquellen, Herstellen von Verbindungen mit Datenquellen und anschließendes Formen der Daten gemäß unseren Analyseanforderungen verwendet. Sobald wir die Daten gemäß unseren Anforderungen gestaltet haben, können wir auch unsere Ergebnisse teilen und mithilfe weiterer Abfragen verschiedene Berichte erstellen.
Schritte
Grundsätzlich gibt es 4 Schritte, und die Reihenfolge dieser 4 Schritte in Power Query lautet wie folgt:
- Verbinden: Wir stellen zuerst eine Verbindung zu den Daten her, die sich irgendwo, in der Cloud, im Dienst oder lokal befinden können.
- Transformieren: Der zweite Schritt besteht darin, die Form der Daten gemäß den Benutzeranforderungen zu ändern.
- Kombinieren: In diesem Schritt führen wir einige Transformations- und Aggregationsschritte durch und kombinieren Daten aus beiden Quellen, um einen kombinierten Bericht zu erstellen.
- Verwalten: Hiermit werden Spalten in einer Abfrage zusammengeführt und mit übereinstimmenden Spalten in anderen Abfragen in der Arbeitsmappe angehängt.
Es gibt viele superstarke Funktionen von Excel Power Query.
Angenommen, wir haben Kaufdaten für die letzten 15 Jahre in 180 Dateien. Jetzt müsste das Management einer Organisation die Zahlen konsolidieren, bevor sie analysiert werden. Das Management kann eine der folgenden Methoden anwenden:
- Sie würden alle Dateien öffnen und sie kopieren und in eine Datei einfügen.
- Auf der anderen Seite können sie eine kluge Lösung verwenden, nämlich Formeln anzuwenden, da diese fehleranfällig sind.
Für welche Methode sie sich auch entscheiden, sie enthält viel manuelle Arbeit, und nach einigen Monaten würden für die zusätzliche Dauer neue Verkaufsdaten vorliegen. Sie müssen die gleiche Übung erneut machen.
Power Query kann ihnen jedoch helfen, diese mühsame und sich wiederholende Arbeit nicht auszuführen. Lassen Sie uns diese Excel-Power-Abfrage anhand eines Beispiels verstehen.
Beispiel
Angenommen, wir haben Textdateien in einem Ordner mit Verkaufsdaten und möchten diese Daten in unsere Excel-Datei aufnehmen.
Wie wir im folgenden Bild sehen können, befinden sich zwei Dateitypen im Ordner, wir möchten jedoch nur die Daten von Textdateien in der Excel-Datei abrufen.
Um dasselbe zu tun, wären die Schritte:
Schritt 1: Zuerst müssen wir die Daten in der Power Query abrufen, damit wir die erforderlichen Änderungen an den Daten vornehmen können, um diese in eine Excel-Datei zu importieren.
Um das gleiche zu tun, werden wir die wählen „From Folder“ Option aus dem „Aus Datei“ Menü nach einem Klick auf dem Befehl „Get Data“ aus der „Get & Transform “ Gruppe in der „Data“ Registerkarte.
Schritt 2: Wählen Sie den Speicherort des Ordners durch Durchsuchen aus.
Klicken Sie auf "OK".
Schritt 3: Ein Dialogfenster öffnet sich die Liste für alle Dateien im ausgewählten Ordner mit den Spaltenüberschriften als enthält ‚Content‘ ‚Name‘ ‚Verlängerung‘ ‚Datum zugegriffen wird ,‘ ‚Datum geändert,‘ ‚Datum erstellt,‘ 'Attribute' und 'Ordnerpfad'.
Es gibt 3 Optionen: Kombinieren , Laden und Transformieren von Daten .
- Kombinieren : Mit dieser Option gelangen Sie zu einem Bildschirm, auf dem Sie auswählen können, welche Daten kombiniert werden sollen. Der Bearbeitungsschritt wird für diese Option übersprungen und gibt uns keine Kontrolle darüber, welche Dateien kombiniert werden sollen. Bei der Kombinationsfunktion wird jede Datei im Ordner konsolidiert, was zu Fehlern führen kann.
- Laden: Mit dieser Option wird nur die oben im Bild gezeigte Tabelle anstelle der tatsächlichen Daten in den Dateien in das Excel-Arbeitsblatt geladen.
- Daten transformieren: Im Gegensatz zum Befehl 'Kombinieren' können wir bei Verwendung dieses Befehls auswählen, welche Dateien kombiniert werden sollen, dh wir können nur einen Dateityp (dieselbe Erweiterung) kombinieren.
Wie in unserem Fall möchten wir nur Textdateien (.txt) kombinieren. Wir werden den Befehl "Daten transformieren" wählen .
Auf der rechten Seite des Fensters sehen Sie „Angewandte Schritte“. Derzeit ist nur ein einziger Schritt erforderlich, um die Dateidetails aus dem Ordner zu übernehmen.
Schritt 4: Es gibt eine Spalte mit dem Namen 'Erweiterung', in der wir sehen können, dass die Werte in der Spalte in beiden Fällen geschrieben sind, dh in Groß- und Kleinbuchstaben.
Wir müssen jedoch alle Werte in Kleinbuchstaben umwandeln, da der Filter zwischen beiden unterscheidet. Um das gleiche zu tun, müssen wir die Spalte auszuwählen und wählen Sie dann „Kleinschreibung“ aus dem „Format“ Befehl-Menü.
Schritt 5: Wir filtern die Daten mithilfe der Spalte "Erweiterung" für Textdateien.
Schritt 6: Wir müssen jetzt Daten für beide Textdateien in der ersten Spalte "Inhalt" kombinieren . Wir werden auf das Symbol rechts neben dem Spaltennamen klicken.
Schritt 7: Ein Dialogfeld mit der Überschrift "Dateien kombinieren" wird geöffnet, in dem Sie das Trennzeichen als "Tab" für Textdateien (Dateien mit der Erweiterung ".txt") auswählen und die Basis für die Datentyperkennung auswählen können. Und klicken Sie auf "OK".
Nachdem Sie auf "OK" geklickt haben , werden die kombinierten Daten von Textdateien im Fenster "Power Query" angezeigt.
Wir können den Datentyp der Spalten nach Bedarf ändern. In der Spalte "Umsatz" ändern wir den Datentyp in "Währung".
Wir können die auf die Daten angewendeten Schritte mithilfe einer Energieabfrage auf der rechten Seite des Fensters sehen.
Nachdem Sie alle erforderlichen Änderungen an den Daten vorgenommen haben, können Sie die Daten mit dem Befehl 'Schließen & Laden in' in der Gruppe 'Schließen' auf der Registerkarte 'Home' in ein Excel-Arbeitsblatt laden .
Wir müssen wählen, ob wir die Daten als Tabelle oder als Verbindung laden möchten. Klicken Sie dann auf "OK".
Jetzt können wir die Daten als Tabelle im Arbeitsblatt sehen.
Und der Bereich "Arbeitsmappenabfragen" auf der rechten Seite, den wir zum Bearbeiten, Duplizieren, Zusammenführen, Anhängen der Abfragen und für viele andere Zwecke verwenden können.
Excel Power Query ist sehr nützlich, da wir sehen können, dass innerhalb weniger Minuten 601.612 Zeilen geladen wurden.
Dinge, an die man sich erinnern sollte
- Power Query ändert die ursprünglichen Quelldaten nicht. Anstatt die ursprünglichen Quelldaten zu ändern, wird jeder Schritt aufgezeichnet, den der Benutzer beim Verbinden oder Transformieren der Daten ausführt. Sobald der Benutzer die Datenformung abgeschlossen hat, nimmt er den verfeinerten Datensatz und bringt ihn in die Arbeitsmappe.
- Power Query unterscheidet zwischen Groß- und Kleinschreibung.
- Während der Konsolidierung der Dateien im angegebenen Ordner müssen wir sicherstellen, dass die Spalte 'Erweiterung' verwendet wird, und temporäre Dateien (mit der Erweiterung '.tmp' und dem Namen dieser Dateien, die mit dem Zeichen '~' beginnen) als ausschließen Power Query kann diese Dateien auch importieren.
