Erfahren Sie, wie Sie mehrere Datenquellen kombinieren (Power Query)
In diesem Lernprogramm können Sie den Abfrage-Editor von Power Query verwenden, um Daten aus einer lokalen Excel-Datei mit Produktinformationen und aus einem OData-Feed mit Produktbestellinformationen 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.
Um dieses Lernprogramm auszuführen, benötigen Sie die Arbeitsmappe Produkte . Benennen Sie im Dialogfeld Speichern unter die Datei Products and Orders.xlsx .
In dieser Aufgabe importieren Sie Produkte aus der Datei „Products and Orders.xlsx" (heruntergeladen und oben umbenannt) in eine Excel-Arbeitsmappe, stufen Zeilen in Spaltenüberschriften um, entfernen einige Spalten und laden die Abfrage in ein Arbeitsblatt.
Schritt 1: Verbinden Sie sich mit einer Excel-Arbeitsmappe
Erstellen Sie eine Excel-Arbeitsmappe.
Wählen Sie Daten > Daten abrufen > Aus Datei > Aus Arbeitsmappe aus .
Suchen Sie im Dialogfeld Daten importieren nach der heruntergeladenen Datei Products.xlsx und wählen Sie dann Öffnen aus.
Doppelklicken Sie im Navigationsbereich auf die Tabelle Products . Der Power Query-Editor wird angezeigt.
Schritt 2: Überprüfen Sie die Abfrageschritte
Standardmäßig fügt Power Query aus praktischen Gründen automatisch mehrere Schritte hinzu. Untersuchen Sie jeden Schritt unter Angewandte 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 erstellt, als Sie die Arbeitsmappe importiert haben.
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.
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, wodurch die Datentypen jeder Spalte abgeleitet wurden. Wählen Sie den Abwärtspfeil rechts neben der Formelleiste aus, um die vollständige Formel anzuzeigen.
Schritt 3: Entfernen Sie andere Spalten, um nur relevante 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+Klick oder Umschalt+Klick).
Wählen Sie Spalten entfernen > Andere Spalten entfernen aus .
Schritt 4: Laden Sie die Produktabfrage
In diesem Schritt laden Sie die Produktabfrage in ein Excel-Arbeitsblatt.
Wählen Sie Startseite > Schließen und 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 aufgelistet. Jeder Abfrageschritt hat 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 Sie eine Excel-Arbeitsmappe | Quelle | = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true) |
Wählen Sie die Tabelle Produkte aus | Navigieren | = Quelle{[Item="Products",Kind="Table"]}[Daten] |
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 ", Text eingeben}, {"UnitPrice", Zahl eingeben}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", Typ logisch}}) |
Entfernen Sie andere Spalten, um nur relevante Spalten anzuzeigen | Andere Spalten entfernt | = Table.SelectColumns (FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
In dieser Aufgabe importieren Sie Daten aus dem OData-Beispielfeed von Northwind unter http://services.odata.org/Northwind/Northwind.svc in Ihre Excel-Arbeitsmappe, erweitern die Tabelle „Order_Details", entfernen Spalten, berechnen eine Zeilensumme und transformieren ein „OrderDate". , gruppieren Sie Zeilen nach ProductID und Year, benennen Sie die Abfrage um und deaktivieren Sie das Herunterladen der Abfrage in die Excel-Arbeitsmappe.
Schritt 1: Verbinden mit einem OData-Feed
Wählen Sie Daten > Daten abrufen > Aus anderen Quellen > Aus OData-Feed aus .
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 Sie eine 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. Die Expand -Operation kombiniert Spalten aus einer verknüpften Tabelle in einer Thementabelle. Wenn die Abfrage ausgeführt wird, werden Zeilen aus der verknüpften Tabelle ( Order_Details ) in Zeilen mit der Primärtabelle ( Orders ) kombiniert.
In Power Query hat eine Spalte, die eine Bezugstabelle enthält, den Wert Datensatz oder Tabelle in der Zelle. Diese werden strukturierte Spalten genannt. Datensatz gibt einen einzelnen verknüpften Datensatz an und stellt eine Eins-zu-Eins-Beziehung mit den aktuellen Daten oder der Primärtabelle 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. Beispielsweise gibt eine strukturierte Spalte eine Entität mit einer Fremdschlüsselzuordnung in einem OData-Feed oder einer Fremdschlüsselbeziehung in einer SQL Server-Datenbank an.
Nachdem Sie die Tabelle „Order_Details " erweitert haben, werden der Tabelle „ Orders " drei neue Spalten und zusätzliche Zeilen hinzugefügt, eine für jede Zeile in der verschachtelten oder verknüpften Tabelle.
Scrollen Sie in der Datenvorschau horizontal zur Spalte Order_Details .
Wählen Sie in der Spalte Order_Details das Erweiterungssymbol (
). Im Drop-down-Menü „ Erweitern ":
Wählen Sie (Alle Spalten auswählen) , 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 mit einer Spalte verknüpfte Tabellen erweitern und die Spalten der verknüpften Tabelle aggregieren, bevor Sie die Daten in der Subjekttabelle erweitern. Weitere Informationen zum Ausführen von Aggregationsvorgängen finden Sie unter Aggregieren von Daten aus einer Spalte .
Schritt 3: Entfernen Sie andere Spalten, um nur relevante Spalten anzuzeigen
In diesem Schritt entfernen Sie alle Spalten außer den Spalten OrderDate , ProductID , UnitPrice und Quantity .
Wählen Sie in der Datenvorschau die folgenden Spalten aus:
Wählen Sie die erste Spalte OrderID aus .
Klicken Sie bei gedrückter Umschalttaste auf die letzte Spalte Versender .
Strg+Klicken Sie 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 Sie die Zeilensumme für jede Zeile „Order_Details".
In diesem Schritt erstellen Sie eine benutzerdefinierte Spalte , um die Zeilensumme für jede Zeile „Order_Details" zu berechnen.
Wählen Sie in der Datenvorschau das Tabellensymbol (
) in der oberen linken Ecke der Vorschau. Klicken Sie auf Benutzerdefinierte Spalte hinzufügen .
Geben Sie im Dialogfeld „Benutzerdefinierte Spalte " im Formelfeld „ Benutzerdefinierte Spalte " [Order_Details.UnitPrice] * [Order_Details.Quantity] ein .
Geben Sie im Feld Neuer Spaltenname Line Total ein.
Wählen Sie OK aus .
Schritt 5: Transformieren Sie eine OrderDate-Jahresspalte
In diesem Schritt wandeln Sie die OrderDate- Spalte um, um das Jahr des Bestelldatums wiederzugeben.
Klicken Sie in Data Preview mit der rechten Maustaste auf die Spalte OrderDate und wählen Sie Transform > Year aus.
Benennen Sie die Spalte OrderDate in Year um:
Doppelklicken Sie auf die Spalte OrderDate und geben Sie Jahr oder ein
Klicken Sie mit der rechten Maustaste auf die Spalte OrderDate , wählen Sie Rename aus und geben Sie Year ein.
Schritt 6: Gruppieren Sie die Zeilen nach ProductID und Year
Wählen Sie in der Datenvorschau Year und Order_Details.ProductID aus.
Klicken Sie mit der rechten Maustaste auf eine der Kopfzeilen und wählen Sie Gruppieren nach aus.
Im Dialogfeld „Gruppieren nach":
Geben Sie im Textfeld Neuer Spaltenname Total Sales ein.
Wählen Sie in der Dropdown-Liste Vorgang die Option Summe aus.
Wählen Sie in der Dropdown-Liste Spalte die Option Zeilensumme aus.
Wählen Sie OK aus .
Schritt 7: Benennen Sie eine Abfrage um
Bevor Sie die Verkaufsdaten in Excel importieren, benennen Sie die Abfrage um:
Geben Sie im Bereich Abfrageeinstellungen im Feld Name Total Sales ein.
Ergebnisse: Letzte Abfrage für Aufgabe 2
Nachdem Sie jeden Schritt ausgeführt haben, erhalten Sie eine Total Sales-Abfrage über den Northwind-OData-Feed.
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 aufgelistet. Jeder Abfrageschritt hat 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 |
|---|---|---|
Stellen Sie eine Verbindung zu einem OData-Feed her | Quelle | = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"]) |
Wählen Sie eine Tabelle aus | Navigation | = Quelle{[Name="Bestellungen"]}[Daten] |
Erweitern Sie die Tabelle Order_Details | Erweitern Sie Bestelldetails | = Table.ExpandTableColumn (Bestellungen, "Bestelldetails", {"Produkt-ID", "Einzelpreis", "Menge"}, {"Bestelldetails.Produkt-ID", "Bestelldetails.Einzelpreis", "Bestelldetails.Menge"}) |
Entfernen Sie andere Spalten, um nur relevante Spalten anzuzeigen | Entfernte Spalten | = Table.RemoveColumns (#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity ", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"}) |
Berechnen Sie die Zeilensumme für jede Zeile Order_Details | Benutzerdefiniert hinzugefügt | = Table.AddColumn (RemovedColumns, "Custom", jeweils [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Line Total", jeweils [Order_Details.UnitPrice] * [Order_Details.Quantity]) |
Ändern Sie zu einem aussagekräftigeren Namen, Lne Total | Spalten umbenannt | = Table.RenameColumns (InsertedCustom,{{"Custom", "Line Total"}}) |
Transformieren Sie die OrderDate-Spalte, um das Jahr zu rendern | Extrahiertes Jahr | = Table.TransformColumns (#"Gruppierte Zeilen",{{"Year", Date.Year, Int64.Type}}) |
Ändern aussagekräftigere Namen, OrderDate und Year | Umbenannte Spalten 1 | (TransformedColumn,{{"OrderDate", "Year"}}) |
Gruppieren Sie Zeilen nach ProductID und Year | Gruppierte Zeilen | = Table.Group (RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}}) |
Mit Power Query können Sie mehrere Abfragen kombinieren, indem Sie sie zusammenführen oder anhängen. Der Zusammenführungsvorgang wird für jede Power Query-Abfrage mit tabellarischer Form ausgeführt, 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 Vorgangs „ Erweitern " und laden dann die Abfrage „Gesamtumsatz pro Produkt" in das Excel-Datenmodell.
Schritt 1: Führen Sie ProductID in eine Total Sales-Abfrage ein
Navigieren Sie in der Excel-Arbeitsmappe zur Abfrage Produkte auf der Registerkarte Produkte des Arbeitsblatts.
Wählen Sie eine Zelle in der Abfrage aus, und wählen Sie dann Abfrage > Zusammenführen aus.
Wählen Sie im Dialogfeld „Zusammenführen" als primäre Tabelle „ Products " und als sekundäre oder verwandte Abfrage „ Total Sales " aus, die zusammengeführt werden soll. Gesamtumsatz wird zu einer neuen strukturierten Spalte mit einem Erweiterungssymbol.
Um Total Sales mit Products nach ProductID abzugleichen , wählen Sie die ProductID- Spalte aus der Products -Tabelle und die Order_Details.ProductID- Spalte aus der Total Sales -Tabelle aus.
Im Dialogfeld Datenschutzstufen :
Wählen Sie Organisatorisch für Ihre Datenschutz-Isolationsstufe für beide Datenquellen aus.
Wählen Sie Speichern aus .
Wählen Sie OK aus .
Sicherheitshinweis: Datenschutzstufen verhindern, dass ein Benutzer versehentlich Daten aus mehreren Datenquellen kombiniert, die privat oder organisatorisch sein können. Abhängig von der Abfrage könnte ein Benutzer versehentlich Daten von der privaten Datenquelle an eine andere möglicherweise schädliche Datenquelle senden. Power Query analysiert jede Datenquelle und klassifiziert sie in die definierte Datenschutzstufe: Öffentlich, Organisatorisch und Privat. Weitere Informationen zu Datenschutzstufen finden Sie unter Datenschutzstufen festlegen .
Ergebnis
Die Merge- Operation erstellt eine Abfrage. Das Abfrageergebnis enthält alle Spalten aus der Primärtabelle ( Products ) und eine einzige strukturierte Tabellenspalte zur zugehörigen Tabelle ( Total Sales ). Wählen Sie das Symbol „Erweitern", um der primären Tabelle neue Spalten aus der sekundären oder verknüpften Tabelle hinzuzufügen.
Schritt 2: Erweitern Sie eine zusammengeführte Spalte
In diesem Schritt erweitern Sie die zusammengeführte Spalte mit dem Namen NewColumn , um zwei neue Spalten in der Abfrage Products zu erstellen: Year und Total Sales .
Wählen Sie in der Datenvorschau das Symbol „ Erweitern " (
) neben NewColumn . In der Dropdown -Liste Erweitern:
Wählen Sie (Alle Spalten auswählen) , um alle Spalten zu löschen.
Wählen Sie Jahr und Gesamtumsatz aus .
Wählen Sie OK aus .
Benennen Sie diese beiden Spalten in Year und Total Sales um.
Um herauszufinden, welche Produkte in welchen Jahren die meisten Verkäufe erzielt haben, wählen Sie Absteigend nach Gesamtumsatz sortieren .
Benennen Sie die Abfrage in Total Sales per Product um.
Ergebnis
Schritt 3: Laden Sie eine Gesamtumsatz-pro-Produkt-Abfrage in ein Excel-Datenmodell
In diesem Schritt laden Sie eine Abfrage in ein Excel-Datenmodell , um einen mit dem Abfrageergebnis verbundenen Bericht zu erstellen. Nachdem Sie Daten in das Excel-Datenmodell geladen haben, können Sie Power Pivot zur weiteren Datenanalyse verwenden.
Wählen Sie Startseite > Schließen und laden aus .
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 Total Sales per Product- Abfrage, die Daten aus der Datei „Products.xlsx" und dem Northwind-OData-Feed kombiniert. 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
Beim Durchführen von Zusammenführungsabfrageaktivitäten in Power Query werden Abfrageschritte erstellt und im Bereich Abfrageeinstellungen in der Liste Angewendete Schritte aufgelistet. Jeder Abfrageschritt hat 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 |
|---|---|---|
Führen Sie ProductID in die Total Sales-Abfrage ein | Quelle (Datenquelle für den Zusammenführungsvorgang ) | = Table.NestedJoin (Produkte, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter) |
Erweitern Sie eine Zusammenführungsspalte | Erweiterter Gesamtumsatz | = Table.ExpandTableColumn (Quelle, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"}) |
Benennen Sie zwei Spalten um | Spalten umbenannt | = Table.RenameColumns (#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}}) |
Gesamtumsatz in aufsteigender Reihenfolge sortieren | Sortierte Zeilen | = Table.Sort (#"Umbenannte Spalten",{{"Total Sales", Order.Ascending}}) |
No comments:
Post a Comment