I denne opplæringen kan du bruke Power Query Power Query-redigeringsprogrammet til å importere data fra en lokal Excel-fil som inneholder produktinformasjon, og fra en OData-feed som inneholder ordreinformasjon om produktet. Utfør transformerings- og aggregeringstrinn, og kombiner data fra begge kildene for å opprette rapporten Totalt salg per produkt og år .
Du trenger Produkter-arbeidsboken for å fullføre denne opplæringen. I Lagre som-dialogboksen gir du filen navnet Produkter og ordrer.xlsx.
Oppgave 1: Importere produkter til en Excel-arbeidsbok
I denne oppgaven importerer du produkter fra filen Produkter og Orders.xlsx (lastet ned og gitt nytt navn i forrige avsnitt) til en Excel-arbeidsbok. Deretter hever du rader til kolonneoverskrifter, fjerner noen kolonner og laster inn spørringen til et regneark.
Trinn 1: Koble deg til en Excel-arbeidsbok
- Opprette en Excel-arbeidsbok.
- Velg data>Hent data>fra fil>fra arbeidsbok.
- Bla gjennom og finn Products.xlsx filen du lastet ned, i dialogboksen Importer data, og velg deretter Åpne.
- Dobbeltklikk Produkter-tabellen i Navigator-ruten. Power Query-redigering vises.
Trinn 2: Undersøke spørringstrinnene
Som standard legger Power Query automatisk til flere trinn for å gjøre det enklere. Undersøk hvert trinn under Brukte trinn i feltet Spørringsinnstillinger for å finne ut mer.
- Høyreklikk på Kilde-trinnet , og velg Rediger innstillinger. Dette trinnet ble opprettet da du importerte arbeidsboken.
- Høyreklikk på navigasjonstrinnet , og velg Rediger innstillinger. Dette trinnet ble opprettet da du valgte tabellen fra dialogboksen Navigasjon .
- Høyreklikk på Endret type-trinnet , 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 fjerner du alle kolonnene unntatt ProduktID, ProduktNavn, KategoriID og AntallPerEnhet.
- I forhåndsvisning av data velger du kolonnene ProductID, ProductName, CategoryID og QuantityPerUnit (bruk CTRL+KLIKK eller SKIFT+KLIKK).
- Velg Fjern kolonner>Fjern andre kolonner.
Trinn 4: Laste inn produkter-spørringen
I dette trinnet laster du inn Produkter-spø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, oppretter den spørringstrinn og viser dem i feltet Spørringsinnstillinger, i listen Brukte trinn. 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 dokumentasjonen for Power Query.
| Oppgave | Spørringstrinn | Formel |
|---|---|---|
| Importere en Excel-arbeidsbok | Kilde | = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, sann) |
| 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 | Fjernet andre kolonner | = Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
Oppgave 2: Importere ordredata fra en OData-feed
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 linjesum, 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 deg til en OData-feed
- Velg Data>: Hent data>fra andre kilder>fra OData-feeden.
- I OData-feed-dialogboksen angir du nettadressen for OData-feeden Northwind.
- Velg OK.
- Dobbeltklikk Ordrer-tabellen i Navigator-ruten.
Trinn 2: Utvide en Order_Details 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 radene fra den relaterte tabellen (Order_Details) inn 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. Post angir en enkelt relatert post, og representerer en én-til-én-relasjon med gjeldende data eller primærtabell. Tabell angir en relatert tabell og representerer en én-til-mange-relasjon med gjeldende eller primær tabell. 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 en sekundærnøkkelrelasjon i en SQL Server-database.
Når du utvider den Order_Details tabellen, legges tre nye kolonner og flere rader til i ordretabellen , én for hver rad i den nestede eller relaterte tabellen.
I forhåndsvisning av data ruller du vannrett til Order_Details kolonne.
Velg utvidelsesikonet (
) i Order_Details kolonne.I Utvide-rullegardinlisten:
Velg (Velg alle kolonner) for å fjerne alle kolonnene.
Velg ProduktID, Enhetspris og Antall.
Velg OK.
Obs!
I Power Query kan du utvide tabeller som er koblet til 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 (Power Query).
Trinn 3: Fjerne andre kolonner for å bare vise kolonner av interesse
I dette trinnet fjerner du alle kolonnene unntatt kolonnene Ordredato, ProduktID, Enhetspris og Antall .
I Forhåndsvisning av data velger du følgende kolonner:
- Merk den første kolonnen, Ordre-ID.
- SKIFT+Klikk den siste kolonnen, Transportør.
- Ctrl+Klikk kolonnene OrdreDato, Ordre_Detaljer.ProduktID, Ordre_Detaljer.Enhetspris, og Ordre_Detaljer.Antall.
Høyreklikk på en valgt kolonneoverskrift, og velg Fjern andre kolonner.
Trinn 4: Beregne totalen for hver Order_Details rad
I dette trinnet oppretter du en Egendefinert kolonne for å beregne totalen for hver Ordre_Detaljer-rad.
- I forhåndsvisning av data velger du tabellikonet (
) øverst til venstre i forhåndsvisningen. - Velg Legg til egendefinert kolonne.
- Skriv inn [Order_Details.Enhetspris] * [Order_Details.Antall] i dialogboksen Egendefinert kolonne.
- Skriv inn Linjesum i boksen Nytt kolonnenavn.
- Velg OK.
Trinn 5: Transformere en Ordredato-år-kolonne
I dette trinnet skal du forandre OrdreDato-kolonnen for å gjengi året for ordredato.
Høyreklikk på Ordredato-kolonnen i Forhåndsvisning av data, og velg Transformer>år.
Gi OrdreDato-kolonnen det nye navnet År:
- Dobbeltklikk på Ordredato-kolonnen , og skriv inn År eller
- Høyreklikk på Ordredato-kolonnen , velg Gi nytt navn og skriv inn år.
Trinn 6: Gruppere rader etter ProduktID og År
Velg År og Order_Details.ProduktID i Forhåndsvisning av data.
Høyreklikk på en av overskriftene, og velg Grupper etter.
I Grupper etter-dialogboksen:
- I Nytt kolonnenavn-tekstboksen angir du Totalt salg.
- I Operasjon-rullegardinlisten velger du Sum.
- I Kolonne-rullegardinlisten velger du Totalt.
Velg OK.
Trinn 7: Gi spørringen nytt navn
Før du importerer salgsdata i Excel, gir du spørringen nytt navn:
- Skriv inn Totalt salg i Navn-boksen i Spørringsinnstillinger-ruten.
Resultater: Siste 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, oppretter den spørringstrinn og viser dem i feltet Spørringsinnstillinger, i listen Brukte trinn. 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 dokumentasjonen for Power Query.
| 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 | = Kilde{[Navn="Ordrer"]}[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 | Omdøpte kolonner | = Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}}) |
| Transformere OrdreDato-kolonnen til å gjengi året | Trukket ut år | = Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}}) |
| Endre til mer beskrivende navn, Ordredato og År |
Omdøpte kolonner 1 |
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}}) |
Oppgave 3: Kombinere spørringene Produkter og totalt salg
Med Power Query kan du kombinere flere spørringer ved å flette eller tilføye dem. Du kan utføre sammenslåingsoperasjonen på en hvilken som helst Power Query-spørring som har en tabellform, uavhengig av datakilden. Hvis du vil ha mer informasjon om å kombinere datakilder, kan du se Kombinere flere spørringer (Power Query).
I denne oppgaven kombinerer du spørringene Produkter og Totalt salg ved hjelp av en flettespørring og utvidelsesoperasjon, og deretter laster du inn spørringen Totalt salg per produkt i Excel-datamodellen.
Trinn 1: Flette ProduktID i en Totalt salg-spørring
Gå til produktspørringen på fanen Produkter-regnearket i Excel-arbeidsboken.
Merk en celle i spørringen, og velg deretter Spørringsfletting>.
Velg Produkter som primærtabellen i Flett-dialogboksen, og velg Totalt salg som sekundær eller relatert spørring for å flette. Totalt salg blir en ny strukturert kolonne med et utvidelsesikon.
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.
I dialogboksen Personvernnivåer:
- Velg Organisasjon som personvernnivå for begge datakildene.
- Velg Lagre.
Velg OK.
Obs!
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 (Power Query).
Resultat
Sammenslåingen oppretter en spørring. Spørringsresultatet inneholder alle kolonnene fra primærtabellen (Produkter), og én strukturert tabellkolonne 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 NyKolonne for å opprette to nye kolonner i produktspørringen: år og totalt salg.
Velg Utvid-ikonet (
) ved siden av Ny kolonne i forhåndsvisning av data.I rullegardinlisten Utvid :
- Velg (Velg alle kolonner) for å fjerne alle kolonnene.
- Velg år og totalt salg.
- Velg OK.
De disse kolonnene de nye navnene År og Totalt salg.
Hvis du vil finne ut hvilke produkter og i hvilket år produktene solgte mest, velger du Sorter synkende etter totalt salg.
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 en spørring inn i en Excel-datamodell, slik at du kan bygge en rapport knyttet til spørringsresultatet. Når du har lastet data inn i Excel-datamodellen, kan du bruke Power Pivot til videre dataanalyse.
- Velg Hjem>Lukk & Last inn.
- Pass på 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 Totalt salg per produkt-spørring som kombinerer data fra Products.xlsx-filen og OData-feeden Northwind. Denne spørringen brukes på en Power Pivot-modell. I tillegg vil endringer i spørringen endre og oppdatere den resulterende tabellen i datamodellen.
Sammendrag: Power Query-trinn opprettet i oppgave 3
Mens du utfører flettingsspørringsaktiviteter i Power Query, opprettes og vises det spørringstrinn i listen Bruk trinn i ruten Spørringsinnstillinger. 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 dokumentasjonen for Power Query.
| 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 | Omdøpte kolonner | = Table.RenameColumns(#"Utvidet totalt salg",{{"Totalt salg.år", "år"}, {"Totalt salg.Totalt salg", "Totalt salg"}}) |
| Sorter totalt salg i stigende rekkefølge | Sorterte rader | = Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}}) |