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

I den här självstudiekursen kan du använda Frågeredigeraren i Power Query för att importera data från en lokal Excel-fil som innehåller produktinformation och från en OData-feed som innehåller produktorderinformation. Du utför transformerings- och sammansättningssteg och kombinerar data från båda källorna för att producera rapporten "Total Sales Per Product and Year".   

För att kunna utföra den här självstudiekursen behöver du arbetsboken Produkter. I dialogrutan Spara som namnger du filen Produkter och order.xlsx.

I den här uppgiften ska du importera produkter från filen Products and Orders.xlsx (nedladdad och omdöpt ovan) till en Excel-arbetsbok, höja rader till kolumnrubriker, ta bort några kolumner och läsa in frågan till ett kalkylblad.

Steg 1: Anslut till en Excel-arbetsbok

  1. Skapa en Excel-arbetsbok.

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

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

  4. I fönstret Navigatör dubbelklickar du på tabellen Produkter. Power Query-redigeraren visas.

Steg 2: Undersöka frågestegen

Som standard lägger Power Query automatiskt till flera steg som en förmån 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å steget Källa 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 och härdade 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 Data Previewväljer du kolumnerna ProductID,ProductName,CategoryIDoch QuantityPerUnit (använd Ctrl+klicka eller Skift+Klicka).

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

    Dölja andra kolumner

Steg 4: Läsa in produktfrågan

I det här steget kan du läsa in frågan Products till ett Excel-kalkylblad.

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

Sammanfattning: Power Query-steg skapade i uppgift 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

Source

= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)

Markera tabellen Produkter

Navigera

= Source{[Item="Products",Kind="Table"]}[Data]

Power Query identifierar automatiskt kolumndatatyper

Ä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

Andra kolumner borttagna

= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

I den här uppgiften ska du importera data till Excel-arbetsboken från exempelfeeden Northwind OData i http://services.odata.org/Northwind/Northwind.svc,expandera Order_Details-tabellen, ta bort kolumner, beräkna en radsumma, omvandla ett OrderDate, gruppera rader med ProductID och Year, byta namn på frågan och inaktivera nedladdning av frågor till Excel-arbetsboken.

Steg 1: Ansluta till en OData-feed

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

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

  3. Välj OK.

  4. I fönstret Navigatör dubbelklickar du på tabellen Order.

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) i rader med den primära tabellen (Order).

I Power Query har en kolumn som innehåller en relaterad tabell värdet Posteller Tabell i cellen. De kallas strukturerade kolumner. Post anger en enskild relaterad post och representerar en 1:1-relation med aktuella data eller primär tabell. Tabellen anger en relaterad tabell och representerar en 1:många-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 foreign key-association i en OData-feed eller en foreign key-relation 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ätttill den första Order_Details i förhandsgranskningsvyn.

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

  3. I listrutan Expandera:

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

    2. Välj Produkt-ID,Enhetsprisoch Antal.

    3. Välj OK.

      Expandera tabellänk för Order_Details

      Obs!: I Power Query kan du expandera tabeller som är länkade från en kolumn och aggregera 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. Välj följande kolumneri Förhandsgranskning:

    1. Markera den första kolumnen, OrderID.

    2. Skift+Klicka på den sista kolumnen, Shipper.

    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 Förhandsgranskningväljer du tabellikonen ( Tabellikonen ) i det övre vänstra hörnet av förhandsgranskningen.

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

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

  4. Ange Radsumma i rutan Nytt kolumnnamn.

  5. Välj OK.

Beräkna radtotalen för varje Order_Details-rad

Steg 5: Omvandla en OrderDate-årskolumn

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

  1. I Data Previewhögerklickar du på OrderDate-kolumnen och väljer Omvandla > Year.

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

    1. Dubbelklicka på kolumnen OrderDate och ange Year eller

    2. Right-Click i kolumnen OrderDate väljer du Byt namnoch anger År.

Steg 6: Gruppera rader genom ProduktID och Year

  1. I Data Previewväljer du Year och Order_Details.ProductID.

  2. Right-Click någon 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.

    Dialogrutan Gruppera efter för mängdåtgärder

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 Query Settings, i rutan Name enterTotal Sales.

Resultat: Slutfråga för uppgift 2

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

Total försäljning

Sammanfattning: Power Query-steg skapade i uppgift 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äs 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

Tillagd anpassad

= 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])

Byt till ett mer beskrivande namn, Lne Total

Kolumner som har bytt namn

= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})

Omvandla kolumnen OrderDate för att återge året

Extraherat år

= Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}})

Ändra till 

mer meningsfulla namn, OrderDate och Year

Kolumn 1 har bytt namn

Table.RenameColumns

(TransformedColumn ; { { "OrderDate", "Year" } })

Gruppera rader genom ProduktID och Year

GroupedRows

= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", varje List.Sum([Line Total]), typ nummer}})

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 ska du kombinera frågorna Products and Total Sales genom att använda en merge-fråga och expandera åtgärden och sedan läsa in frågan Total Sales per product till Excel-datamodellen.

Steg 1: Koppla ProduktID till en Total Salessfråga

  1. Gå till frågan Produkter i Excel-arbetsbokenkalkylbladsfliken Produkter.

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

  3. I dialogrutan Merge väljer du Products som den primära tabellen och väljer Total Sales som sekundär eller relaterad fråga att sammanfoga. Total Sales blir en ny strukturerad kolumn med en expand-ikon.

  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.

    Dialogrutan Slå samman

Resultat

Åtgärden Merge 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 ikonen Expandera för att lägga till nya kolumner i den primära tabellen från den sekundära eller relaterade tabellen.

Slå samman slutlig

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 produktfrågan:Year och Total Sales.

  1. I Data Previewväljer du Expandera-ikonen ( Visa ) bredvid NewColumn.

  2. I listrutan Expandera:

    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 som produkterna hade den högsta försäljningsvolymen väljer du Sortera fallande efter totalförsäljning.

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

Resultat

Expandera tabellänken

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

I det här steget kan du läsa in en fråga i en Excel-datamodellför att skapa en rapport kopplad till frågeresultatet. När du har läser in data i Excel-datamodellenkan du använda Power Pivot för vidare analys.

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

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

Resultat

Du har en Total Sales Per Product-fråga som kombinerar data från Products.xlsx-filen och Northwind OData-feed. Den här frågan tillämpas på en Power Pivot-modell. Dessutom kan ändringar i frågan ändra och uppdatera den resulterande tabellen i datamodellen.

Sammanfattning: Power Query-steg skapade i uppgift 3

När du utför Merge-frågeaktiviteter i Power Query, skapas frågesteg som listas 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äs 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

Expanderad total försäljning

= Table.ExpandTableColumn (Källa,"Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})

Byta namn på två kolumner

Kolumner som har bytt namn

= 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}})

Se även

Hjälp om Power Query för Excel

Behöver du mer hjälp?

Utöka dina Office-kunskaper
Utforska utbildning
Få nya funktioner först
Anslut till Office Insiders

Hade du nytta av den här informationen?

Tack för din feedback!

Tack för din feedback! Det låter som att det kan vara bra att koppla dig till en av våra Office-supportrepresentanter.

×