Lär dig att kombinera flera datakällor (Power Query)

I den här uppgiften importerar du produkter från filen Produkter och Orders.xlsx (hämtad och bytt namn ovan) till en Excel-arbetsbok, höjer upp rader till kolumnrubriker, tar bort några kolumner och läser in frågan i ett kalkylblad.

Steg 1: Anslut till en Excel-arbetsbok

1. Skapa en Excel-arbetsbok.

2. Välj Data > Hämta data > Från fil > Från arbetsbok.

3. I dialogrutan Importera data bläddrar du efter och letar reda på den Products.xlsx fil du laddade ned och väljer sedan Öppna.

4. Dubbelklicka på tabellen Produkter i fönstret Navigatör. Power Power Query-redigeraren visas.

Steg 2: Undersöka frågestegen

Som standard lägger Power Query automatiskt till flera steg som en tjänst för dig. Undersök varje steg under Tillämpade steg i fönstret Frågeinställningar om du vill veta mer.

1. Högerklicka på källsteget och välj Redigera inställningar. Det här steget skapades när du importerade arbetsboken.

2. Högerklicka på navigeringssteget och välj Redigera inställningar. Det här steget skapades när du valde tabellen i dialogrutan Navigering .

3. Högerklicka på steget Ändrad typ och välj Redigera inställningar. Det här steget skapades av Power Query som härledde datatyperna för varje kolumn. Välj nedåtpilen till höger om formelfältet för att se hela formeln.

Steg 3: Ta bort andra kolumner för att endast visa kolumner av intresse

I det här steget tar du bort alla kolumner utom ProductID, ProductName, CategoryID och QuantityPerUnit.

1. I Dataförhandsgranskning väljer du kolumnerna ProductID, ProductName, CategoryID och QuantityPerUnit (använd Ctrl+Klicka eller Skift+Klicka).

2. Välj Ta bort kolumner > Ta bort andra kolumner.

   

Steg 4: Läs in produktfrågan

I det här steget läser du in produktfrågan i ett Excel-kalkylblad.

• Välj Start > Stäng & läs in. Frågan visas i ett nytt Excel-kalkylblad.

Sammanfattning: Power Query steg som skapats i Aktivitet 1

När du utför frågeaktiviteter i Power Query skapas och listas frågesteg i fönstret Frågeinställningar i listan Tillämpade steg. Varje fråga steg har en motsvarande Power Query-formel, som också kallas "M"-språket. Mer information om Power Query formler finns i Skapa Power Query formler i Excel.

Uppgift	Frågesteg	Formel
Importera en Excel-arbetsbok	Källa	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Välj tabellen Produkter	Navigera	= Källa{[Item="Products",Kind="Table"]}[Data]
Power Query identifierar kolumndatatyper automatiskt	Ändrad typ	= 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}})
Ta bort andra kolumner för att endast visa kolumner av intresse	Borttagna andra kolumner	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

I den här uppgiften importerar du data till excel-arbetsboken från Northwind OData-exempelfeeden på http://services.odata.org/Northwind/Northwind.svc,expanderar Order_Details-tabellen, tar bort kolumner, beräknar en radsumma, omvandlar orderdatum, grupperar rader efter ProductID och Year, byter namn på frågan och inaktiverar nedladdning av frågan till Excel-arbetsboken.

Steg 1: Ansluta till en OData-feed

1. Välj Data > Hämta data > från andra källor > från OData-feed.

2. I OData-feedens dialogrutan ska du ange URL:en för Northwind OData-feeden.

3. Välj OK.

4. Dubbelklicka på tabellen Order i fönstret Navigatör.

Steg 2: Utöka en tabell för Order_Details

I det här steget expanderar du Order_Details-tabellen som är relaterad till Order-tabellen, för att kombinera ProductID, UnitPrice, and Quantity-kolumnerna från Order_Details i Orders-tabellen. Åtgärden Expand kombinerar kolumner från en relaterad tabell till ett ämnestabell. När frågan körs kombineras rader från den relaterade tabellen (Order_Details) till rader med den primära tabellen (Order).

I Power Query har en kolumn som innehåller en relaterad tabell värdet Post eller Tabell i cellen. Dessa kallas för strukturerade kolumner. Posten anger en enskild relaterad post och representerar en1:1-relation med aktuella data eller den primära tabellen. Tabellen anger en relaterad tabell och representerar en 1:N-relation med den aktuella eller primära tabellen. En strukturerad kolumn representerar en relation i en datakälla som har en relationsmodell. En strukturerad kolumn anger till exempel en entitet med en sekundärnyckelassociation i en OData-feed eller en sekundärnyckelrelation i en SQL Server databas.

När du expanderar Order_Details -tabellen kommer tre nya kolumner och ytterligare rader att läggas till Order-tabell, en för varje rad i den kapslade eller relaterad tabellen.

1. Rulla vågrätt till kolumnen Order_Details i dataförhandsgranskningen.

2. I kolumnen Order_Details väljer du expanderikonen ().

3. I listrutan Expandera:

   1. Markera (Markera alla kolumner) om du vill ta bort alla kolumner.

   2. Välj ProductID, UnitPrice och Quantity.

   3. Välj OK.

      

      Obs!: I Power Query kan du expandera tabeller som är länkade från en kolumn och sammanställa kolumnerna i den länkade tabellen innan du expanderar data i ämnestabellen. För mer information om hur du utför sammansättningsåtgärder, se Sammanställa data från en kolumn.

Steg 3: Ta bort andra kolumner för att endast visa kolumner av intresse

I det här steget du tar bort alla kolumner förutom OrderDate, ProductID, UnitPrice och Quantity -kolumnerna. 

1. Markera följande kolumner i Dataförhandsgranskning: 

   1. Markera den första kolumnen, OrderID.

   2. Skift+Klicka på den sista kolumnen, Speditör.

   3. Ctrl+klicka på OrderDate, Order_Details.ProductID, Order_Details.UnitPrice, and Order_Details.Quantity-kolumnerna.

2. Högerklicka på en markerad kolumnrubrik och välj Ta bort andra kolumner.

Steg 4: Beräkna radtotalen för varje Order_Details-rad

I det här steget skapar du en Custom column för att beräkna radtotalen för varje Order_Details-rad.

1. I Dataförhandsgranskning väljer du tabellikonen () i det övre vänstra hörnet i förhandsgranskningen.

2. Klicka på Lägg till anpassad kolumn.

3. Ange [Order_Details.Enhetspris] * [Order_Details.Quantity] i rutan Formel för anpassad kolumn i dialogrutan Anpassad kolumn.

4. I rutan Nytt kolumnnamn anger du Radsumma.

5. Välj OK.

Steg 5: Omvandla en OrderDate-årskolumn

I det här steget omvandlar du OrderDate-kolumner för att återge året för OrderDate.

1. Högerklicka på kolumnen OrderDate i Dataförhandsgranskning och välj Omvandla > År.

2. Byta namn på OrderDate-kolumnen till Year:

   1. Dubbelklicka på kolumnen OrderDate och ange Year eller

   2. Right-Click i kolumnen Orderdatum väljer du Byt namn och anger År.

Steg 6: Gruppera rader genom ProduktID och Year

1. I Dataförhandsgranskning väljer du År och Order_Details.ProductID.

2. Right-Click en av rubrikerna och välj Gruppera efter.

3. I dialogrutan Gruppera efter:

   1. I textrutan New column name skriver du in Total Sales.

   2. I listrutan Operation väljer du Sum.

   3. I listrutan Column väljer du Line Total.

4. Välj OK.

   

Steg 7: Byt namn på en fråga

Innan du importerar försäljningsdata till Excel byter du namn på frågan:

• I fönstret Frågeinställningar anger du Total försäljning i rutan Namn.

Resultat: Sista frågan för aktivitet 2

Efter att du utfört varje steg kommer du att ha en Total Sales-fråga över Northwind OData-feeden.

Sammanfattning: Power Query steg som skapats i aktivitet 2 

När du utför frågeaktiviteter i Power Query skapas och listas frågesteg i fönstret Frågeinställningar i listan Tillämpade steg. Varje fråga steg har en motsvarande Power Query-formel, som också kallas "M"-språket. Mer information om Power Query formler finns i Lär dig mer om Power Query formler.

Uppgift	Frågesteg	Formel
Ansluta till en OData-feed	Källa	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])
Välj en tabell	Navigering	= Källa{[Name="Orders"]}[Data]
Expandera tabellen för Order_Details	Expandera Order_Details	= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})
Ta bort andra kolumner för att endast visa kolumner av intresse	RemovedColumns	= Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})
Beräkna radtotalen för varje Order_Details-rad	Anpassad tillagd	= Table.AddColumn(RemovedColumns, "Custom", varje [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
Ändra till ett mer beskrivande namn, Lne Total	Namnändrade kolumner	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
Omvandla kolumnen OrderDate för att återge året	Extraherat år	= Table.TransformColumns(#"Grupperade rader",{{"År", Datum.År, Int64.Type}})
Ändra till  mer beskrivande namn, Orderdatum och År	Namnändrade kolumner 1	Table.RenameColumns (TransformedColumn ; { { "OrderDate", "Year" } })
Gruppera rader genom ProduktID och Year	GroupedRows	= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

Med Power Query kan du kombinera flera frågor genom att sammanfoga eller lägga till dem. Åtgärden Merge utförs på alla Power Query-frågor med en tabulär form, oberoende av den datakälla som uppgifterna kommer från. Mer information om att kombinera datakällor finns i Kombinera flera frågor.

I den här uppgiften kombinerar du frågorna Produkter och Total Sales med hjälp av en sammanslagningsfråga och åtgärden Expandera och läser sedan in frågan Total Sales per Product i Excel-datamodellen.

Steg 1: Koppla ProduktID till en Total Salessfråga

1. Gå till frågan Produkter på kalkylbladsfliken Produkter i Excel-arbetsboken.

2. Markera en cell i frågan och välj sedan Fråga > Slå samman.

3. I dialogrutan Slå samman väljer du Produkter som primär tabell och väljer Total Sales som sekundär eller relaterad fråga för sammanslagning. Total Sales blir en ny strukturerad kolumn med en expanderingsikon.

4. För att matcha Total Sales till Products genom ProductID väljer du kolumnen ProductID från Produkter och kolumnen Order_Details.ProductID från tabellen Totala Sales.

5. I dialogrutan Privacy Levels:

   1. Välj Organizational som isoleringsnivå för din sekretess för båda datakällor.

   2. Välj Spara.

6. Välj OK.

   Säkerhetsmeddelande:  Sekretessnivåer hindrar att användare oavsiktligt kombinerar data från flera datakällor, som kan vara privata eller organisatoriska. Beroende på frågan så kan användaren råka skicka data från den privata datakällan till en annan datakälla som kan vara skadlig. Power Query analyserar varje datakälla och klassificerar in i dess definierade sekretessnivå: offentlig, organisatorisk eller privat. Mer information om sekretessnivåer finns i Ange sekretessnivåer.

   

Resultat

Åtgärden Slå samman skapar en fråga. Frågeresultatet innehåller alla kolumner från den primära tabellen (Produkter) och en enda tabellstrukturerad kolumn till den relaterade tabellen (Total Sales). Välj expanderingsikonen för att lägga till nya kolumner i den primära tabellen från den sekundära eller relaterade tabellen.

Steg 2: Expandera en sammanslagen kolumn

I det här steget expanderar du den sammanslagna kolumnen med namnet NewColumn för att skapa två nya kolumner i frågan Produkter : År och Total Sales.

1. I Dataförhandsgranskning väljer du Expandera ikon () bredvid NewColumn.

2. I listrutan Visa :

   1. Markera (Markera alla kolumner) om du vill ta bort alla kolumner.

   2. Välj År och Total försäljning.

   3. Välj OK.

3. Byta namn på dessa två kolumner till Year och Total Sales.

4. Om du vill ta reda på vilka produkter och under vilka år produkterna fick den högsta försäljningsvolymen väljer du Sortera fallande efter Total försäljning.

5. Byt namn på frågan till Total Sales Per Product.

Resultat

Steg 3: Ladda en fråga om Total Sales Per Product till en Excel-datamodell

I det här steget läser du in en fråga i en Excel-datamodell för att skapa en rapport som är kopplad till frågeresultatet. När du har läst in data i Excel-datamodellen kan du använda Power Pivot för att fortsätta med dataanalysen.

1. Välj Start > Stäng & Läs in.

2. I dialogrutan Importera data kontrollerar du att du väljer Lägg till dessa data i datamodellen. Om du vill ha mer information om hur du använder den här dialogrutan väljer du frågetecknet (?).

Resultat

Du har en fråga om total försäljning per produkt som kombinerar data från filen Products.xlsx och Northwind OData-feeden. Den här frågan tillämpas på en Power Pivot-modell. Dessutom ändrar och uppdaterar ändringar i frågan den resulterande tabellen i datamodellen.

Sammanfattning: Power Query steg som skapats i Aktivitet 3

När du utför slå samman frågeaktiviteter i Power Query skapas och listas frågesteg i fönstret Frågeinställningar i listan Tillämpade steg. Varje fråga steg har en motsvarande Power Query-formel, som också kallas "M"-språket. Mer information om Power Query formler finns i Lär dig mer om Power Query formler.

Uppgift	Frågesteg	Formel
Sammanfoga ProductID i frågan Total Sales	Källa (datakälla för åtgärden Merge)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
Expandera en sammanfogad kolumn	Utökad total försäljning	= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
Byta namn på två kolumner	Namnändrade kolumner	= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Sortera total försäljning i stigande ordning	Sorterade rader	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})