Lær hvordan du kombinerer flere datakilder (Power Query)

I denne oppgaven importerer du produkter fra produkter og Orders.xlsx (lastet ned og gitt nytt navn ovenfor) til en Excel-arbeidsbok, hever rader til kolonneoverskrifter, fjerner noen kolonner og laster inn spørringen i et regneark.

Trinn 1: Koble deg til en Excel-arbeidsbok

1. Opprette en Excel-arbeidsbok.

2. Velg Data > Hent data > fra fil > fra arbeidsbok.

3. Bla gjennom og finn Products.xlsx filen du lastet ned, i dialogboksen Importer data, og velg deretter Åpne.

4. Dobbeltklikk produkter-tabellen i Navigator-ruten. Power Power Query-redigering vises.

Trinn 2: Undersøk spørringstrinnene

Som standard legger Power Query automatisk til flere trinn som en enkelhet for deg. Undersøk hvert trinn under Brukte trinn i ruten Spørringsinnstillinger for å finne ut mer.

1. Høyreklikk kildetrinnet , og velg Rediger innstillinger. Dette trinnet ble opprettet da du importerte arbeidsboken.

2. Høyreklikk navigasjonstrinnet, og velg Rediger innstillinger. Dette trinnet ble opprettet da du valgte tabellen fra dialogboksen Navigasjon .

3. Høyreklikk trinnet Endret type, og velg Rediger innstillinger. Dette trinnet ble opprettet av Power Query som utledet datatypene for hver kolonne. Velg pil ned til høyre for formellinjen for å se hele formelen.

Trinn 3: Fjerne andre kolonner for å bare vise kolonner av interesse

I dette trinnet du fjerner alle kolonnene unntatt ProduktID, ProduktNavn, KategoriID, og AntallPerEnhet.

1. Velg kolonnene ProductID, ProductName, CategoryID og QuantityPerUnit (bruk Ctrl+Klikk eller Skift+Klikk).

2. Velg Fjern kolonner > Fjern andre kolonner.

   

Trinn 4: Laste inn produktspørringen

I dette trinnet laster du inn produktspørringen i et Excel-regneark.

• Velg Hjem > Lukk & Last inn. Spørringen vises i et nytt Excel-regneark.

Sammendrag: Power Query trinn opprettet i oppgave 1

Når du utfører spørringsaktiviteter i Power Query, opprettes spørringstrinn og vises i spørringsinnstillinger-ruten i brukte trinn-listen. Hvert spørringstrinn har en tilsvarende Power Query-formel, også kalt M-språket. Hvis du vil ha mer informasjon om Power Query formler, kan du se Opprette Power Query formler i Excel.

Oppgave	Spørringstrinn	Formel
Importere en Excel-arbeidsbok	Kilde	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Velg Produkter-tabellen	Naviger	= Source{[Item="Products",Kind="Table"]}[Data]
Power Query oppdager automatisk kolonnedatatyper	Endret 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 å bare vise kolonner av interesse	Fjernede andre kolonner	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

I denne oppgaven importerer du data til Excel-arbeidsboken fra OData-eksempelfeeden Northwind på http://services.odata.org/Northwind/Northwind.svc,utvider Order_Details-tabellen, fjerner kolonner, beregner en linjetotal, transformerer en Ordredato, grupperer rader etter ProduktID og År, gir nytt navn til spørringen og deaktiverer nedlasting av spørring til Excel-arbeidsboken.

Trinn 1: Koble til en OData-feed

1. Velg Data > Hent data > fra andre kilder > fra OData-feeden.

2. I OData-feed-dialogboksen angir du nettadressen for OData-feeden Northwind.

3. Velg OK.

4. Dobbeltklikk ordretabellen i Navigator-ruten.

Trinn 2: Utvide en Ordre_Detaljer-tabell

I dette trinnet utvider du Ordre_Detaljer-tabellen som er relatert til Ordrer-tabellen, for å kombinere kolonnene ProduktID, EnhetsPris, og Antall fra Ordre_Detaljer til Ordrer-tabellen. Utvide-operasjonen kombinerer kolonnene fra en relatert tabell til en emnetabell. Når spørringen kjøres, kombineres rader fra den relaterte tabellen (Order_Details) i rader med primærtabellen (Ordrer).

I Power Query har en kolonne som inneholder en relatert tabell verdien Post eller Tabell i cellen. Disse kalles strukturerte kolonner. Posten angir én enkelt relatert post og representerer enén-til-én-relasjon med gjeldende data eller primærtabell. Tabellen angir en relatert tabell og representerer en én-til-mange-relasjon med gjeldende eller primærtabell. En strukturert kolonne representerer en relasjon i en datakilde som har en relasjonsmodell. En strukturert kolonne angir for eksempel en enhet med en sekundærnøkkeltilknytning i en OData-feed eller sekundærnøkkelrelasjon i en SQL Server database.

Når du utvider Ordre_Detaljer-tabellen, legges, tre nye kolonner og flere rader til i Ordrer-tabellen, én for hver rad i en nestet eller relatert tabell.

1. Rull vannrett til kolonnen Order_Details i Forhåndsvisning av data.

2. Velg utvid-ikonet () i Order_Details-kolonnen .

3. I Utvide-rullegardinlisten:

   1. Velg (Merk alle kolonner) for å fjerne alle kolonner.

   2. Velg ProduktID, Enhetspris og Antall.

   3. Velg OK.

      

      Obs!: I Power Query kan du utvide tabeller som er koblet fra en kolonne, og aggregere kolonnene i den koblede tabellen før du utvider dataene i emnetabellen. Hvis du vil ha mer informasjon om hvordan du utfører mengdeoperasjoner, kan du se Aggregere data fra en kolonne.

Trinn 3: Fjerne andre kolonner for å bare vise kolonner av interesse

I dette trinnet du fjerner alle kolonnene unntatt OrdreDato, ProduktID, EnhetsPris, og Antall. 

1. Velg følgende kolonner i Forhåndsvisning av data: 

   1. Velg den første kolonnen, OrdreID.

   2. SKIFT+KLIKK den siste kolonnen, Speditør.

   3. Ctrl+Klikk kolonnene OrdreDato, Ordre_Detaljer.ProduktID, Ordre_Detaljer.Enhetspris, og Ordre_Detaljer.Antall.

2. Høyreklikk på en valgt kolonneoverskrift, og velg Fjern andre kolonner.

Trinn 4: Beregne totalen for hver Ordre_Detaljer-rad

I dette trinnet oppretter du en Egendefinert kolonne for å beregne totalen for hver Ordre_Detaljer-rad.

1. Velg tabellikonet () øverst til venstre i forhåndsvisningen i forhåndsvisningen i forhåndsvisningen.

2. Klikk Legg til egendefinert kolonne.

3. Skriv inn [Order_Details.UnitPrice] * [Order_Details.Quantity] i boksen Egendefinert kolonneformel i dialogboksen Egendefinert kolonne.

4. Skriv inn Linjetotal i boksen Navn på ny kolonne.

5. Velg OK.

Trinn 5: Transformere en Ordredato-år-kolonne

I dette trinnet skal du forandre OrdreDato-kolonnen for å gjengi året for ordredato.

1. Høyreklikk OrderDate-kolonnen i Forhåndsvisning av data, og velg Transformer > år.

2. Gi OrdreDato-kolonnen det nye navnet År:

   1. Dobbeltklikk OrdreDato-kolonnen og skriv inn År eller

   2. Right-Click i OrderDate-kolonnen , velger du Gi nytt navn, og skriver inn År.

Trinn 6: Gruppere rader etter ProduktID og År

1. Velg År og Order_Details.ProductID i Forhåndsvisning av data.

2. Right-Click en av overskriftene, og velg Grupper etter.

3. I Grupper etter-dialogboksen:

   1. I Nytt kolonnenavn-tekstboksen angir du Totalt salg.

   2. I Operasjon-rullegardinlisten velger du Sum.

   3. I Kolonne-rullegardinlisten velger du Totalt.

4. Velg OK.

   

Trinn 7: Gi spørringen nytt navn

Gi nytt navn til spørringen før du importerer salgsdataene til Excel:

• Angi Totalt salg i Navn-boksen i spørringsinnstillinger-ruten.

Resultater: Endelig spørring for oppgave 2

Når du har utført alle trinnene, får du en Totalt salg-spørring for OData-feeden Northwind.

Sammendrag: Power Query trinn opprettet i oppgave 2 

Når du utfører spørringsaktiviteter i Power Query, opprettes spørringstrinn og vises i spørringsinnstillinger-ruten i brukte trinn-listen. Hvert spørringstrinn har en tilsvarende Power Query-formel, også kalt M-språket. Hvis du vil ha mer informasjon om Power Query formler, kan du se Lær om Power Query formler.

Oppgave	Spørringstrinn	Formel
Koble deg til en OData-feed	Kilde	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc"; null, [Implementation="2.0"])
Velg en tabell	Navigasjon	= Source{[Name="Orders"]}[Data]
Utvide Ordre_Detaljer-tabellen	Utvide Ordre_Detaljer	= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})
Fjerne andre kolonner for å bare vise kolonner av interesse	RemovedColumns	= Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})
Beregne totalen for hver Ordre_Detaljer-rad	Lagt til egendefinert	= 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])
Endre til et mer beskrivende navn, Lne Total	Kolonner med nytt navn	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Transformere OrdreDato-kolonnen til å gjengi året	Utpakket år	= Table.TransformColumns(#"Grouped rows",{{"Year", Date.Year, Int64.Type}})
Endre til  mer meningsfulle navn, Ordredato og År	Kolonne 1 med nytt navn	Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}})
Gruppere rader etter ProduktID og År	GroupedRows	= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

I Power Query kan du kombinere flere spørringer ved å flette eller tilføye dem. Flette-operasjonen utføres på alle Power Query-spørringer som har en tabellform, uavhengig av datakilden som dataene kommer fra. Hvis du vil ha mer informasjon om å kombinere datakilder, kan du se Kombinere flere spørringer.

I denne oppgaven kombinerer du spørringene produkter og totalt salg ved hjelp av en flettespørring og utvidelsesoperasjon , og deretter laster du inn totalsalg per produkt-spørringen i Excel-datamodellen.

Trinn 1: Flette ProduktID i en Totalt salg spørring

1. Gå til Produkter-spørringen på regnearkfanen Produkter i Excel-arbeidsboken.

2. Merk en celle i spørringen, og velg deretter Spørring > Slå sammen.

3. Velg Produkter som primærtabell i dialogboksen Slå sammen, og velg Totalt salg som sekundær eller relatert spørring for å slå sammen. Totalt salg blir en ny strukturert kolonne med et utvidelsesikon.

4. Hvis du vil matche Totalt salg med Produkter etter ProduktID, velger du ProduktID-kolonnen fra Produkter-tabellen og kolonnen Ordre_Detaljer.ProduktID fra Totalt salg-tabellen.

5. I dialogboksen Personvernnivåer:

   1. Velg Organisasjon som personvernnivå for begge datakildene.

   2. Velg Lagre.

6. Velg OK.

   Sikkerhetsmerknad:  Personvernnivåer hindrer en bruker i å utilsiktet kombinere data fra flere datakilder, som kan være privat eller organisatoriske. Avhengig av spørringen kan en bruker utilsiktet sende data fra private datakilder til en annen datakilde som kan være skadelig. Power Query analyserer hver datakilde og klassifiserer den etter det definerte personvernnivået: Offentlig, Organisasjon og Privat. Hvis du vil ha mer informasjon om personvernnivåer, kan du se Angi personvernnivåer.

   

Resultat

Sammenslåingsoperasjonen oppretter en spørring. Spørringsresultatet inneholder alle kolonner fra primærtabellen (Produkter) og én enkelt tabellstrukturert kolonne til den relaterte tabellen (Totalt salg). Velg Utvid-ikonet for å legge til nye kolonner i primærtabellen fra den sekundære eller relaterte tabellen.

Trinn 2: Utvide en sammenslått kolonne

I dette trinnet utvider du den sammenslåtte kolonnen med navnet NewColumn for å opprette to nye kolonner i Produkter-spørringen : År og Totalt salg.

1. Velg Utvid-ikonet () ved siden av NewColumn i Forhåndsvisning av data.

2. I rullegardinlisten Utvid :

   1. Velg (Merk alle kolonner) for å fjerne alle kolonner.

   2. Velg år og totalt salg.

   3. Velg OK.

3. De disse kolonnene de nye navnene År og Totalt salg.

4. Hvis du vil finne ut hvilke produkter og i hvilke år produktene fikk det høyeste salgsvolumet, velger du Sorter synkende ettertotalt salg.

5. Gi spørringen det nye navnetTotalt salg per produkt.

Resultat

Trinn 3: Laste inn en Samlet Salg per Produkt-spørring i en Excel-datamodell

I dette trinnet laster du inn en spørring i en Excel-datamodell for å bygge en rapport som er koblet til spørringsresultatet. Når du har lastet inn data i Excel-datamodellen, kan du bruke Power Pivot til å videreutvikle dataanalysen.

1. Velg Hjem > Lukk & Last inn.

2. Kontroller at du velger Legg til disse dataene i datamodellen i dialogboksen Importer data. Hvis du vil ha mer informasjon om hvordan du bruker denne dialogboksen, velger du spørsmålstegnet (?).

Resultat

Du har en totalsalgsspørring per produkt som kombinerer data fra Products.xlsx-filen og Northwind OData-feeden. Denne spørringen brukes på en Power Pivot-modell. I tillegg endres spørringen og oppdaterer den resulterende tabellen i datamodellen.

Sammendrag: Power Query trinn opprettet i oppgave 3

Når du utfører flettingsspørringsaktiviteter i Power Query, opprettes spørringstrinn og vises i spørringsinnstillinger-ruten i brukte trinn-listen. Hvert spørringstrinn har en tilsvarende Power Query-formel, også kalt M-språket. Hvis du vil ha mer informasjon om Power Query formler, kan du se Lær om Power Query formler.

Oppgave	Spørringstrinn	Formel
Flett ProduktID inn i Totalt salg-spørringen	Kilde (datakilde for Fletting-operasjon)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
Utvide en flettingskolonne	Utvidet totalt salg	= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
Gi nytt navn til to kolonner	Kolonner med nytt navn	= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Sorter totalt salg i stigende rekkefølge	Sorterte rader	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})