Lær at kombinere flere datakilder (Power Query)

I denne opgave skal du importere produkter fra filen Products and Orders.xlsx (downloadet og omdøbt ovenfor) til en Excel-projektmappe, hæve rækker til kolonneoverskrifter, fjerne nogle kolonner og indlæse forespørgslen i et regneark.

Trin 1: Oprette forbindelse til en Excel-projektmappe

1. Oprette en Excel-projektmappe.

2. Vælg Data > Hent data > fra fil > fra projektmappe.

3. I dialogboksen Importér data skal du søge efter og finde den Products.xlsx fil, du har downloadet, og derefter vælge Åbn.

4. Dobbeltklik på tabellen Produkter i ruden Navigator. Tænd/sluk-Power Query-editor vises.

Trin 2: Undersøg forespørgselstrinnene

Som standard tilføjer Power Query automatisk flere trin som en hjælp for dig. Undersøg hvert trin under Anvendte trin i ruden Forespørgselsindstillinger for at få mere at vide.

1. Højreklik på kildetrinnet , og vælg Rediger indstillinger. Dette trin blev oprettet, da du importerede projektmappen.

2. Højreklik på navigationstrinnet, og vælg Rediger indstillinger. Dette trin blev oprettet, da du valgte tabellen fra dialogboksen Navigation .

3. 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 hele formlen.

Trin 3: Fjerne andre kolonner for kun at vise kolonner af interesse

På dette trin fjerner du alle kolonner, undtagen ProductID, ProductName, CategoryID og QuantityPerUnit.

1. I Datavisning skal du vælge kolonnerne ProductID, ProductName, CategoryID og QuantityPerUnit (brug Ctrl+Klik eller Skift+Klik).

2. Vælg Fjern kolonner > Fjern andre kolonner.

   

Trin 4: Indlæs produktforespørgslen

I dette trin skal du indlæse forespørgslen Produkter i et Excel-regneark.

• Vælg Hjem > Luk & Indlæs. Forespørgslen vises i et nyt Excel-regneark.

Oversigt: Power Query trin, der er oprettet i Opgave 1

Når du udfører forespø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 Opret Power Query formler i Excel.

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}, {"Leverandør-id", 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 fjernet	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

I denne opgave importerer du data til din Excel-projektmappe fra Northwind-eksemplet på OData-feedet på http://services.odata.org/Northwind/Northwind.svc,udvider tabellen Order_Details, fjerner kolonner, beregner en linjetotal, transformerer en Ordredato, grupperer rækker efter ProductID og Year, omdøb forespørgslen, og deaktiver forespørgselsoverførsel til Excel-projektmappen.

Trin 1: Opret forbindelse til et OData-feed

1. Vælg Data > Hent data > fra andre kilder , > fra OData-feedet.

2. I dialogboksen OData-feed skal du indtaste URL-adresse for Northwind OData-feedet.

3. Vælg OK.

4. Dobbeltklik på tabellen Ordrer i ruden Navigator.

Trin 2: Udvide en tabel med ordreoplysninger

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, kombineres rækker fra den relaterede tabel (Order_Details) i rækker med den primære tabel (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 entil 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 fremmed nøglerelation i en SQL Server database.

Når du udvider tabellen Order_Details, tilføjes tre nye kolonner og ekstra rækker til tabellen Ordrer, én for hver række i den indlejrede eller relaterede tabel.

1. Rul vandret til kolonnen Order_Details i Datavisning.

2. Vælg udvidelsesikonet ( ) i kolonnen Order_Details .

3. I rullelisten Udvid:

   1. Vælg (Markér alle kolonner) for at rydde alle kolonner.

   2. Vælg Produkt-id, Enhedspris og Antal.

   3. 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 du udvider dataene i emnetabellen. Du kan finde flere oplysninger om, hvordan du udfører sammenlægningshandlinger under Sammenlægge data fra en kolonne.

Trin 3: Fjerne andre kolonner for kun at vise kolonner af interesse

I dette trin fjerner du alle kolonner, undtagen kolonnerne OrderDate, ProductID, UnitPrice og Quantity. 

1. Vælg følgende kolonner i Datavisning: 

   1. Markér den første kolonne, Ordre-id.

   2. Skift+Klik på den sidste kolonne, Speditør.

   3. Ctrl+Klik på kolonnerne OrderDate, Order_Details.ProductID, Order_Details.UnitPrice og Order_Details.Quantity.

2. Højreklik på en markeret kolonneoverskrift, og vælg Fjern andre kolonner.

Trin 4: Beregne det samlede antal linjer for hver række med ordreoplysninger

I dette trin skal du oprette en Brugerdefineret kolonne til at beregne linjetotalen for hver række af Order_Details.

1. I Datavisning skal du vælge tabelikonet () i øverste venstre hjørne af eksemplet.

2. Klik på Tilføj brugerdefineret kolonne.

3. I dialogboksen Brugerdefineret kolonne i feltet Brugerdefineret kolonneformel skal du skrive [Order_Details.Enhedspris] * [Order_Details.Antal].

4. Skriv Linjetotal i feltet Nyt kolonnenavn.

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

1. Højreklik på kolonnen OrderDate i Data preview, og vælg Transformér > År.

2. Omdøbe kolonnen OrderDate til År:

   1. Dobbeltklik på kolonnen OrderDate, og angiv År, eller

   2. Right-Click på kolonnen Ordredato , vælg Omdøb, og angiv År.

Trin 6: Gruppere rækker efter ProductID og Year

1. I Dataeksempel skal du vælge År og Order_Details.ProductID.

2. Right-Click et af overskrifterne, og vælg Gruppér efter.

3. I dialogboksen Gruppér efter:

   1. I tekstboksen Nyt kolonnenavn skal du angive Samlet salg.

   2. I rullemenuen Handling skal du vælge Sum.

   3. I rullemenuen Kolonne skal du vælge Linjetotal.

4. Vælg OK.

   

Trin 7: Omdøbe en forespørgsel

Før du importerer salgsdataene til Excel, skal du omdøbe forespørgslen:

• Skriv Samlet salg i feltet Navn i ruden Forespørgselsindstillinger.

Resultater: Endelig forespørgsel for opgave 2

Når du udfører hver enkelt trin, vil du få en Samlet salg-forespørgsel over i Northwind OData-feedet.

Oversigt: Power Query trin, der er oprettet i Opgave 2 

Når du udfører forespø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 under Få mere at vide om Power Query formler.

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	Brugerdefineret tilføjet	= 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])
Skift til et mere sigende navn, Lne Total	Omdøbte kolonner	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Transformere kolonnen OrderDate til at gengive år	Ekstraheret år	= Table.TransformColumns(#"Grupperede rækker",{{"Year", Date.Year, Int64.Type}})
Skift til  mere sigende navne, Ordredato og År	Omdøbte kolonner 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}})

Med Power-forespørgsel kan du kombinere flere forespørgsler ved at flette eller vedhæfte dem. Handlingen Flet udføres på alle forespørgsler i Power-forespørgsel med en tabularfigur, uafhængigt af den datakilde, som dataene kommer fra. Du kan finde flere oplysninger om at kombinere datakilder under Kombinere flere forespørgsler.

I denne opgave kombinerer du forespørgslerne Produkter og Samlet salg ved hjælp af en fletforespørgsel og handlingen Udvid og indlæser derefter forespørgslen Samlet salg pr. produkt i Excel-datamodellen.

Trin 1: Flette ProductID til en Total Sales-forespørgsel

1. I Excel-projektmappen skal du gå til forespørgslen Produkter på regnearksfanen Produkter .

2. Markér en celle i forespørgslen, og vælg derefter Forespørgsel > Flet.

3. I dialogboksen Flet skal du vælge Produkter som primær tabel og vælge Samlet salg som den sekundære eller relaterede forespørgsel, der skal flettes. Samlet salg bliver til en ny struktureret kolonne med et udvidelsesikon.

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

5. I dialogboksen Fortrolighedsniveau:

   1. Vælg Virksomhedsbeskyttet som dit isolationsniveau for begge datakilder.

   2. Markér Gem.

6. Vælg OK.

   Sikkerhedsnote!:  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 få mere at vide om fortrolighedsniveauer under Angiv fortrolighedsniveauer.

   

Resultat

Handlingen Flet 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 ikonet Udvid 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 NewColumn for at oprette to nye kolonner i forespørgslen Produkter : År og Samlet salg.

1. I Dataeksempel skal du vælge Udvid ikon () ud for NewColumn.

2. På rullelisten Udvid :

   1. Vælg (Markér alle kolonner) for at rydde alle kolonner.

   2. Vælg År og Samlet salg.

   3. Vælg OK.

3. Omdøbe disse to kolonner til År og Samlet salg.

4. Hvis du vil finde ud af, hvilke produkter og i hvilke år produkterne har fået det højeste salg, skal du vælge Sortér faldende efter samlet salg.

5. 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 for at opbygge en rapport, der er forbundet til forespørgselsresultatet. Når du har indlæst data i Excel-datamodellen, kan du bruge Power Pivot til at fremme din dataanalyse.

1. Vælg Hjem > Luk & Indlæs.

2. I dialogboksen Importér data skal du sørge for at vælge Føj disse data til datamodellen. Hvis du vil have mere at vide om brug af denne dialogboks, skal du vælge spørgsmålstegnet (?).

Resultat

Du har en forespørgsel om 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, der er oprettet i Opgave 3

Når du udfører fletforespø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 under Få mere at vide om Power Query formler.

Opgave	Forespørgselstrin	Formel
Flette ProductID ind i forespørgslen Samlet salg	Kilde (datakilde for handlingen Flet)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", 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øb to kolonner	Omdøbte kolonner	= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Sortér samlet salg i stigende rækkefølge	Sorterede rækker	= Table.Sort(#"Omdøbte kolonner",{{"Samlet salg", Ordre.Stigende}})