Gegevens uit meerdere gegevensbronnen combineren (Power Query)

Belangrijk : Dit artikel is automatisch vertaald, bekijk de disclaimer. De Engelse versie van dit artikel vindt u hier voor referentiedoeleinden.

Opmerking : Power Query heet Ophalen en transformeren in Excel 2016. De informatie hier is van toepassing op beide. Zie Ophalen en transformeren in Excel 2016 voor meer informatie.

In deze zelfstudie gebruikt u de Queryeditor van Power Query om gegevens te importeren uit een lokale Excel-bestand dat productinformatie bevat, en uit een OData-feed die bevat productinformatie volgorde. U-transformatie en aggregatie stappen uitvoeren en Combineer gegevens uit beide gegevensbronnen om te produceren, een rapport Totale verkoop per Product en het jaar .

Voor deze zelfstudie hebt u de werkmap Producten en orders nodig. Geef het bestand de naam Products en Orders.xlsx in het dialoogvenster Opslaan als.

In deze zelfstudie

Taak 1: Producten importeren in een Excel-werkmap

Stap 1: Verbinding maken met een Excel-werkmap

Stap 2: Van de eerste rij tabelkolomkoppen maken

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

Gemaakte Power Query-stappen

Stap 4: Een productquery importeren

Taak 2: Ordergegevens importeren uit een OData-feed

Stap 1: Verbinding maken met een OData-feed

Stap 2: Een tabel met ordergegevens uitbreiden

De tabelkoppeling Ordergegevens uitbreiden

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

Geselecteerde kolommen verwijderen

Stap 4: Het regeltotaal voor elke rij met ordergegevens berekenen

Het regeltotaal voor elke rij met ordergegevens berekenen

Stap 5: Een kolom Orderdatumjaar transformeren

Stap 6: Rijen groeperen op product-id en jaar

Stap 7: De naam van een query wijzigen

Uiteindelijke queryresultaten

Gemaakte Power Query-stappen

Stap 8: Het downloaden van query’s naar een Excel-werkmap uitschakelen

Het downloaden van een query uitschakelen

Taak 3: De query's Producten en Totale verkoop combineren

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

Stap 2: Een samenvoegingskolom uitbreiden

De tabelkoppeling NieuweKolom uitbreiden

Gemaakte Power Query-stappen

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

De query Totale verkoop per product laden in het Excel-gegevensmodel

Query Uiteindelijke totale verkoop per product

Taak 1: Producten importeren in een Excel-werkmap

In deze taak importeert u producten uit het bestand Producten en orders.xlsx in een Excel-werkmap.

Stap 1: Verbinding maken met een Excel-werkmap

  1. Maak een Excel-werkmap.

  2. Klik op het linttabblad POWER QUERY op Uit bestand > Uit Excel.

  3. Blader in Excel in het dialoogvenster Bladeren naar het pad van Producten en orders.xlsx of typ dit om het bestand te importeren of een koppeling hiernaar te maken.

  4. Dubbelklik in het deelvenster Navigator op het werkblad Producten of klik op Producten en vervolgens op Query bewerken. Wanneer u een query bewerkt, of verbinding maakt met een nieuwe gegevensbron, verschijnt het venster Query-editor.

    Opmerking : Aan het einde van dit artikel vindt u een korte video over het weergeven van de queryeditor.

Stap 2: Van de eerste rij tabelkolomkoppen maken

In het raster Queryvoorbeeld bevat de eerste rij van de tabel niet de namen van de tabelkolommen. Van de eerste rij tabelkolomkoppen maken:

  1. Klik op het tabelpictogram ( Tabelpictogram ) linksboven in het gegevensvoorbeeld.

  2. Klik op Eerste rij als koppen gebruiken.

Van de eerste rij tabelkolomkoppen maken

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 het raster Queryvoorbeeld de kolommen Product-id, Productnaam, Categorie-id en HoeveelheidPerEenheid (met Ctrl+klikken of Shift+klikken).

  2. Klik op het lint van de queryeditor op Kolommen verwijderen > Andere kolommen verwijderen of klik met de rechtermuisknop op een kolomkop en klik op Andere kolommen verwijderen.

    Andere kolommen verbergen

Gemaakte Power Query-stappen

Wanneer u queryactiviteiten in Power Query uitvoert, worden er 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 de Power Query-formuletaal.

Taak

Querystap

Formule

Verbinding maken met een Excel-werkmap

Bron

Source{[Name="Producten"]}[Gegevens]

Van de eerste rij tabelkolomkoppen maken

FirstRowAsHeader

Table.PromoteHeaders

(Producten)

Andere kolommen verwijderen, zodat alleen belangrijke kolommen worden weergegeven

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader, {"Product-id", "Productnaam", "Categorie-id", "HoeveelheidPerEenheid"})

Stap 4: Een productquery importeren

In deze stap importeert u de query Producten in uw Excel-werkmap.

  1. Klik op het lint van de queryeditor op Toepassen en sluiten. De resultaten worden weergegeven in een nieuw Excel-werkblad.

Naar boven

Taak 2: Ordergegevens importeren uit een OData-feed

In deze taak importeert u gegevens in uw Excel-werkmap uit de voorbeeld-OData-feed Northwind op http://services.odata.org/Northwind/Northwind.svc.

Stap 1: Verbinding maken met een OData-feed

  1. Klik op het linttabblad POWER QUERY op Uit een andere bron > Van OData-feed.

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

  3. Klik op OK.

  4. Dubbelklik in het deelvenster Navigator op de tabel Orders of klik op Orders en vervolgens op Bewerken.

Opmerking :  Wanneer u de muisaanwijzer over een tabel beweegt, ziet u een fly-out met een voorbeeld van de tabel.

De aanwijzer over een gegevensbron bewegen

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 de rijen uit de gerelateerde tabel (Ordergegevens) gecombineerd tot rijen uit de onderwerptabel (Orders).

In Power Query heeft een kolom met een koppeling naar een gerelateerde tabel een koppeling Vermelding of Tabel. Via de koppeling Vermelding gaat u naar één gerelateerde record, waarmee eeneen-op-een-relatie met een onderwerptabel wordt aangegeven. Via de koppeling Tabel gaat u naar een gerelateerde tabel, waarmee een een-op-veel-relatie met een onderwerptabel wordt aangegeven. Met een koppeling worden navigatie-eigenschappen in een gegevensbron in een relationeel model aangegeven. Voor een OData-feed wordt met de navigatie-eigenschappen een entiteit met een refererende-sleutelkoppeling aangegeven. In een database, zoals SQL Server, worden met de navigatie-eigenschappen refererende-sleutelrelaties in de database aangegeven.

De tabelkoppeling Ordergegevens uitbreiden

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 het deelvenster Queryvoorbeeld naar de kolom Ordergegevens.

  2. Klik in de kolom Ordergegevens op het pictogram Uitvouwen ( Uitbreiden ).

  3. In de vervolgkeuzelijst Uitvouwen:

    1. Klik op (Alle kolommen selecteren) om alle kolommen te wissen.

    2. Klik op Product-id, Prijs per eenheid en Hoeveelheid.

    3. Klik op OK.

      De tabelkoppeling Ordergegevens uitbreiden

      Opmerking : In Power Query kunt u gekoppelde tabellen uitbreiden naar een kolom en ook aggregatiebewerkingen uitvoeren op de kolommen van de gekoppelde tabel 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. In de vorige taak hebt u Andere kolommen verwijderen gebruikt. Voor deze taak verwijdert u geselecteerde kolommen.

Geselecteerde kolommen verwijderen

  1. Selecteer alle kolommen in het deelvenster Queryvoorbeeld:

    1. Klik op de eerste kolom (Order-id).

    2. Houd Shift ingedrukt en 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 klik op 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.

Het regeltotaal voor elke rij met ordergegevens berekenen

  1. Klik in het deelvenster Queryvoorbeeld op het tabelpictogram ( Tabelpictogram ) linksboven in het voorbeeld.

  2. Klik op Kolom invoegen > Aangepast.

  3. Voer [Ordergegevens.Prijs per eenheid] * [Ordergegevens.Hoeveelheid] in het tekstvak Aangepaste kolomformule in het dialoogvenster Aangepaste kolom invoegen in.

  4. Voer Totale verkoop in in het tekstvak Regeltotaal.

  5. Klik op OK .

Het regeltotaal voor elke rij met ordergegevens berekenen

Stap 5: Een kolom Orderdatumjaar transformeren

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

  1. Klik in het raster Voorbeeld met de rechtermuisknop op de kolom Orderdatum en klik op Transformeren > Jaar.

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

    1. Dubbelklik op de kolom Orderdatum en typ Jaar of

    2. Klik met de rechtermuisknop op de kolom Orderdatum, klik op Naam wijzigen en typ Jaar.

Stap 6: Rijen groeperen op product-id en jaar

  1. Selecteer Jaar en Ordergegevens.Product-id in het raster Queryvoorbeeld.

  2. Klik met de rechtermuisknop op een van de koppen en klik op 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. Klik op OK.

    Dialoogvenster Groeperen op voor aggregatiebewerkingen

Stap 7: De naam van een query wijzigen

Geef als naam van de query Totale verkoop op voordat u de verkoopgegevens importeert in Excel:

  1. Ga naar het deelvenster Query-instellingen en typ in het tekstvak Naam de waarde Totale verkoop.

Uiteindelijke queryresultaten

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

Totale verkoop

Gemaakte Power Query-stappen

Wanneer u queryactiviteiten in Power Query uitvoert, worden er 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 de Power Query-formuletaal.

Taak

Querystap

Formule

Verbinding maken met een OData-feed

Bron

Source{[Name="Orders"]}[Data]

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

IngevoegdeKolommen

Table.AddColumn

(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

De kolom Orderdatum transformeren, zodat het jaar wordt weergegeven

RenamedColumns

Table.RenameColumns

(InsertedCustom,{{"Custom", "Line Total"}})

TransformedColumn

Table.TransformColumns

(RenamedColumns,{{"OrderDate", Date.Year}})

GewijzigdeKolomnamen1

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

Stap 8: Het downloaden van query’s naar een Excel-werkmap uitschakelen

U schakelt het downloaden van query’s naar een Excel-werkmap uit, omdat met de query Totale verkoop niet het uiteindelijke rapport Totale verkoop per product en jaar wordt weergegeven. Wanneer de optie Laden in werkblad is ingesteld op Uit in het deelvenster Queryinstellingen, wordt het gegevensresultaat van deze query niet gedownload, maar kan de query nog steeds worden gecombineerd met andere query’s om het gewenste resultaat te verkrijgen. In de volgende taak leert u hoe u deze query combineert met de query Producten.

Het downloaden van een query uitschakelen

  1. Schakel in het deelvenster Queryinstellingen de optie Laden in werkblad uit.

  2. Klik op het lint van de queryeditor op Toepassen en sluiten. In het deelvenster Werkmapquery’s wordt bij de query Totale verkoopLaden is uitgeschakeld weergegeven.

    Het downloaden van een query uitschakelen

Naar boven

Taak 3: De query's Producten en Totale verkoop combineren

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 door de querystappen Samenvoegen en Uitvouwen uit te voeren.

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

  1. Ga in de Excel-werkmap naar de query Producten op Blad2.

  2. Klik op het linttabblad QUERY op Samenvoegen.

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

  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. Klik op Opslaan .

  6. Klik op 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 voor meer informatie over privacyniveaus.

    Dialoogvenster Samenvoegen

Wanneer u op OK hebt geklikt, wordt er via de bewerking Samenvoegen een query gemaakt. Het queryresultaat bevat alle kolommen uit de primaire tabel (Producten) en één kolom met een navigatiekoppeling naar de gerelateerde tabel (Totale verkoop). Met de bewerking Uitvouwen voegt u nieuwe kolommen toe aan de primaire tabel of onderwerptabel uit de gerelateerde tabel.

Definitief samenvoegen

Stap 2: Een samenvoegingskolom uitbreiden

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

De tabelkoppeling NieuweKolom uitbreiden

  1. Klik in het raster Queryvoorbeeld op het uitbreidingspictogram NieuweKolom ( Uitbreiden ).

  2. In de vervolgkeuzelijst Uitvouwen:

    1. Klik op (Alle kolommen selecteren) om alle kolommen te wissen.

    2. Klik op Jaar en Totale verkoop.

    3. Klik op OK.

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

  4. Kies bij Aflopend sorteren voor Totale verkoop om te bekijken welke producten in welke jaren het beste zijn verkocht.

  5. Wijzig de querynaam in Totale verkoop per product.

Tabelkoppeling uitvouwen

Gemaakte Power Query-stappen

Wanneer u queryactiviteiten voor samenvoegen uitvoert in Power Query, worden er 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 de Power Query-formuletaal.

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"},"NewColumn")

Een samenvoegingskolom uitbreiden

NieuweKolomUitbreiden

Table.ExpandTableColumn

(Source, "NewColumn", {"Year", "Total Sales"}, {"NewColumn.Year", "NewColumn.Total Sales"})

RenamedColumns

Table.RenameColumns

(#"Expand NewColumn",{{"NewColumn.Year", "Year"}, {"NewColumn.Total Sales", "Total Sales"}})

SortedRows

Table.Sort

(RenamedColumns,{{"Total Sales", Order.Descending}})

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

In deze stap schakelt u de optie Laden in werkblad uit en laadt u een query in het Excel-gegevensmodel om een rapport te maken dat is gekoppeld aan het queryresultaat. Naast het laden van queryresultaten in een Excel-werkblad, kunt u in Power Query een queryresultaat in een Excel-gegevensmodel laden. Nadat u gegevens in het Excel-gegevensmodel hebt geladen, kunt u Power Pivot en Power View gebruiken voor verdere gegevensanalyse.

De query Totale verkoop per product laden in het Excel-gegevensmodel

  1. Schakel in het deelvenster Query-instellingen de optie Laden in werkblad uit en schakel de optie Laden in gegevensmodel in.

  2. Als u de query in het Excel-gegevensmodel wilt laden, klikt u op Toepassen en sluiten.

Excel-gegevensmodel laden

Query Uiteindelijke totale verkoop per product

Nadat u de stappen hebt uitgevoerd, beschikt u over een query Totale verkoop per product die gegevens combineert uit het bestand Producten en orders.xlsx en de OData-feed Northwind. Deze query kan worden toegepast op een Power Pivot-model. Daarnaast wordt de resulterende tabel in het Power Pivot-model gewijzigd en vernieuwd met wijzigingen in de query in Power Query.

Naar boven

Opmerking : De queryeditor wordt alleen weergegeven wanneer u een nieuwe query laadt, bewerkt of maakt met Power Query. In de volgende video ziet u dat het venster Queryeditor wordt weergegeven nadat een query vanuit een Excel-werkmap wordt bewerkt. Als u de queryeditor wilt weergeven zonder een bestaande werkmapquery te laden of te bewerken, selecteert u vanuit het gedeelte Externe gegevens ophalen op het linttabblad Power QueryUit een andere bron > Lege query. In de volgende video ziet u een manier om de queryeditor weer te geven.

De weergave bepalen van Queryeditor in Excel

Opmerking : Disclaimer voor automatische vertaling: Dit artikel is vertaald door een computersysteem zonder menselijke tussenkomst. Microsoft biedt deze automatische vertalingen aan om niet-Engels sprekende gebruikers te helpen de inhoud over producten, services en technologieën van Microsoft te raadplegen. Omdat het artikel automatisch is vertaald, bevat het mogelijk fouten in grammatica, woordenschat en syntaxis.

Uw vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagents.

×