Informationen zum Kombinieren mehrerer Datenquellen (Power Query)

In diesem Lernprogramm können Sie den Power Query-Abfrage-Editor verwenden, um Daten aus einer lokalen Excel-Datei zu importieren, die Produktinformationen enthält, und aus einem OData-Feed, der Informationen zur Produktbestellung enthält. Sie führen Transformations- und Aggregationsschritte aus und kombinieren Daten aus beiden Quellen, um einen Bericht "Gesamtumsatz pro Produkt und Jahr" zu erstellen.   

Um dieses Lernprogramm ausführen zu können, benötigen Sie die Arbeitsmappe "Produkte". Geben Sie der Datei im Dialogfeld Speichern unter den Namen Produkte und Bestellungen.xlsx.

In dieser Aufgabe importieren Sie Produkte aus der Datei "Products" und "Orders.xlsx" (heruntergeladen und oben umbenannt) in eine Excel-Arbeitsmappe, bewerben Zeilen in Spaltenüberschriften, 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 > Aus Datei > Aus Arbeitsmappe aus.

  3. Suchen Sie im Dialogfeld Daten importieren nach der heruntergeladenen Products.xlsx datei, und wählen Sie dann Öffnen aus.

  4. Doppelklicken Sie im Bereich Navigator auf die Tabelle "Produkte". Der Power Query-Editor wird angezeigt.

Schritt 2: Untersuchen der Abfrageschritte

Standardmäßig fügt Power Query automatisch mehrere Schritte als Komfort für Sie hinzu. Überprüfen Sie jeden Schritt unter Angewendete Schritte im Bereich Abfrageeinstellungen, um weitere Informationen zu erhalten.

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

  2. Klicken Sie mit der rechten Maustaste auf den Navigationsschritt, 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, der die Datentypen der einzelnen Spalten abgeleitet hat. Wählen Sie den Pfeil nach unten rechts neben der Formelleiste aus, um die vollständige Formel zu sehen.

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 inder Datenvorschau die Spalten ProductID,ProductName,CategoryIDund QuantityPerUnit aus (verwenden Sie STRG+Klicken oder UMSCHALT+Klicken).

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

    Andere Spalten ausblenden

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

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)

Auswählen der Tabelle "Produkte"

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 Spalten entfernt

= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

In dieser Aufgabe importieren Sie Daten aus dem Northwind-OData-Beispielfeed von http://services.odata.org/Northwind/Northwind.svcin Ihre Excel-Arbeitsmappe, erweitern die Order_Details-Tabelle, entfernen Spalten, berechnen eine Zeilensumme, transformieren ein Bestelldatum, gruppieren Zeilen nach ProductID und Jahr, benennen die Abfrage um, und deaktivieren Sie den Abfragedownload in die Excel-Arbeitsmappe.

Schritt 1: Herstellen einer Verbindung mit einem OData-Feed

  1. Wählen Sie Daten > Daten >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 Bestellungen.

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 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 zur 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üssel-Zuordnung in einem OData-Feed oder eine Fremdschlüsselbeziehung in einer SQL Server 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 inder Datenvorschau horizontal zur Order_Details Spalte.

  2. Wählen Sie in Order_Details Spalte das Symbol zum Erweitern aus ( Erweitern ).

  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,UnitPriceund Quantity aus.

    3. Wählen Sie OK aus.

      Erweitern der "Order_Details"-Tabellenverknüpfung

      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 in derDatenvorschau 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 inder Datenvorschau das Tabellensymbol ( 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 Spaltenformel [Order_Details.UnitPrice] * [Order_Details.Quantity] ein.

  4. Geben Sie im Feld Neuer Spaltenname die Zeile Summe ein.

  5. Wählen Sie OK aus.

Berechnen der Zeilensumme für jede Zeile von 'Order_Details'

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 derDatenvorschau mit der rechten Maustaste auf die Spalte OrderDate, und wählen Sie >Jahr transformieren 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 die Option Umbenennenaus, und geben Sie Jahr ein.

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

  1. Wählen Sie in derDatenvorschau die Option Jahrund Order_Details.ProductID aus.

  2. Right-Click Sie eine der Kopfzeilen, und wählen Sie Gruppieren 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.

    Dialogfeld 'Gruppieren nach' für Aggregationsvorgänge

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 den Namen Gesamtumsatz ein.

Ergebnisse: Abschlussabfrage 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.

Gesamtumsatz (Total Sales)

Zusammenfassung: Power Query-Schritte, die in Aufgabe 2 erstellt wurden 

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="Bestellungen"]}[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'

Benutzerdefiniert hinzugefügt

= 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 in einen aussagekräftigen 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 "Produkte"und "Gesamtumsatz" mithilfe einer Seriendruckabfrage und eines Erweiterungsvorgangs, 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 Arbeitsblatt "Produkte" zur Artikelabfrage.

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

  3. Wählen Sie im Dialogfeld Zusammenführen die Option Produkte als primäre Tabelle aus, und wählen Sie Gesamtumsatz als sekundäre oder verwandte Abfrage zum Zusammenführen aus. Gesamtumsatz 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 Datenschutzstufen finden Sie unter Festlegen von Datenschutzstufen.

    Dialogfeld 'Zusammenführen'

Ergebnis

Der Seriendruckvorgang erstellt eine Abfrage. Das Abfrageergebnis enthält alle Spalten aus der primären Tabelle (Produkte) und eine einzelne strukturierte Spalte "Tabelle" zur verknüpftenTabelle (Gesamtumsatz). Wählen Sie das Symbol Erweitern aus, um der primären Tabelle neue Spalten aus der sekundären oder verknüpften Tabelle hinzuzufügen.

Ergebnis der Zusammenführung

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 Artikelabfrage zu erstellen: Jahr und Gesamtumsatz.

  1. Wählen Sie in derDatenvorschau symbol erweitern ( Erweitern ) neben NewColumn 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 hatten, wählen Sie Absteigend nach Gesamtumsatz sortieren aus.

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

Ergebnis

Tabellenverknüpfung erweitern

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 Ihre Datenanalyse zu weiter zu nutzen.

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

  2. Stellen Sie im Dialogfeld Daten importieren sicher, dass Sie diese Daten zum Datenmodell hinzufügen auswählen. Wenn Sie weitere Informationen zur Verwendung dieses Dialogfelds erhalten, wählen Sie das Fragezeichen (?) aus.

Ergebnis

Sie verfügen über eine Abfrage "Gesamtumsatz pro Produkt", in der Daten aus der Products.xlsx und dem Northwind-OData-Feed kombiniert werden. Diese Abfrage wird auf ein Power Pivot-Modell angewendet. Darüber hinaus ändern und aktualisieren Änderungen an der Abfrage die resultierende Tabelle im Datenmodell.

Zusammenfassung: Power Query-Schritte, die in Aufgabe 3 erstellt wurden

Während Sie Abfrageaktivitäten in Power Query zusammenführen 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

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}})

Siehe auch

Power Query für Excelhttps://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsupport.microsoft.com%2Farticle%2F2b433a85-ddfb-420b-9cda-fe0e60b82a94&data=04%7C01%7Cmarkgi%40microsoft.com%7C5078fa09d8f74eb22d3408d8b4ce5657%7C72f988bf86f141af91ab2d7cd011db47%7C0%7C0%7C637458145707641576%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=JxJGbg5j58OLUoQTnY93jJ5%2Bf5VNGP0ZqwgUdpsPDGY%3D&reserved=0

Benötigen Sie weitere Hilfe?

Ihre Office-Fähigkeiten erweitern
Schulungen erkunden
Neue Funktionen als Erster erhalten
Office Insider werden

War diese Information hilfreich?

Vielen Dank für Ihr Feedback!

Vielen Dank für Ihr Feedback. Es klingt, als ob es hilfreich sein könnte, Sie mit einem unserer Office-Supportmitarbeiter zu verbinden.

×