Meer informatie over het combineren van meerdere gegevensbronnen (Power Query)

In deze taak importeert u producten uit het bestand Producten en Orders.xlsx (hierboven gedownload en hernoemd) in een Excel-werkmap, promoot u rijen tot kolomkoppen, verwijdert u enkele kolommen en laadt u de query naar een werkblad.

Stap 1: Verbinding maken met een Excel-werkmap

1. Maak een Excel-werkmap.

2. Selecteer Gegevens > Gegevens ophalen > uit bestand > uit werkmap.

3. Blader in het dialoogvenster Gegevens importeren naar het Products.xlsx bestand dat u hebt gedownload en zoek het bestand dat u hebt gedownload en selecteer openen.

4. Dubbelklik in het deelvenster Navigator op de tabel Producten . De Power Power Query-editor wordt weergegeven.

Stap 2: De querystappen onderzoeken

Standaard voegt Power Query voor uw gemak automatisch verschillende stappen toe. Bekijk elke stap onder Toegepaste stappen in het deelvenster Query-instellingen voor meer informatie.

1. Klik met de rechtermuisknop op de stap Bron en selecteer Instellingen bewerken. Deze stap is gemaakt toen u de werkmap importeerde.

2. Klik met de rechtermuisknop op de navigatiestap en selecteer Instellingen bewerken. Deze stap is gemaakt toen u de tabel selecteerde in het dialoogvenster Navigatie .

3. Klik met de rechtermuisknop op de stap Gewijzigd type en selecteer Instellingen bewerken. Deze stap is gemaakt door Power Query die de gegevenstypen van elke kolom heeft afgeleid. Selecteer de pijl-omlaag rechts van de formulebalk om de volledige formule weer te geven.

Stap 3: Andere kolommen verwijderen, zodat alleen belangrijke kolommen worden weergegeven

In deze stap verwijdert u alle kolommen behalve Product-id, Productnaam, Categorie-id en HoeveelheidPerEenheid.

1. Selecteer in Data Preview de kolommen ProductID, ProductName, CategoryID en QuantityPerUnit (gebruik Ctrl+Klik of Shift+Klik).

2. Selecteer Kolommen verwijderen > Andere kolommen verwijderen.

   

Stap 4: De productquery laden

In deze stap laadt u de query Producten in een Excel-werkblad.

• Selecteer Start > & laden sluiten. De query wordt weergegeven in een nieuw Excel-werkblad.

Samenvatting: Power Query stappen die zijn gemaakt in taak 1

Wanneer u queryactiviteiten uitvoert in Power Query, worden querystappen gemaakt en weergegeven in het deelvenster Queryinstellingen in de lijst Toegepaste stappen. Elke querystap heeft een bijbehorende Power Query-formule, ook wel bekend als de "M"-taal. Zie Power Query formules maken in Excel voor meer informatie over Power Query formules.

Taak	Querystap	Formule
Een Excel-werkmap importeren	Bron	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Selecteer de tabel Producten	Navigeren	= Source{[Item="Products",Kind="Table"]}[Gegevens]
Power Query detecteert automatisch kolomgegevenstypen	Type gewijzigd	= 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}})
Andere kolommen verwijderen, zodat alleen belangrijke kolommen worden weergegeven	Andere kolommen verwijderd	= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

In deze taak importeert u gegevens in uw Excel-werkmap vanuit de voorbeeldfeed Northwind OData op http://services.odata.org/Northwind/Northwind.svc,vouwt u de Order_Details tabel uit, verwijdert u kolommen, berekent u een regeltotaal, transformeert u een OrderDate, groepeert u rijen op ProductID en Year, wijzigt u de naam van de query en schakelt u het downloaden van query's uit naar de Excel-werkmap.

Stap 1: verbinding maken met een OData-feed

1. Selecteer Gegevens > Gegevens ophalen > uit andere bronnen > uit de OData-feed.

2. Voer in het dialoogvenster OData-feed de URL in voor de OData-feed Northwind.

3. Selecteer OK.

4. Dubbelklik in het deelvenster Navigator op de tabel Orders .

Stap 2: Een tabel met ordergegevens uitbreiden

In deze stap vouwt u de tabel Ordergegevens uit die is gerelateerd aan de tabel Orders, om de kolommen Product-id, Prijs per eenheid en Hoeveelheid van Ordergegevens te combineren in de tabel Orders. Via de bewerking Uitvouwen worden kolommen van een gerelateerde tabel gecombineerd in een onderwerptabel. Wanneer de query wordt uitgevoerd, worden rijen uit de gerelateerde tabel (Order_Details) gecombineerd in rijen met de primaire tabel (Orders).

In Power Query heeft een kolom met een gerelateerde tabel de waarde Record of Tabel in de cel. Dit worden gestructureerde kolommen genoemd. Record geeft één gerelateerde record aan en vertegenwoordigt eeneen-op-een-relatie met de huidige gegevens of primaire tabel. Tabel geeft een gerelateerde tabel aan en vertegenwoordigt een een-op-veel-relatie met de huidige of primaire tabel. Een gestructureerde kolom vertegenwoordigt een relatie in een gegevensbron met een relationeel model. Een gestructureerde kolom geeft bijvoorbeeld een entiteit aan met een refererende sleutelkoppeling in een OData-feed of een relatie met een refererende sleutel in een SQL Server-database.

Nadat u de tabel Ordergegevens hebt uitgebreid, worden er drie nieuwe kolommen en extra rijen toegevoegd aan de tabel Orders, één kolom voor elke rij in de geneste of gerelateerde tabel.

1. Schuif in Gegevensvoorbeeld horizontaal naar de kolom Order_Details .

2. Selecteer in de kolom Order_Details het uitvouwpictogram ().

3. In de vervolgkeuzelijst Uitbreiden:

   1. Selecteer (Alle kolommen selecteren) om alle kolommen te wissen.

   2. Selecteer ProductID, Prijs per eenheid en Hoeveelheid.

   3. Selecteer OK.

      

      Opmerking: In Power Query kunt u tabellen uitvouwen die zijn gekoppeld vanuit een kolom en de kolommen van de gekoppelde tabel aggregeren voordat u de gegevens in de onderwerptabel uitbreidt. Zie Gegevens van een kolom aggregeren voor meer informatie over het uitvoeren van aggregatiebewerkingen.

Stap 3: Andere kolommen verwijderen, zodat alleen belangrijke kolommen worden weergegeven

In deze stap verwijdert u alle kolommen met uitzondering van de kolommen Orderdatum, Product-id, Prijs per eenheid en Hoeveelheid. 

1. Selecteer in Gegevensvoorbeeld de volgende kolommen: 

   1. Selecteer de eerste kolom, OrderID.

   2. Shift+Klik op de laatste kolom, Verzender.

   3. Houd Ctrl ingedrukt en klik op de kolommen Orderdatum, Ordergegevens.Product-id, Ordergegevens.Prijs per eenheid en Ordergegevens.Hoeveelheid.

2. Klik met de rechtermuisknop op een geselecteerde kolomkop en selecteer Andere kolommen verwijderen.

Stap 4: Het regeltotaal voor elke rij met ordergegevens berekenen

In deze stap maakt u een aangepaste kolom om het regeltotaal voor elke rij met ordergegevens te berekenen.

1. Selecteer in Gegevensvoorbeeld het tabelpictogram () in de linkerbovenhoek van het voorbeeld.

2. Klik op Aangepaste kolom toevoegen.

3. Voer in het dialoogvenster Aangepaste kolom in het vak Formule van aangepaste kolom[Order_Details.UnitPrice] * [Order_Details.Quantity] in.

4. Voer in het vak Nieuwe kolomnaamregeltotaal in.

5. Selecteer OK.

Stap 5: Een kolom Orderdatumjaar transformeren

In deze stap transformeert u de kolom Orderdatum om het Orderdatumjaar weer te geven.

1. Klik in Gegevensvoorbeeld met de rechtermuisknop op de kolom OrderDate en selecteer Transformeren > Jaar.

2. Wijzig de naam van de kolom Orderdatum in Jaar:

   1. Dubbelklik op de kolom Orderdatum en typ Jaar of

   2. Right-Click in de kolom OrderDate , selecteert u Naam wijzigen en voert u Year in.

Stap 6: Rijen groeperen op product-id en jaar

1. Selecteer in Data Previewde optie Year en Order_Details.ProductID.

2. Right-Click een van de kopteksten en selecteer Groeperen op.

3. In het dialoogvenster Groeperen op:

   1. Typ Totale verkoop in het tekstvak Nieuwe kolomnaam.

   2. Selecteer Som in de vervolgkeuzelijst Bewerking.

   3. Selecteer Regeltotaal in de vervolgkeuzelijst Kolom.

4. Selecteer OK.

   

Stap 7: De naam van een query wijzigen

Voordat u de verkoopgegevens in Excel importeert, wijzigt u de naam van de query:

• Voer in het deelvenster Query-instellingen in het vak Naamtotale verkoop in.

Resultaten: laatste query voor taak 2

Wanneer u elke stap hebt uitgevoerd, beschikt u over de query Totale verkoop voor de OData-feed Northwind.

Samenvatting: Power Query stappen die zijn gemaakt in taak 2 

Wanneer u queryactiviteiten uitvoert in Power Query, worden querystappen gemaakt en weergegeven in het deelvenster Queryinstellingen in de lijst Toegepaste stappen. Elke querystap heeft een bijbehorende Power Query-formule, ook wel bekend als de "M"-taal. Zie Meer informatie over Power Query formules voor meer informatie over Power Query formules.

Taak	Querystap	Formule
Verbinding maken met een OData-feed	Bron	= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])
Een tabel selecteren	Navigatie	= Source{[Name="Orders"]}[Gegevens]
De tabel Ordergegevens uitbreiden	Ordergegevens uitbreiden	= Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})
Andere kolommen verwijderen, zodat alleen belangrijke kolommen worden weergegeven	VerwijderdeKolommen	= Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})
Het regeltotaal voor elke rij met ordergegevens berekenen	Aangepast toegevoegd	= 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])
Wijzig in een meer betekenisvolle naam, Lne Total	Naam van kolommen gewijzigd	= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
De kolom Orderdatum transformeren, zodat het jaar wordt weergegeven	Geëxtraheerd jaar	= Table.TransformColumns(#"Gegroepeerde rijen",{{"Year", Date.Year, Int64.Type}})
Wijzigen naar  meer betekenisvolle namen, OrderDatum en Jaar	Naam van kolommen 1 gewijzigd	Table.RenameColumns (TransformedColumn,{{"OrderDate", "Year"}})
Rijen groeperen op product-id en jaar	GegroepeerdeRijen	= Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

Met Power Query kunt u meerdere query's combineren door deze samen te voegen of toe te voegen. De bewerking Samenvoegen wordt uitgevoerd op een Power Query-query met een tabelvorm, onafhankelijk van de gegevensbron waarvan de gegevens afkomstig zijn. Zie Meerdere query's combineren voor meer informatie over het combineren van gegevensbronnen.

In deze taak combineert u de query's Producten en Totale verkoop met behulp van een samenvoegquery en de bewerking Uitvouwen en laadt u vervolgens de query Totale verkoop per product in het Excel-gegevensmodel.

Stap 1: Product-id samenvoegen in de query Totale verkoop

1. Navigeer in de Excel-werkmap naar de query Producten op het werkbladtabblad Producten .

2. Selecteer een cel in de query en selecteer vervolgens Query > Samenvoegen.

3. Selecteer in het dialoogvenster Samenvoegen de optie Producten als primaire tabel en selecteer Totale verkoop als de secundaire of gerelateerde query die u wilt samenvoegen. Totale verkoop wordt een nieuwe gestructureerde kolom met een uitvouwpictogram.

4. Als u Totale verkoop en Producten wilt afstemmen op Product-id, selecteert u de kolom Product-id in de tabel Producten en de kolom Ordergegevens.Product-id in de tabel Totale verkoop.

5. In het dialoogvenster Privacyniveaus:

   1. Selecteer Van bedrijf voor uw privacy-isolatieniveau voor beide gegevensbronnen.

   2. Kies Opslaan.

6. Selecteer OK.

   Opmerking over de beveiliging: Via privacyniveaus kunt u voorkomen dat een gebruiker per ongeluk gegevens uit meerdere gegevensbronnen combineert, die mogelijk persoonlijk of van het bedrijf zijn. Afhankelijk van de query kan een gebruiker per ongeluk gegevens van de persoonlijke gegevensbron verzenden naar een andere gegevensbron die mogelijk schadelijk is. In Power Query wordt elke gegevensbron geanalyseerd en wordt deze geclassificeerd in het gedefinieerde privacyniveau: Openbaar, Van bedrijf en Persoonlijk. Zie Privacyniveaus instellen voor meer informatie over privacyniveaus.

   

Resultaat

Met de bewerking Samenvoegen wordt een query gemaakt. Het queryresultaat bevat alle kolommen uit de primaire tabel (Producten) en één gestructureerde tabelkolom tot de gerelateerde tabel (Totale verkoop). Selecteer het pictogram Uitvouwen om nieuwe kolommen toe te voegen aan de primaire tabel vanuit de secundaire of gerelateerde tabel.

Stap 2: Een samengevoegde kolom uitvouwen

In deze stap vouwt u de samengevoegde kolom uit met de naam NewColumn om twee nieuwe kolommen te maken in de query Producten : Jaar en Totale verkoop.

1. Selecteer in Gegevensvoorbeeldde optie Pictogram uitvouwen () naast NewColumn.

2. In de vervolgkeuzelijst Uitvouwen :

   1. Selecteer (Alle kolommen selecteren) om alle kolommen te wissen.

   2. Selecteer Jaar en Totale verkoop.

   3. Selecteer OK.

3. Wijzig deze twee kolomnamen in Jaar en Totale verkoop.

4. Als u wilt zien welke producten en in welke jaren de producten het hoogste verkoopvolume hebben ontvangen, selecteert u Aflopend sorteren op totale verkoop.

5. Wijzig de querynaam in Totale verkoop per product.

Resultaat

Stap 3: Een query Totale verkoop per product laden in een Excel-gegevensmodel

In deze stap laadt u een query in een Excel-gegevensmodel om een rapport te maken dat is verbonden met het queryresultaat. Nadat u gegevens in het Excel-gegevensmodel hebt geladen, kunt u Power Pivot gebruiken om uw gegevensanalyse te vervolgen.

1. Selecteer Start > & Laden sluiten.

2. Zorg ervoor dat u in het dialoogvenster Gegevens importeren de optie Deze gegevens toevoegen aan het gegevensmodel selecteert. Selecteer het vraagteken (?) voor meer informatie over het gebruik van dit dialoogvenster.

Resultaat

U hebt een query Totale verkoop per product waarmee gegevens uit het Products.xlsx-bestand en de OData-feed northwind worden gecombineerd. Deze query wordt toegepast op een Power Pivot-model. Daarnaast wordt door wijzigingen in de query de resulterende tabel in het gegevensmodel gewijzigd en vernieuwd.

Samenvatting: Power Query stappen die zijn gemaakt in taak 3

Wanneer u queryactiviteiten samenvoegen uitvoert in Power Query, worden querystappen gemaakt en weergegeven in het deelvenster Query-instellingen in de lijst Toegepaste stappen. Elke querystap heeft een bijbehorende Power Query-formule, ook wel bekend als de "M"-taal. Zie Meer informatie over Power Query formules voor meer informatie over Power Query formules.

Taak	Querystap	Formule
Product-id samenvoegen in de query Totale verkoop	Bron (gegevensbron voor de bewerking Samenvoegen)	= Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
Een samenvoegingskolom uitbreiden	Uitgebreide totale verkoop	= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
Naam van twee kolommen wijzigen	Naam van kolommen gewijzigd	= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
Totale verkoop in oplopende volgorde sorteren	Gesorteerde rijen	= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})