I dette selvstudium kan du bruge Power Query's Power Query-editor til at importere data fra en lokal Excel-fil med produktoplysninger og fra et OData-feed, der indeholder produktordreoplysninger. Udfør transformations- og sammenlægningstrin, og kombiner data fra begge kilder for at oprette en rapport over Samlet salg pr. produkt og år .
For at fuldføre dette selvstudium skal du bruge projektmappen Produkter . I dialogboksen Gem som skal du navngive filen Produkter og ordrer.xlsx.
Opgave 1: Importere produkter til en Excel-projektmappe
I denne opgave importerer du produkter fra filen Products and Orders.xlsx (downloadet og omdøbt i forrige afsnit) til en Excel-projektmappe. Derefter hæver du rækker til kolonneoverskrifter, fjerner nogle kolonner og indlæser forespørgslen i et regneark.
Trin 1: Oprette forbindelse til en Excel-projektmappe
- Oprette en Excel-projektmappe.
- Vælg Data>, hent data>fra fil>fra projektmappe.
- I dialogboksen Importér data skal du søge efter og finde denProducts.xlsx fil, du downloadede, og derefter vælge Åbn.
- Dobbeltklik på tabellen Produkter i ruden Navigator. Power Query-editor vises.
Trin 2: Undersøg forespørgselstrinnene
Som standard tilføjer Power Query automatisk flere trin for at gøre det nemmere for dig. Undersøg hvert trin under Anvendte trin i ruden Forespørgselsindstillinger for at få mere at vide.
- Højreklik på trinnet Kilde , og vælg Rediger indstillinger. Dette trin blev oprettet, da du importerede projektmappen.
- Højreklik på navigationstrinnet, og vælg Rediger indstillinger. Dette trin blev oprettet, da du valgte tabellen fra dialogboksen Navigation .
- Højreklik på trinnet Ændret type , og vælg Rediger indstillinger. Dette trin blev oprettet af Power Query, som udledte datatyperne for hver kolonne. Vælg pil ned til højre for formellinjen for at få vist den komplette formel.
Trin 3: Fjerne andre kolonner for kun at vise kolonner af interesse
I dette trin fjerner du alle kolonner undtagen ProductID, ProductName, CategoryID og QuantityPerUnit.
- I Datavisning skal du vælge kolonnerne Produkt-id, Produktnavn, Kategori-id og AntalPerEnhed (brug Ctrl+klik eller Skift+klik).
- Vælg Fjern kolonner>Fjern andre kolonner.
Trin 4: Indlæs produktforespørgslen
I dette trin indlæser du produktforespørgslen i et Excel-regneark.
- Vælg Hjem>Luk & Indlæs. Forespørgslen vises i et nyt Excel-regneark.
Oversigt: Power Query-trin oprettet i opgave 1
Efterhånden som du udfører forespørgselsaktiviteter i Power Query, oprettes der forespørgselstrin, og de vises i ruden Forespørgselsindstillinger på listen Anvendte trin. Hver forespørgselstrin har en tilsvarende Power forespørgsel-formel, der også kaldes "M"-sprog. Du kan finde flere oplysninger om Power Query-formler i dokumentationen til Power Query.
| Opgave | Forespørgselstrin | Formel |
|---|---|---|
| Importere en Excel-projektmappe | Kilde | = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true) |
| Vælg tabellen Produkter | Naviger | = Source{[Item="Products",Kind="Table"]}[Data] |
| Power Query registrerer automatisk kolonnedatatyper | Ændret type | = 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}}) |
| Fjerne andre kolonner for kun at vise kolonner af interesse | Andre kolonner, der er fjernet | = Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
Opgave 2: Importere ordredata fra et OData-feed
I denne opgave importerer du data til Excel-projektmappen fra Northwind-eksempeldatabasens OData-feed på http://services.odata.org/Northwind/Northwind.svc, udvider tabellen Order_Details, fjerner kolonner, beregner en samlet linje, transformerer en Ordredato, grupperer rækker efter Produkt-id og År, omdøber forespørgslen og deaktiverer forespørgselsdownload til Excel-projektmappen.
Trin 1: Oprette forbindelse til et OData-feed
- Vælg data>Hente data>fra andre kilder>fra OData-feedet.
- I dialogboksen OData-feed skal du indtaste URL-adresse for Northwind OData-feedet.
- Vælg OK.
- Dobbeltklik på tabellen Ordrer i navigationsruden.
Trin 2: Udvide en Order_Details tabel
I dette trin skal du udvide tabellen Order_Details, der er relaterer sig til tabellen Ordrer for at samle kolonnerne ProductID, UnitPrice og Quantity fra Order_Details til tabellen Ordrer. Handlingen Udvid samler kolonner fra de berørte tabeller i en emnetabel. Når forespørgslen kører, samles rækker fra den relaterede tabel (Order_Details) i rækker med primærtabellen (Ordrer).
I Power Query har en kolonne, der indeholder en relateret tabel, værdien Post eller Tabel i cellen. Disse kaldes strukturerede kolonner. Post angiver en enkelt relateret post og repræsenterer en en til en-relation med de aktuelle data eller den primære tabel. Tabel angiver en relateret tabel og repræsenterer en en-til-mange-relation med den aktuelle eller primære tabel. En struktureret kolonne repræsenterer en relation i en datakilde, der har en relationel model. En struktureret kolonne angiver f.eks. en enhed med en tilknytning af en fremmed nøgle i et OData-feed eller en relation for en fremmed nøgle i en SQL Server-database.
Når du udvider tabellen Order_Details , føjes tre nye kolonner og ekstra rækker til tabellen Ordrer , én for hver række i den indlejrede eller relaterede tabel.
I Datavisning skal du rulle vandret til den Order_Details kolonne.
I den Order_Details kolonne skal du vælge udvidelsesikonet (
).I rullelisten Udvid:
Vælg (Markér alle kolonner) for at rydde alle kolonner.
Vælg Produkt-id, Enhedspris og Antal.
Vælg OK.
Bemærk
I Power Query kan du udvide tabeller, der er sammenkædet fra en kolonne, og aggregere kolonnerne i den sammenkædede tabel, før data udvides i emnetabellen. Du kan finde flere oplysninger om, hvordan du udfører sammenlægningshandlinger under Sammenlægge data fra en kolonne (Power Query).
Trin 3: Fjerne andre kolonner for kun at vise kolonner af interesse
I dette trin fjerner du alle kolonner undtagen kolonnerne Ordredato, Produktid, Enhedspris og Antal .
Vælg følgende kolonner i Datavisning:
- Vælg den første kolonne, Ordre-id.
- Skift+Klik på den sidste kolonne, Speditionsfirma.
- Ctrl+Klik på kolonnerne OrderDate, Order_Details.ProductID, Order_Details.UnitPrice og Order_Details.Quantity.
Højreklik på en markeret kolonneoverskrift, og vælg Fjern andre kolonner.
Trin 4: Beregne linjetotalen for hver Order_Details række
I dette trin skal du oprette en Brugerdefineret kolonne til at beregne linjetotalen for hver række af Order_Details.
- I Datavisning skal du vælge tabelikonet (
) i øverste venstre hjørne af forhåndsvisningen. - Vælg Tilføj brugerdefineret kolonne.
- I dialogboksen Brugerdefineret kolonne i formelfeltet Brugerdefineret kolonne skal du angive [Order_Details.Enhedspris] * [Order_Details.Antal].
- I feltet Nyt kolonnenavn skal du angive linjetotal.
- Vælg OK.
Trin 5: Transformere en OrderDate i kolonnen Year
I dette trin kan du transformere kolonnen OrderDate kolonne for at gengive året for ordredato.
I Datavisning skal du højreklikke på kolonnen OrderDate og vælge Transformér>år.
Omdøbe kolonnen OrderDate til År:
- Dobbeltklik på kolonnen OrderDate , og angiv Year eller
- Højreklik på kolonnen OrderDate , vælg Omdøb, og angiv år.
Trin 6: Gruppere rækker efter ProductID og Year
I Datavisning skal du vælge År og Order_Details.Produkt-id.
Højreklik på et af overskrifterne, og vælg Gruppér efter.
I dialogboksen Gruppér efter:
- I tekstboksen Nyt kolonnenavn skal du angive Samlet salg.
- I rullemenuen Handling skal du vælge Sum.
- I rullemenuen Kolonne skal du vælge Linjetotal.
Vælg OK.
Trin 7: Omdøbe en forespørgsel
Før du importerer salgsdata til Excel, skal du omdøbe forespørgslen:
- I ruden Forespørgselsindstillinger skal du angive det samlede salg i feltet Navn.
Resultater: Endelig forespørgsel for opgave 2
Når du udfører hver enkelt trin, har du en Samlet salg-forespørgsel over i Northwind OData-feedet.
Oversigt: Power Query-trin oprettet i opgave 2
Efterhånden som du udfører forespørgselsaktiviteter i Power Query, oprettes der forespørgselstrin, og de vises i ruden Forespørgselsindstillinger på listen Anvendte trin. Hver forespørgselstrin har en tilsvarende Power forespørgsel-formel, der også kaldes "M"-sprog. Du kan finde flere oplysninger om Power Query-formler i dokumentationen til Power Query.
| Opgave | Forespørgselstrin | Formel |
|---|---|---|
| Oprette forbindelse til et OData-feed | Kilde | = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"]) |
| Vælg en tabel | Navigation | = Source{[Name="Orders"]}[Data] |
| Udvide tabellen Order_Details | Udvide Order_Details | = Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"}) |
| Fjerne andre kolonner for kun at vise kolonner af interesse | Fjernede kolonner | = Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"}) |
| Beregne det samlede antal linjer for hver række med Order_Details | Tilføjet brugerdefineret |
= Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Udvidet Order_Details", "Linjetotal", hver [Order_Details.Enhedspris] * [Order_Details.Antal]) |
| Skift til et mere beskrivende navn, Lne Total | Omdøbte kolonner | = Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}}) |
| Transformere kolonnen OrderDate til at gengive år | Udtrukket år | = Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}}) |
| Skift til mere beskrivende navne, OrderDate og Year |
Omdøbt Kolonne 1 |
Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}}) |
| Gruppere rækker efter ProductID og År | Grupperede rækker | = Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}}) |
Opgave 3: Kombinere forespørgsler for Products og Total Sales
Power Query gør det muligt at kombinere flere forespørgsler ved at flette eller tilføje dem. Du kan udføre flettehandlingen på en hvilken som helst Power Query-forespørgsel med en tabellignende figur, uanset datakilden. Du kan finde flere oplysninger om at kombinere datakilder under Kombinere flere forespørgsler (Power Query).
I denne opgave skal du kombinere forespørgslerne Produkter og Samlet salg ved hjælp af en fletteforespørgsel og udvidelseshandling og derefter indlæse forespørgslen Samlet salg pr. produkt i Excel-datamodellen.
Trin 1: Flette ProductID til en Total Sales-forespørgsel
I Excel-projektmappen skal du gå til produktforespørgslen på produktregnearksfanen.
Markér en celle i forespørgslen, og vælg derefterForespørgselsfletning>.
I dialogboksen Flet skal du vælge Produkter som primærtabel og vælge Samlet salg som sekundær eller relateret forespørgsel til fletning. Samlet salg bliver en ny struktureret kolonne med et udvidelsesikon.
For at matche Samlet salg med Produkter efter ProductID skal du vælge kolonnen ProductID fra tabellen Produkter og kolonnen Order_Details.ProductID fra tabellen Samlet salg.
I dialogboksen Fortrolighedsniveau:
- Vælg Virksomhedsbeskyttet som dit isolationsniveau for begge datakilder.
- Markér Gem.
Vælg OK.
Bemærk
Fortrolighedsniveauer forhindrer en bruger i uforvarende at kombinere data fra flere datakilder, som kan være private eller organisatorisk. Afhængigt af forespørgslen, kan en bruger utilsigtet sende data fra den private datakilde til en anden datakilde, der kan være skadelig. Power-forespørgsel analyserer hver enkelt datakilde og klassificerer den ind i det definerede niveau for beskyttelse af personlige oplysninger: Offentlig, Organisatorisk og Privat. Du kan finde flere oplysninger om niveauer for beskyttelse af personlige oplysninger i Angive fortrolighedsniveauer (Power Query).
Resultat
Flethandlingen opretter en forespørgsel. Forespørgselsresultatet indeholder alle kolonner fra den primære tabel (Produkter) og en enkelt struktureret tabelkolonne til den relaterede tabel (Samlet salg). Vælg udvidelsesikonet for at føje nye kolonner til den primære tabel fra den sekundære eller relaterede tabel.
Trin 2: Udvide en flettet kolonne
I dette trin skal du udvide den flettede kolonne med navnet Ny kolonne for at oprette to nye kolonner i produktforespørgslen: År og Samlet salg.
I Datavisningskal du vælge udvidelsesikonet (
) ud for Ny kolonne.Gør følgende på rullelisten Udvid :
- Vælg (Markér alle kolonner) for at rydde alle kolonner.
- Vælg År og Samlet salg.
- Vælg OK.
Omdøbe disse to kolonner til År og Samlet salg.
Hvis du vil finde ud af, hvilke produkter og i hvilke år produkterne havde det største salg, skal du vælge Sortér faldende efter samlet salg.
Omdøb forespørgslen til Samlet salg pr. produkt.
Resultat
Trin 3: Indlæse en forespørgsel om Samlet salg pr. produkt i en Excel-datamodel
I dette trin indlæser du en forespørgsel i en Excel-datamodel, så du kan oprette en rapport med forbindelse til forespørgselsresultatet. Når du har indlæst data til Excel-datamodellen, kan du bruge Power Pivot til at videreanalysere dataene.
- Vælg Hjem>Luk & Indlæs.
- I dialogboksen Importér data skal du sørge for, at du har valgt Føj disse data til datamodellen. Klik på spørgsmålstegnet (?) for at få flere oplysninger om brugen af denne dialogboks.
Resultat
Du har en forespørgsel for Samlet salg pr. produkt , der kombinerer data fra Products.xlsx filen og Northwind OData-feedet. Denne forespørgsel anvendes på en Power Pivot-model. Desuden ændrer og opdaterer ændringer i forespørgslen den resulterende tabel i datamodellen.
Oversigt: Power Query-trin oprettet i opgave 3
Efterhånden som du udfører fletteforespørgselsaktiviteter i Power Query, oprettes og vises forespørgselstrin i ruden Forespørgselsindstillinger på listen Anvendte trin. Hver forespørgselstrin har en tilsvarende Power forespørgsel-formel, der også kaldes "M"-sprog. Du kan finde flere oplysninger om Power Query-formler i dokumentationen til Power Query.
| Opgave | Forespørgselstrin | Formel |
|---|---|---|
| Flette ProductID ind i forespørgslen Samlet salg | Kilde (datakilde for handlingen Flet) | = Table.NestedJoin(Products, {"ProductID"}, #"Samlet salg", {"Order_Details.ProductID"}, "Samlet salg", JoinKind.LeftOuter) |
| Udvide en flettet kolonne | Udvidet samlet salg | = Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"}) |
| Omdøbe to kolonner | Omdøbte kolonner | = Table.RenameColumns(#"Udvidet samlet salg",{{"Samlet salg.år", "år"}, {"Samlet salg.Samlet salg", "Samlet salg"}}) |
| Sortere det samlede salg i stigende rækkefølge | Sorterede rækker | = Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}}) |