In deze zelfstudie gebruikt u de Power Query-editor van Power Query om gegevens te importeren uit een lokaal Excel-bestand met productinformatie en een OData-feed met productordergegevens. Voer transformatie- en aggregatiestappen uit en combineer gegevens uit beide bronnen om een rapport met de totale verkoop per product en het jaar te maken.
Als u deze zelfstudie wilt voltooien, hebt u de werkmap Producten nodig. Geef het bestand de naam Products en Orders.xlsx in het dialoogvenster Opslaan als.
Taak 1: Producten importeren in een Excel-werkmap
In deze taak importeert u producten uit het bestand Producten en Orders.xlsx (gedownload en hernoemd in de vorige sectie) in een Excel-werkmap. Vervolgens verhoogt u het niveau van rijen tot kolomkoppen, verwijdert u enkele kolommen en laadt u de query in een werkblad.
Stap 1: Verbinding maken met een Excel-werkmap
- Maak een Excel-werkmap.
- Selecteer Gegevens>ophalen>uit bestand>uit werkmap.
- Blader in het dialoogvenster Gegevens importeren naar het Products.xlsx bestand dat u hebt gedownload en selecteer vervolgens Openen.
- Dubbelklik in het deelvenster Navigator op de tabel Producten . De Power Query-editor wordt weergegeven.
Stap 2: De querystappen bekijken
In Power Query worden standaard automatisch diverse stappen toegevoegd, als het u uitkomt. Bekijk elke stap onder Toegepaste stappen in het deelvenster Query-instellingen voor meer informatie.
- Klik met de rechtermuisknop op de stap Bron en selecteer Instellingen bewerken. Deze stap is gemaakt toen u de werkmap importeerde.
- Klik met de rechtermuisknop op de navigatiestap en selecteer Instellingen bewerken. Deze stap is gemaakt toen u de tabel selecteerde in het navigatiedialoogvenster.
- Klik met de rechtermuisknop op de stap Type gewijzigd 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 hele formule te bekijken.
Stap 3: Andere kolommen verwijderen, zodat alleen belangrijke kolommen worden weergegeven
In deze stap verwijdert u alle kolommen met uitzondering van Product-id, Productnaam, Categorie-id en HoeveelheidPerEenheid.
- Selecteer in Voorbeeld van gegevens de kolommen Product-id, Productnaam, Categorie-id en HoeveelheidPereenheid (gebruik Ctrl+klikken of Shift+klikken).
- Selecteer Kolommen> verwijderenAndere kolommen verwijderen.
Stap 4: De productenquery laden
In deze stap laadt u de query Producten in een Excel-werkblad.
- Selecteer Start,>sluiten & laden. De query wordt weergegeven in een nieuw Excel-werkblad.
Overzicht: Power Query-stappen die in taak 1 zijn gemaakt
Wanneer u queryactiviteiten in Power Query uitvoert, worden er querystappen gemaakt en worden deze 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 Power Query documentatie 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"]}[Data] |
| In Power Query worden kolomgegevenstypen automatisch gedetecteerd | 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,{"Product-id", "Productnaam", "Categorie-id", "HoeveelheidPerEenheid"}) |
Taak 2: Ordergegevens importeren uit een OData-feed
In deze taak importeert u gegevens in uw Excel-werkmap vanuit de voorbeeld-OData-feed Northwind op http://services.odata.org/Northwind/Northwind.svc, vouwt u de tabel Order_Details uit, verwijdert u kolommen, berekent u een regeltotaal, transformeert u een orderdatum, groepeert u rijen op product-id en jaar, wijzigt u de naam van de query en schakelt u het downloaden van query's naar de Excel-werkmap uit.
Stap 1: Verbinding maken met een OData-feed
- Selecteer Gegevens>>ophalenuit andere bronnen>van OData-feed.
- Voer in het dialoogvenster OData-feed de URL in voor de OData-feed Northwind.
- Selecteer OK.
- Dubbelklik in het deelvenster Navigator op de tabel Orders .
Stap 2: Een Order_Details tabel 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 tot 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. Met record wordt een enkele gerelateerde record aangegeven en een een-op-een-relatie met de huidige gegevens of primaire tabel. In een tabel wordt een gerelateerde tabel aangegeven en er is een een-op-veel-relatie met de huidige tabel of primaire tabel. Een gestructureerde kolom vertegenwoordigt een relatie in een gegevensbron die een relationeel model bevat. Een gestructureerde kolom geeft bijvoorbeeld een entiteit aan met een refererende-sleutelkoppeling in een OData-feed of een refererende-sleutelrelatie in een SQL Server-database.
Nadat u de tabel Order_Details hebt uitgebreid, worden er drie nieuwe kolommen en extra rijen toegevoegd aan de tabel Orders , één voor elke rij in de geneste of gerelateerde tabel.
Schuif in Voorbeeld van gegevens horizontaal naar de Order_Details kolom.
Selecteer in de Order_Details kolom het pictogram voor uitvouwen (
).In de vervolgkeuzelijst Uitbreiden:
Selecteer (Alle kolommen selecteren) om alle kolommen te wissen.
Selecteer Product-id,Prijs per eenheid en Aantal.
Selecteer OK.
Opmerking
In Power Query kunt u tabellen uitvouwen die vanuit een kolom zijn gekoppeld en de kolommen van de gekoppelde tabel aggregeren voordat u de gegevens in de onderwerptabel uitbreidt. Zie Gegevens uit een kolom aggregeren (Power Query) 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 Aantal .
Selecteer in Voorbeeld van gegevens de volgende kolommen:
- Selecteer de eerste kolom, Ordernummer.
- Shift+klik op de laatste kolom, Verzender.
- Houd Ctrl ingedrukt en klik op de kolommen Orderdatum, Ordergegevens.Product-id, Ordergegevens.Prijs per eenheid en Ordergegevens.Hoeveelheid.
Klik met de rechtermuisknop op een geselecteerde kolomkop en selecteer Andere kolommen verwijderen.
Stap 4: Het regeltotaal voor elke Order_Details rij berekenen
In deze stap maakt u een aangepaste kolom om het regeltotaal voor elke rij met ordergegevens te berekenen.
- Selecteer in Voorbeeld van gegevens het tabelpictogram (
) in de linkerbovenhoek van het voorbeeld. - Selecteer Aangepaste kolom toevoegen.
- Voer in het dialoogvenster Aangepaste kolom in het formulevak Aangepaste kolom[Order_Details.Prijs per eenheid] * [Order_Details.Quantity] in.
- Voer in het vak Nieuwe kolomnaamRegeltotaal in.
- Selecteer OK.
Stap 5: Een kolom Orderdatumjaar transformeren
In deze stap transformeert u de kolom Orderdatum om het Orderdatumjaar weer te geven.
Klik in Voorbeeld van gegevens met de rechtermuisknop op de kolom Orderdatum en selecteer Jaar transformeren>.
Wijzig de naam van de kolom Orderdatum in Jaar:
- Dubbelklik op de kolom Orderdatum en voer Jaar of
- Klik met de rechtermuisknop op de kolom Orderdatum , selecteer Naam wijzigen en voer het jaar in.
Stap 6: Rijen groeperen op product-id en jaar
Selecteer in Voorbeeld van gegevensJaar en Order_Details.ProductID.
Klik met de rechtermuisknop op een van de kopteksten en selecteer Groeperen op.
In het dialoogvenster Groeperen op:
- Typ Totale verkoop in het tekstvak Nieuwe kolomnaam.
- Selecteer Som in de vervolgkeuzelijst Bewerking.
- Selecteer Regeltotaal in de vervolgkeuzelijst Kolom.
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
Nadat u elke stap hebt uitgevoerd, beschikt u over de query Totale verkoop voor de OData-feed Northwind.
Overzicht: Power Query-stappen die in taak 2 zijn gemaakt
Wanneer u queryactiviteiten in Power Query uitvoert, worden er querystappen gemaakt en worden deze 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 Power Query documentatie 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"]}[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 | 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 duidelijkere naam, Lne Total | Hernoemde kolommen | = Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}}) |
| De kolom Orderdatum transformeren, zodat het jaar wordt weergegeven | Geëxtraheerd jaar | = Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}}) |
| Wijzigen naar Betekenisvollere namen, Orderdatum en Jaar |
Naam van kolom 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}}) |
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. U kunt de bewerking Samenvoegen uitvoeren op elke Power Query-query met een tabelvorm, ongeacht de gegevensbron. Zie Meerdere query's combineren (Power Query) voor meer informatie over het combineren van gegevensbronnen.
In deze taak combineert u de query's Producten en Totale verkoop met behulp van de bewerking Samenvoegen en Uitvouwen , waarna u de query Totale verkoop per product laadt in het Excel-gegevensmodel.
Stap 1: Product-id samenvoegen in de query Totale verkoop
Ga in de Excel-werkmap naar de query Producten op het tabblad Producten op het werkblad Producten.
Selecteer een cel in de query en selecteer vervolgens Query>samenvoegen.
Selecteer in het dialoogvenster SamenvoegenProducten als primaire tabel en selecteer Totale verkoop als secundaire of gerelateerde query die u wilt samenvoegen. Totale verkoop wordt een nieuwe gestructureerde kolom met een uitvouwpictogram.
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.
In het dialoogvenster Privacyniveaus:
- Selecteer Van bedrijf voor uw privacy-isolatieniveau voor beide gegevensbronnen.
- Kies Opslaan.
Selecteer OK.
Opmerking
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 (Power Query) 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 naar de gerelateerde tabel (Totale verkoop). Selecteer het pictogram Uitvouwen om nieuwe kolommen toe te voegen aan de primaire tabel uit de secundaire of gerelateerde tabel.
Stap 2: Een samengevoegde kolom uitbreiden
In deze stap breidt u de samengevoegde kolom uit met de naam NieuweKolom om twee nieuwe kolommen te maken in de query Producten : Jaar en Totale verkoop.
Selecteer in Voorbeeld van gegevensde optie Pictogram Uitvouwen (
) naast NieuweKolom.In de vervolgkeuzelijst Uitvouwen :
- Selecteer (Alle kolommen selecteren) om alle kolommen te wissen.
- Selecteer Jaar en totale verkoop.
- Selecteer OK.
Wijzig deze twee kolomnamen in Jaar en Totale verkoop.
Als u wilt weten welke producten en in welke jaren de producten het meeste zijn verkocht, selecteert u Aflopend sorteren op totale verkoop.
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, zodat u een rapport kunt maken dat is gekoppeld aan het queryresultaat. Nadat u gegevens in het Excel-gegevensmodel hebt geladen, kunt u Power Pivot gebruiken om de gegevensanalyse voort te zetten.
- Selecteer Start,>sluiten & laden.
- Selecteer in het dialoogvenster Gegevens importeren de optie Deze gegevens aan het gegevensmodel toevoegen. Selecteer het vraagteken (?) voor meer informatie over het gebruik van dit dialoogvenster.
Resultaat
U hebt een query Totale verkoop per product waarin gegevens uit het bestand Products.xlsx en de OData-feed Northwind worden gecombineerd. Deze query wordt toegepast op een Power Pivot-model. Daarnaast wordt de resulterende tabel in het gegevensmodel gewijzigd en vernieuwd met wijzigingen in de query.
Overzicht: Power Query-stappen die in taak 3 zijn gemaakt
Wanneer u queryactiviteiten samenvoegen uitvoert in Power Query, worden er 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 Power Query documentatie 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"}) |
| De naam van twee kolommen wijzigen | Hernoemde kolommen | = Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}}) |
| Totale verkoop sorteren in oplopende volgorde | Gesorteerde rijen | = Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}}) |