Informationen zum Kombinieren mehrerer Datenquellen (Power Query)

In dieser Aufgabe importieren Sie Produkte aus der Datei Products und Orders.xlsx (oben heruntergeladen und umbenannt) in eine Excel-Arbeitsmappe, stufen Zeilen in Spaltenüberschriften höher, entfernen einige Spalten und laden die Abfrage in ein Arbeitsblatt.

Schritt 1: Herstellen einer Verbindung mit einer Excel-Arbeitsmappe

1. Erstellen Sie eine Excel-Arbeitsmappe.

2. Wählen Sie Daten > Daten abrufen > Aus Datei > Aus Arbeitsmappe aus.

3. Suchen Sie im Dialogfeld Daten importieren nach der Products.xlsx Datei, die Sie heruntergeladen haben, und wählen Sie dann Öffnen aus.

4. Doppelklicken Sie im Bereich Navigator auf die Tabelle Products . Die Power Abfrage-Editor wird angezeigt.

Schritt 2: Untersuchen der Abfrageschritte

Standardmäßig fügt Power Query automatisch mehrere Schritte hinzu, um Sie zu vereinfachen. Überprüfen Sie die einzelnen Schritte unter Angewendete Schritte im Bereich Abfrageeinstellungen , um mehr zu erfahren.

1. Klicken Sie mit der rechten Maustaste auf den Schritt Quelle , und wählen Sie Einstellungen bearbeiten aus. Dieser Schritt wurde erstellt, als Sie die Arbeitsmappe importiert haben.

2. Klicken Sie mit der rechten Maustaste auf den Schritt Navigation, und wählen Sie Einstellungen bearbeiten aus. Dieser Schritt wurde erstellt, als Sie die Tabelle im Dialogfeld Navigation ausgewählt haben.

3. 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, die die Datentypen der einzelnen Spalten abgeleitet haben. Klicken Sie rechts neben der Bearbeitungsleiste auf den Pfeil nach unten, um die vollständige Formel anzuzeigen.

Schritt 3: Entfernen anderer Spalten, um nur relevante Spalten anzuzeigen

In diesem Schritt entfernen Sie alle Spalten außer ProductID, ProductName, CategoryID und QuantityPerUnit.

1. Wählen Sie in der Datenvorschau die Spalten ProductID, ProductName, CategoryID und QuantityPerUnit aus (drücken Sie STRG+Klick oder UMSCHALT+Klick).

2. Wählen Sie Spalten entfernen > Andere Spalten entfernen aus.

   

Schritt 4: Laden der Produktabfrage

In diesem Schritt laden Sie die Products-Abfrage in ein Excel-Arbeitsblatt.

• Wählen Sie Start > Schließen & Laden aus. Die Abfrage wird in einem neuen Excel-Arbeitsblatt angezeigt.

Zusammenfassung: Power Query Schritte, die in Aufgabe 1 erstellt wurden

Wenn 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 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 Products aus.	Navigieren	= Quelle{[Item="Products",Kind="Table"]}[Data]
Power Query erkennt Spaltendatentypen automatisch	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"})

In dieser Aufgabe importieren Sie Daten aus dem Northwind OData-Beispielfeed um http://services.odata.org/Northwind/Northwind.svc in Ihre Excel-Arbeitsmappe ,erweitern die Order_Details Tabelle, entfernen Spalten, berechnen eine Zeilensumme, transformieren ein OrderDate, 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

1. Wählen Sie Daten > Daten abrufen> aus anderen Quellen > Aus OData-Feed aus.

2. Geben Sie im Dialogfeld OData-Feed die URL für den Northwind-OData-Feed ein.

3. Wählen Sie OK aus.

4. Doppelklicken Sie im Bereich Navigator auf die Tabelle Orders .

Schritt 2: Erweitern der Tabelle "Order_Details"

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 verknüpften Tabelle (Order_Details) in Zeilen mit der primären Tabelle (Orders) kombiniert.

In Power Query weist eine Spalte, die eine verknüpfte Tabelle enthält, den Wert Record oder Table in der Zelle auf. Diese werden als strukturierte Spalten bezeichnet. Record gibt einen einzelnen verknüpften Datensatz an und stellt eine1:1-Beziehung mit den aktuellen Daten oder der primären Tabelle dar. Table 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. Beispielsweise gibt eine strukturierte Spalte 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 Orders drei neue Spalten sowie weitere Zeilen hinzugefügt, eine für jede Zeile in der verknüpften Tabelle.

1. Scrollen Sie in der Datenvorschau horizontal zur spalte Order_Details .

2. Wählen Sie in der Spalte Order_Details das Erweiterungssymbol () aus.

3. Führen Sie im Dropdownfeld Erweitern die folgenden Aktionen aus:

   1. Wählen Sie (Alle Spalten auswählen) aus, um alle Spalten zu löschen.

   2. Wählen Sie ProductID, UnitPrice und Quantity aus.

   3. Wählen Sie OK aus.

      

      Hinweis: In Power Query können Sie tabellen erweitern, die aus einer Spalte verknüpft sind, 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 relevante Spalten anzuzeigen

In diesem Schritt entfernen Sie alle Spalten mit Ausnahme der Spalten OrderDate, ProductID, UnitPrice und Quantity. 

1. Wählen Sie unter Datenvorschau die folgenden Spalten aus: 

   1. Wählen Sie die erste Spalte OrderID aus.

   2. UMSCHALT+Klicken Sie auf die letzte Spalte Versand.

   3. Klicken Sie bei gedrückter STRG-Taste auf die Spalten OrderDate, Order_Details.ProductID, Order_Details.UnitPrice und Order_Details.Quantity.

2. 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 Zeile von "Order_Details"

In diesem Schritt erstellen Sie eine Benutzerdefinierte Spalte, um die Zeilensumme für jede Zeile von Order_Details zu berechnen.

1. Wählen Sie in der Datenvorschau das Tabellensymbol () in der oberen linken Ecke der Vorschau aus.

2. Klicken Sie auf Benutzerdefinierte Spalte hinzufügen.

3. Geben Sie im Dialogfeld Benutzerdefinierte Spalte im Feld Benutzerdefinierte Spaltenformelden Wert [Order_Details.UnitPrice] * [Order_Details.Quantity] ein.

4. Geben Sie im Feld Neuer Spaltennamedie Zeichenfolge Line Total (Zeilensumme) ein.

5. Wählen Sie OK aus.

Schritt 5: Transformieren einer "OrderDate"-Jahresspalte

In diesem Schritt transformieren Sie die Spalte OrderDate so, dass sie das Jahr des Bestelldatums angibt.

1. Klicken Sie in der Datenvorschau mit der rechten Maustaste auf die Spalte OrderDate, und wählen Sie > Jahrtransformieren aus.

2. Benennen Sie die Spalte OrderDate in Jahr um:

   1. Doppelklicken Sie auf die Spalte OrderDate, und geben Sie Jahr ein, oder

   2. Right-Click in der Spalte OrderDate aus, wählen Sie Umbenennen aus, und geben Sie Year ein.

Schritt 6: Gruppieren von Zeilen nach "ProductID" und "Jahr"

1. Wählen Sie unter Datenvorschaudie Option Jahr und Order_Details.ProductID aus.

2. Right-Click einen der Header aus, und wählen Sie Gruppierung nach aus.

3. Führen Sie im Dialogfeld Gruppieren nach die folgenden Aktionen aus:

   1. Geben Sie im Textfeld Neuer Spaltenname den Wert Total Sales ein.

   2. Wählen Sie im Dropdownfeld Vorgang den Wert Summe aus.

   3. Wählen Sie im Dropdownfeld Spalte den Wert Line Total aus.

4. Wählen Sie OK aus.

   

Schritt 7: Umbenennen einer Abfrage

Bevor Sie die Umsatzdaten in Excel importieren, benennen Sie die Abfrage um:

• Geben Sie im Bereich Abfrageeinstellungen im Feld Name die Zeichenfolge Gesamtumsatz ein.

Ergebnisse: Letzte 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: Power Query schritte, die in Aufgabe 2 erstellt wurden 

Wenn 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"]}[Data]
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])
Ändern Sie in einen aussagekräftigeren Namen, Lne Total	Umbenannte Spalten	= Table.RenameColumns(InsertedCustom;{{"Custom", "Line Total"}})
Transformieren der Spalte "OrderDate", um das Jahr anzugeben	Extrahiertes Jahr	= Table.TransformColumns(#"Grouped Rows";{{"Year", Date.Year, Int64.Type}})
Ändern in  aussagekräftigere 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}})

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 Products und Total Sales mithilfe einer Zusammenführungsabfrage und eines Expand-Vorgangs und laden dann die Abfrage Gesamtumsatz pro Produkt in das Excel-Datenmodell.

Schritt 1: Zusammenführen der "ProductID" mit der Abfrage "Total Sales"

1. Navigieren Sie in der Excel-Arbeitsmappe auf der Registerkarte "Produkte" zur Abfrage "Produkte".

2. Wählen Sie eine Zelle in der Abfrage und dann Abfrage > Zusammenführen aus.

3. Wählen Sie im Dialogfeld Zusammenführendie Option Produkte als primäre Tabelle aus, und wählen Sie Total Sales (Gesamtumsatz ) als sekundäre oder verwandte Abfrage für die Zusammenführung aus. Total Sales wird zu einer neuen strukturierten Spalte mit einem Erweiterungssymbol.

4. 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.

5. Führen Sie im Dialogfeld Sicherheitsstufen folgende Aktionen aus:

   1. Wählen Sie für beide Datenquellen Organisation als Sicherheitsstufe aus.

   2. Wählen Sie Speichern aus.

6. Wählen Sie OK aus.

   Sicherheitshinweis:  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.

   

Result

Der Mergevorgang erstellt eine Abfrage. Das Abfrageergebnis enthält alle Spalten aus der primären Tabelle (Products) und eine einzelne strukturierte Table-Spalte zur verknüpften Tabelle (Total Sales). Wählen Sie das Symbol Erweitern aus, um der primären Tabelle aus der sekundären oder verwandten Tabelle neue Spalten 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: Jahr und Gesamtumsatz.

1. Wählen Sie in der Datenvorschau neben NewColumn das Symbol Erweitern () aus.

2. In der Dropdownliste Erweitern :

   1. Wählen Sie (Alle Spalten auswählen) aus, um alle Spalten zu löschen.

   2. Wählen Sie Jahr und Gesamtumsatz aus.

   3. Wählen Sie OK aus.

3. Benennen Sie diese zwei Spalten in Jahr und Total Sales um.

4. Um herauszufinden, welche Produkte und in welchen Jahren die Produkte das höchste Umsatzvolumen erzielt haben, wählen Sie Absteigend nach Gesamtumsatz sortieren aus.

5. Benennen Sie die Abfrage in Total Sales per Product um.

Result

Schritt 3: Laden einer Abfrage "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 Die Datenanalyse zu vertiefen.

1. Wählen Sie Start > Schließen & Laden aus.

2. Stellen Sie sicher, dass Sie im Dialogfeld Daten importieren die Option Diese Daten zum Datenmodell hinzufügen auswählen. Um weitere Informationen zur Verwendung dieses Dialogfelds zu erfahren, wählen Sie das Fragezeichen (?) aus.

Result

Sie verfügen über 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 ändern Änderungen an der Abfrage die resultierende Tabelle im Datenmodell und aktualisieren sie.

Zusammenfassung: Power Query schritte, die in Aufgabe 3 erstellt wurden

Während Sie Zusammenführungsabfrageaktivitäten in Power Query ausführen, werden Abfrageschritte erstellt und im Bereich Abfrageeinstellungen in der Liste Angewendete Schritte aufgelistet. 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	Erweiterter Gesamtumsatz	= 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"}})
Sortieren des Gesamtumsatzes in aufsteigender Reihenfolge	Sortierte Zeilen	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})