In diesem Tutorial können Sie den Abfrage-Editor von Power Query verwenden, um Daten aus einer lokalen Excel-Datei, die Produktinformationen enthält, und aus einem OData-Feed, der Produktbestellinformationen enthält, zu importieren. Sie führen Transformations- und Aggregationsschritte durch und kombinieren Daten aus beiden Quellen, um einen Bericht "Gesamtumsatz pro Produkt und Jahr" zu erstellen.
Zum Ausführen dieses Tutorials benötigen Sie die Arbeitsmappe "Produkte ". Geben Sie der Datei im Dialogfeld Speichern unter den Namen Produkte und Bestellungen.xlsx.
Aufgabe 1: Importieren von Produkten in eine Excel-Arbeitsmappe
Bei dieser Aufgabe importieren Sie Produkte aus der Datei "Produkte und Orders.xlsx" (oben heruntergeladen und umbenannt) in eine Excel-Arbeitsmappe, stufen Zeilen zu Spaltenüberschriften herauf, entfernen einige Spalten und laden die Abfrage in ein Arbeitsblatt.
Schritt 1: Herstellen einer Verbindung mit einer Excel-Arbeitsmappe
- Erstellen Sie eine Excel-Arbeitsmappe.
- Daten> auswählen Datenaus Datei>aus Arbeitsmappeabrufen>.
- Suchen Sie im Dialogfeld "Daten importieren " nach der heruntergeladenen Products.xlsx Datei, und wählen Sie dann "Öffnen" aus.
- Doppelklicken Sie im Navigationsbereich auf die Tabelle "Artikel ". Der Power Query-Editor wird angezeigt.
Schritt 2: Überprüfen der Abfrageschritte
Standardmäßig fügt Power Query automatisch mehrere Schritte hinzu, um Ihnen die Arbeit zu erleichtern. Untersuchen Sie jeden Schritt unter "Angewendete Schritte " im Bereich "Abfrageeinstellungen ", um mehr zu erfahren.
- Klicken Sie mit der rechten Maustaste auf den Schritt "Quelle ", und wählen Sie "Einstellungen bearbeiten" aus. Dieser Schritt wurde beim Importieren der Arbeitsmappe erstellt.
- Klicken Sie mit der rechten Maustaste auf den Navigationsschritt, und wählen Sie Einstellungen bearbeiten aus. Dieser Schritt wurde erstellt, wenn Sie die Tabelle im Dialogfeld Navigation ausgewählt haben.
- Klicken Sie mit der rechten Maustaste auf den Schritt Geänderter Typ , und wählen Sie Einstellungen bearbeiten aus. Dieser Schritt wurde von Power Query erstellt, das die Datentypen der einzelnen Spalten ableitete. Wählen Sie den Abwärtspfeil rechts neben der Bearbeitungsleiste aus, um die vollständige Formel anzuzeigen.
Schritt 3: Entfernen anderer Spalten, um nur die gewünschten Spalten anzuzeigen
In diesem Schritt entfernen Sie alle Spalten außer ProductID, ProductName, CategoryID und QuantityPerUnit.
- Wählen Sie in der Datenvorschau die Spalten "ProductID", "ProductName", "CategoryID" und "QuantityPerUnit " aus (verwenden Sie STRG+Klicken oder UMSCHALT+Klick).
- Wählen Sie "Spalten> entfernen", "Andere Spalten entfernen" aus.
Schritt 4: Laden der Produktabfrage
In diesem Schritt laden Sie die Abfrage "Produkte " in ein Excel-Arbeitsblatt.
- Start> auswählenSchließen & Laden. Die Abfrage wird in einem neuen Excel-Arbeitsblatt angezeigt.
Zusammenfassung: In Aufgabe 1 erstellte Power Query-Schritte
Während Sie Abfrageaktivitäten in Power Query ausführen, werden Abfrageschritte erstellt und im Bereich "Abfrageeinstellungen" in der Liste "Angewendete Schritte" aufgeführt. Zu jedem Abfrageschritt gibt es eine entsprechende Power Query-Formel, die auch als "M"-Sprache bezeichnet wird. Weitere Informationen zu Power Query-Formeln finden Sie unter Erstellen von Power Query-Formeln in Excel.
| Aufgabe | Abfrageschritt | Formel |
|---|---|---|
| Importieren einer Excel-Arbeitsmappe | Quelle | = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true) |
| Wählen Sie die Tabelle "Artikel" aus. | Navigieren | = Quelle{[Item="Produkte",Kind="Tabelle"]}[Daten] |
| Power Query erkennt automatisch Spaltendatentypen | Geänderter Typ | = Table.TransformColumnTypes( Products_Table,{{"ProductID", Int64.Type}, {"ProductName", type text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}}) |
| Entfernen anderer Spalten, um nur relevante Spalten anzuzeigen | Andere entfernte Spalten | = Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
Aufgabe 2: Importieren von Bestelldaten aus einem OData-Feed
Bei dieser Aufgabe importieren Sie Daten aus dem Northwind OData-Beispielfeed bei http://services.odata.org/Northwind/Northwind.svc in Ihre Excel-Arbeitsmappe, erweitern die Order_Details Tabelle, entfernen Spalten, berechnen eine Zeilensumme, transformieren ein Bestelldatum, gruppieren Zeilen nach ProductID und Year, benennen die Abfrage um und deaktivieren den Abfragedownload in die Excel-Arbeitsmappe.
Schritt 1: Herstellen einer Verbindung mit einem OData-Feed
- Daten> auswählen Datenaus anderen Quellen>aus OData Feedabrufen>.
- Geben Sie im Dialogfeld OData-Feed die URL für den Northwind-OData-Feed ein.
- Wählen Sie OK aus.
- Doppelklicken Sie im Navigationsbereich auf die Tabelle Bestellungen .
Schritt 2: Erweitern einer Order_Details Tabelle
In diesem Schritt erweitern Sie die Tabelle Order_Details, die mit der Tabelle Orders verknüpft ist, um die Spalten ProductID, UnitPrice und Quantity aus Order_Details in der Tabelle Orders zu kombinieren. Beim Vorgang Erweitern werden Spalten aus einer verknüpften Tabelle in einer Thementabelle kombiniert. Wenn die Abfrage ausgeführt wird, werden Zeilen aus der Bezugstabelle (Order_Details) in Zeilen mit der Primärtabelle (Orders) kombiniert.
In Power Query enthält eine Spalte, die eine verknüpfte Tabelle enthält, den Wert "Datensatz" oder "Tabelle" in der Zelle. Diese werden als strukturierte Spalten bezeichnet. Datensatz gibt einen einzelnen verknüpften Datensatz an und stellt eine 1:1-Beziehung mit den aktuellen Daten oder der primären Tabelle dar. Tabelle gibt eine verknüpfte Tabelle an und stellt eine 1:n-Beziehung mit der aktuellen oder primären Tabelle dar. Eine strukturierte Spalte stellt eine Beziehung in einer Datenquelle dar, die über ein relationales Modell verfügt. Eine strukturierte Spalte gibt beispielsweise eine Entität mit einer Fremdschlüsselzuordnung in einem OData-Feed oder eine Fremdschlüsselbeziehung in einer SQL Server-Datenbank an.
Nachdem Sie die Tabelle Order_Details erweitert haben, werden der Tabelle Bestellungen drei neue Spalten und zusätzliche Zeilen hinzugefügt – eine für jede Zeile in der geschachtelten oder verknüpften Tabelle.
Scrollen Sie in der Datenvorschau horizontal zur Order_Details Spalte.
Wählen Sie in der Order_Details Spalte das Erweiterungssymbol (
) aus.Führen Sie im Dropdownfeld Erweitern die folgenden Aktionen aus:
Wählen Sie (Alle Spalten auswählen) aus, um alle Spalten zu löschen.
Wählen Sie "ProductID", "UnitPrice" und " Quantity" aus.
Wählen Sie OK aus.
Hinweis
In Power Query können Sie Tabellen, die mit einer Spalte verknüpft sind, erweitern und die Spalten der verknüpften Tabelle aggregieren, bevor Sie die Daten in der Betrefftabelle erweitern. Weitere Informationen zum Ausführen von Aggregationsvorgängen finden Sie unter Aggregieren von Daten aus einer Spalte.
Schritt 3: Entfernen anderer Spalten, um nur die gewünschten Spalten anzuzeigen
In diesem Schritt entfernen Sie alle Spalten mit Ausnahme der Spalten OrderDate, ProductID, UnitPrice und Quantity.
Wählen Sie in der Datenvorschau die folgenden Spalten aus:
- Wählen Sie die erste Spalte, OrderID, aus.
- UMSCHALT+Klicken Sie auf die letzte Spalte, Shipper.
- Klicken Sie bei gedrückter STRG-Taste auf die Spalten OrderDate, Order_Details.ProductID, Order_Details.UnitPrice und Order_Details.Quantity.
Klicken Sie mit der rechten Maustaste auf eine ausgewählte Spaltenüberschrift, und wählen Sie Andere Spalten entfernen aus.
Schritt 4: Berechnen der Zeilensumme für jede Order_Details Zeile
In diesem Schritt erstellen Sie eine Benutzerdefinierte Spalte, um die Zeilensumme für jede Zeile von Order_Details zu berechnen.
- Wählen Sie in der Datenvorschau das Tabellensymbol (
) in der oberen linken Ecke der Vorschau aus. - Klicken Sie auf Benutzerdefinierte Spalte hinzufügen.
- Geben Sie im Dialogfeld Benutzerdefinierte Spalte im Formelfeld Benutzerdefinierte Spalte[Order_Details.Einzelpreis] * [Order_Details.Menge] ein.
- Geben Sie im Feld Neuer Spaltennamedie Zeilensumme ein.
- Wählen Sie OK aus.
Schritt 5: Transformieren einer Spalte für das Jahr "Bestelldatum"
In diesem Schritt transformieren Sie die Spalte OrderDate so, dass sie das Jahr des Bestelldatums angibt.
Klicken Sie in der Datenvorschau mit der rechten Maustaste auf die Spalte "Bestelldatum", und wählen Sie "Jahrtransformieren>" aus.
Benennen Sie die Spalte OrderDate in Jahr um:
- Doppelklicken Sie auf die Spalte OrderDate, und geben Sie Jahr ein, oder
- Right-Click in der Spalte "Bestelldatum " die Option "Umbenennen" aus, und geben Sie "Jahr" ein.
Schritt 6: Gruppieren von Zeilen nach ProductID und Year
Wählen Sie in der Datenvorschau"Jahr " und "Order_Details.ProductID" aus.
Right-Click eine der Überschriften, und wählen Sie Gruppieren nach.
Führen Sie im Dialogfeld Gruppieren nach die folgenden Aktionen aus:
- Geben Sie im Textfeld Neuer Spaltenname den Wert Total Sales ein.
- Wählen Sie im Dropdownfeld Vorgang den Wert Summe aus.
- Wählen Sie im Dropdownfeld Spalte den Wert Line Total aus.
Wählen Sie OK aus.
Schritt 7: Umbenennen einer Abfrage
Benennen Sie die Abfrage um, bevor Sie die Umsatzdaten in Excel importieren:
- Geben Sie im Bereich Abfrageeinstellungen im Feld Nameden Gesamtumsatz ein.
Ergebnisse: Abschließende Abfrage für Aufgabe 2
Nachdem Sie alle Schritte ausgeführt haben, haben Sie jetzt eine Abfrage "Total Sales" (Gesamtumsatz) für den Northwind-OData-Feed.
Zusammenfassung: In Aufgabe 2 erstellte Power Query-Schritte
Während Sie Abfrageaktivitäten in Power Query ausführen, werden Abfrageschritte erstellt und im Bereich "Abfrageeinstellungen" in der Liste "Angewendete Schritte" aufgeführt. Zu jedem Abfrageschritt gibt es eine entsprechende Power Query-Formel, die auch als "M"-Sprache bezeichnet wird. Weitere Informationen zu Power Query-Formeln finden Sie unter Informationen zu Power Query-Formeln.
| Aufgabe | Abfrageschritt | Formel |
|---|---|---|
| Herstellen einer Verbindung mit einem OData-Feed | Quelle | = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"]) |
| Auswählen einer Tabelle | Navigation | = Quelle{[Name="Orders"]}[Daten] |
| Erweitern der Order_Details-Tabelle | Erweitern von Order_Details | = Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"}) |
| Entfernen anderer Spalten, um nur relevante Spalten anzuzeigen | SpaltenEntfernt | = Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"}) |
| Berechnen der Zeilensumme für jede Zeile von 'Order_Details' | Hinzugefügte benutzerdefinierte Spalte |
= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) |
| In einen aussagekräftigeren Namen ändern, Lne Total | Umbenannte Spalten | = Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}}) |
| Transformieren der Spalte "OrderDate", um das Jahr anzugeben | Extrahiertes Jahr | = Table.TransformColumns(#"Gruppierte Zeilen",{{"Year", Date.Year, Int64.Type}}) |
| Ändern in aussagekräftigeren Namen, "OrderDate" und "Year" |
Umbenannte Spalten 1 |
Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}}) |
| Gruppieren von Zeilen nach ProductID und Jahr | GroupedRows | = Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}}) |
Aufgabe 3: Kombinieren der Abfragen "Products" und "Total Sales"
Power Query ermöglicht Ihnen, mehrere Abfragen zu kombinieren, indem Sie sie zusammenführen oder anfügen. Der Vorgang Zusammenführen kann für jede beliebige Power Query-Abfrage in Tabellenform ausgeführt werden, und zwar unabhängig von der Datenquelle, aus der die Daten stammen. Weitere Informationen zum Kombinieren von Datenquellen finden Sie unter Kombinieren mehrerer Abfragen.
In dieser Aufgabe kombinieren Sie die Abfragen "Produkte" und "Gesamtumsatz " mithilfe einer Zusammenführungsabfrage und eines Erweiterungsvorgangs und laden dann die Abfrage "Gesamtumsatz pro Produkt" in das Excel-Datenmodell.
Schritt 1: Zusammenführen von "ProductID" mit einer Gesamtumsatzabfrage
Navigieren Sie in der Excel-Arbeitsmappe auf der Arbeitsblattregisterkarte "Produkte" zur Abfrage "Produkte".
Wählen Sie eine Zelle in der Abfrage und dann "Seriendruck"> aus.
Wählen Sie im Dialogfeld Zusammenführendie Tabelle "Artikel " als primäre Tabelle und dann Gesamtumsatz als sekundäre oder verwandte Abfrage aus, die zusammengeführt werden soll. "Gesamtumsatz" wird zu einer neuen strukturierten Spalte mit einem Erweiterungssymbol.
Um Gesamtumsatz anhand von ProductID zu Products zuzuordnen, wählen Sie in der Tabelle Products die Spalte ProductID und in der Tabelle Gesamtumsatz die Spalte Order_Details.ProductID aus.
Führen Sie im Dialogfeld Sicherheitsstufen folgende Aktionen aus:
- Wählen Sie für beide Datenquellen Organisation als Sicherheitsstufe aus.
- Wählen Sie Speichern aus.
Wählen Sie OK aus.
Hinweis
Sicherheitsstufen hindern Benutzer daran, unabsichtlich Daten aus mehreren Datenquellen zu kombinieren, die z. B. privat oder organisationsweit verfügbar sein können. Je nach Abfrage könnte ein Benutzer unbeabsichtigt Daten aus der privaten Datenquelle an eine andere Datenquelle senden, was schwer absehbare Folgen haben kann. Power Query analysiert jede Datenquelle und ordnet sie den definierten Datenschutzstufen zu: öffentlich, organisationsweit und privat. Weitere Informationen zu Datenschutzebenen finden Sie unter Festlegen von Datenschutzebenen.
Ergebnis
Beim Zusammenführen wird eine Abfrage erstellt. Das Abfrageergebnis enthält alle Spalten aus der Primärtabelle (Produkte) und eine einzelne in Tabelle strukturierte Spalte zur verknüpften Tabelle (Gesamtumsatz). Wählen Sie das Symbol "Erweitern " aus, um der Primärtabelle neue Spalten aus der Sekundärtabelle oder der verknüpften Tabelle hinzuzufügen.
Schritt 2: Erweitern einer zusammengeführten Spalte
In diesem Schritt erweitern Sie die zusammengeführte Spalte mit dem Namen NewColumn , um zwei neue Spalten in der Abfrage "Produkte " zu erstellen: "Year " und "Total Sales".
Wählen Sie in der Datenvorschau das Erweiterungssymbol (
) neben NewColumn aus.In der Dropdownliste "Erweitern ":
- Wählen Sie (Alle Spalten auswählen) aus, um alle Spalten zu löschen.
- Wählen Sie "Jahr" und "Gesamtumsatz" aus.
- Wählen Sie OK aus.
Benennen Sie diese zwei Spalten in Jahr und Total Sales um.
Um herauszufinden, welche Produkte und in welchen Jahren die Produkte das höchste Umsatzvolumen erzielt haben, wählen Sie Absteigend nachGesamtumsatz sortieren aus.
Benennen Sie die Abfrage in Total Sales per Product um.
Ergebnis
Schritt 3: Laden einer Abfrage vom Gesamtumsatz pro Produkt in ein Excel-Datenmodell
In diesem Schritt laden Sie eine Abfrage in ein Excel-Datenmodell, um einen Bericht zu erstellen, der mit dem Abfrageergebnis verbunden ist. Nachdem Sie Daten in das Excel-Datenmodell geladen haben, können Sie Power Pivot verwenden, um Ihre Datenanalyse voranzutreiben.
- Start> auswählenSchließen & Laden.
- Stellen Sie im Dialogfeld Daten importieren sicher, dass Sie diese Daten zum Datenmodell hinzufügen auswählen. Um weitere Informationen zur Verwendung dieses Dialogfelds zu erhalten, wählen Sie das Fragezeichen (?) aus.
Ergebnis
Sie haben eine Abfrage Gesamtumsatz pro Produkt , die Daten aus der Products.xlsx Datei und dem Northwind OData-Feed kombiniert. Diese Abfrage wird auf ein Power Pivot-Modell angewendet. Darüber hinaus wird bei Änderungen an der Abfrage die resultierende Tabelle im Datenmodell geändert und aktualisiert.
Zusammenfassung: In Aufgabe 3 erstellte Power Query-Schritte
Wenn Sie Merge-Abfrageaktivitäten in Power Query ausführen, werden Abfrageschritte erstellt und im Bereich "Abfrageeinstellungen" in der Liste "Angewendete Schritte" aufgeführt. Zu jedem Abfrageschritt gibt es eine entsprechende Power Query-Formel, die auch als "M"-Sprache bezeichnet wird. Weitere Informationen zu Power Query-Formeln finden Sie unter Informationen zu Power Query-Formeln.
| Aufgabe | Abfrageschritt | Formel |
|---|---|---|
| Zusammenführen der ProductID mit der Abfrage "Gesamtumsatz" | Quelle (Datenquelle für den Vorgang Zusammenführen) | = Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter) |
| Erweitern einer Zusammenführungsspalte | Expanded Total Sales | = Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"}) |
| Umbenennen von zwei Spalten | Umbenannte Spalten | = Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}}) |
| Gesamtumsatz in aufsteigender Reihenfolge sortieren | Sortierte Zeilen | = Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}}) |