Doorgaan naar hoofdcontent

Excel: parametriseren en linken van datasets in PowerQuery

PowerQuery

PowerQuery is het ETL instrument dat binnen Excel en PowerBi gebruikt wordt om verschillende datasets binnen te halen, aan te passen en in Excel of PowerBi in te laden. Heel veel acties kunnen menugestuurd afgehandeld worden, maar af en toe moet er het een en ander worden aangepast in M, de taal die PowerQuery gebruikt.

Voorbeeld

In het onderstaande voorbeeld laat ik zien hoe we een dataset in PowerQuery met een parameter vanuit Excel kunnen filteren. In het voorbeeld heb ik gebruikt gemaakt van de tabellen Klanten en Orders uit de database Noordenwind van Microsoft. Die staan op respectievelijk blad 1 en 2. Van beide datasets heb ik een Excel tabel gemaakt met de namen Klanten en Orders.

Op een derde blad heb ik een filter gemaakt dat uit de klantenlijst kan kiezen. Vervolgens wordt de bij de betreffende klanten horende lijst van orders getoond.

Blad met selectie

Bovenaan van A1:B3 heb ik een blokje variabelen gezet:

Parameter Waarde
Klantnaam Ernst Handel
Datum 2-6-1998
Van dit blokje heb ik een Excel tabel gemaakt met de naam Parameters. Vervolgens ga ik naar de editor van Power Query. Daar maak ik een lege query met de naam fncParameter. Hierin plak ik de volgende code:

(ParameterName as text) =>
let
    ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
    Value=
    if Table.IsEmpty(ParamRow)=true
        then null
    else Record.Field(ParamRow{0},"Waarde")
in
    Value

In deze code heb ik met rood de relatie aangegeven met de tabel met parameters uit Excel. Na de code bewaard te hebben ga ik terug naar Excel. 

Klanten

Staande in de tabel Klanten kies ik de optie POWER QUERY => Uit tabel / bereik. Deze dataset noem ik qryKlanten. Op de kolom Bedrijf zet ik een filter op een willekeurige klant. In M (via de geavanceerde editor) ziet de code er dan zo uit:

let
    Bron = Excel.CurrentWorkbook(){[Name="Klanten"]}[Content],
    Typegewijzigd = Table.TransformColumnTypes(Bron,{{"Klantnummer", type text}, {"Bedrijf", type text}, {"Contactpersoon", type text}, {"Functie", type text}, {"Adres", type text}, {"Plaats", type text}, {"Provincie", type text}, {"Postcode", type text}, {"Land", type text}, {"Telefoonnummer", type text}, {"Faxnummer", type text}}),
    Rijengefilterd = Table.SelectRows(Typegewijzigd, each ([Bedrijf] = "Alfreds Futterkiste"))
in
    Rijengefilterd

In deze code breng ik vervolgens handmatig twee wijzigingen aan:

let
    strKlantnaam = fncParameter("Klantnaam"),
    Bron = Excel.CurrentWorkbook(){[Name="Klanten"]}[Content],
    Typegewijzigd = Table.TransformColumnTypes(Bron,{{"Klantnummer", type text}, {"Bedrijf", type text}, {"Contactpersoon", type text}, {"Functie", type text}, {"Adres", type text}, {"Plaats", type text}, {"Provincie", type text}, {"Postcode", type text}, {"Land", type text}, {"Telefoonnummer", type text}, {"Faxnummer", type text}}),
    Rijengefilterd = Table.SelectRows(Typegewijzigd, each ([Bedrijf] = strKlantnaam ))
in
    Rijengefilterd

De functie fncParameter haalt uit de tabel Parameters de juiste waarde op. Na de code bewaard te hebben ga ik terug naar Excel.

Orders

Staande in de tabel Orders kies ik de optie POWER QUERY => Uit tabel / bereik. Deze dataset noem ik qryOrders. 

Koppelen qryKlanten en qryOrders

In Power Query is het niet mogelijk de geparametriseerde dataset qryKlanten direct te koppelen aan qryOrders. We krijgen dan een foutmelding:

Formula.Firewall: Query qryKlanten (stap Query's samengevoegd) verwijst naar andere query's of stappen en heeft daardoor mogelijk niet direct toegang tot een gegevensbron. Maak deze gegevenscombinatie opnieuw.

We hebben daar een tussenstap voor nodig. Die tussenstap bestaat uit het maken van een nieuwe lege query. Daarin zetten we via de geavanceerde editor de code:

let
    Bron = qryKlanten
in
    Bron

Dit slaan we op als qryKoppeling. Van daaruit koppelen we via Query's samenvoegen aan qryOrders.


Vervolgens verwijderen we nog wat kolommen uit de gekoppelde dataset. Uiteindelijk houden we dan in M de volgende code over:

let
    Bron = qryKlanten,
    Querysamengevoegd = Table.NestedJoin(Bron,{"Klantnummer"},qryOrders,{"Klant"},"qryOrders",JoinKind.LeftOuter),
    qryOrdersuitgevouwen = Table.ExpandTableColumn(Querysamengevoegd, "qryOrders", {"Order-id", "Klant", "Werknemer", "Orderdatum", "Vervaldatum", "Leverdatum", "Verzendwijze", "Vrachtkosten", "Verzendnaam", "Verzendadres", "Verzendplaats", "Verzendprovincie", "Verzendpostcode", "Verzendland"}, {"qryOrders.Order-id", "qryOrders.Klant", "qryOrders.Werknemer", "qryOrders.Orderdatum", "qryOrders.Vervaldatum", "qryOrders.Leverdatum", "qryOrders.Verzendwijze", "qryOrders.Vrachtkosten", "qryOrders.Verzendnaam", "qryOrders.Verzendadres", "qryOrders.Verzendplaats", "qryOrders.Verzendprovincie", "qryOrders.Verzendpostcode", "qryOrders.Verzendland"}),
    Kolommenverwijderd = Table.RemoveColumns(qryOrdersuitgevouwen,{"Functie", "Adres", "Plaats", "Provincie", "Postcode", "Land", "Telefoonnummer", "Faxnummer", "qryOrders.Klant", "Klantnummer", "Bedrijf", "Contactpersoon"}),
    Namenvankolommengewijzigd = Table.RenameColumns(Kolommenverwijderd,{{"qryOrders.Werknemer", "Werknemer"}, {"qryOrders.Order-id", "Order-id"}, {"qryOrders.Orderdatum", "Orderdatum"}, {"qryOrders.Vervaldatum", "Vervaldatum"}, {"qryOrders.Leverdatum", "Leverdatum"}, {"qryOrders.Verzendwijze", "Verzendwijze"}, {"qryOrders.Vrachtkosten", "Vrachtkosten"}, {"qryOrders.Verzendnaam", "Verzendnaam"}}),
    Kolommenverwijderd1 = Table.RemoveColumns(Namenvankolommengewijzigd,{"Verzendnaam", "qryOrders.Verzendadres", "qryOrders.Verzendplaats", "qryOrders.Verzendprovincie", "qryOrders.Verzendpostcode", "qryOrders.Verzendland", "Werknemer"})
in
    Kolommenverwijderd1

Het resultaat laden we naar het blad Selectie in Excel. Het een en ander ziet er zo uit:


Het enige wat we dan nog nodig hebben is wat VBA om de koppeling te vernieuwen als we een andere klant kiezen.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Condition on Target.Address is needed to avoid a circular reaction
    If Target.Address = "$B$2" Then
         'ActiveWorkbook.RefreshAll
         ActiveWorkbook.Connections("query - qryKoppeling").Refresh
    End If
End Sub

Orders filteren met een datum

Het filteren op een datum is iets ingewikkelder in Power Query. We geven hier alleen de M code:

let
    strDatum =Date.From(fncParameter("Datum")),
let
    Bron = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Typegewijzigd = Table.TransformColumnTypes(Bron,{{"Order-id", Int64.Type}, {"Klant", type text}, {"Werknemer", Int64.Type}, {"Orderdatum", type datetime}, {"Vervaldatum", type datetime}, {"Leverdatum", type datetime}, {"Verzendwijze", Int64.Type}, {"Vrachtkosten", type number}, {"Verzendnaam", type text}, {"Verzendadres", type text}, {"Verzendplaats", type text}, {"Verzendprovincie", type text}, {"Verzendpostcode", type text}, {"Verzendland", type text}}),
    Typegewijzigd = Table.TransformColumnTypes(Typegewijzigd,{{"Orderdatum", type date}}),
    Rijengefilterd = Table.SelectRows(Typegewijzigd, each ([Orderdatum] = strDatum))
in
    Rijengefilterd

We hebben - zoals in rood aangegeven - twee extra stappen nodig. We gebruiken de M functie Date.From nodig om de datum uit de tabel Parameters te halen. Bovendien moeten we het gegevenstype van de datum waarop we willen filteren veranderen van datum tijd naar datum.

Download

Het bestand  VoorbeeldPowerQuery.xlsm is te downloaden via https://drive.google.com/drive/folders/0B7HgkOwFZtdZVmhRQUZFM28yc1U

Andere blogs over Excel

Voor het beste overzicht verwijs ik naar een pagina van mijn website: http://www.walmar.nl/spreadsheets.asp

Reacties

Populaire posts van deze blog

Excel: VBA script om wachtwoord te verwijderen

Af en toe krijg ik een vraag om een wachtwoord van een Excel blad te halen. Doodsimpel met VBA. Hier een script dat ik gebruik:

Sub WachtwoordCrack()
    Dim a As Integer, b As Integer, c As Integer, d As Integer, _
    e As Integer, f As Integer, g As Integer, h As Integer, _ 
I As Integer, j As Integer, k, m As Integer
    Dim begin As Date, eind As Date
    Dim duur As String
    Dim objSheet As Worksheet
    begin = TimeValue(Time)
    On Error Resume Next
    For Each objSheet In Application.Worksheets
        For a = 65 To 66: For b = 65 To 66: For c = 65 To 66
            For d = 65 To 66: For e = 65 To 66: For f = 65 To 66
                For g = 65 To 66: For h = 65 To 66: For I = 65 To 66
                    For j = 65 To 66: For k = 65 To 66: For m = 32 To 126
                        ActiveSheet.Unprotect Chr(a) & Chr(b) & _ 
 Chr(c) & Chr(d) & Chr(e) & Chr(f) & _ 
 Chr(g) & Chr(h) & Chr(I) & Chr(j) & Chr(k) & Chr(m)
                        If Ac…

Excel 2013: uniek aantal in draaitabel

Tot en met versie 2010 was het in Excel lastig om in een draaitabel een uniek aantal (DISTINCT COUNT) te tellen. We geven een voorbeeld op basis van een verkoperslijst. In deze lijst kunnen we zien welke verkopers welke artikelen hebben verkocht. Willen we nu in een draaitabel laten zien hoeveel artikelen een verkoper heeft verkocht, dan krijgen we wel de aantallen maar niet de unieke aantallen te zien.


Om toch de unieke aantallen te laten zien, hebben we een aantal stappen nodig.

Op het moment dat we de draaitabel invoegen, krijgen we in Excel 2013 dit dialoogvenster:


Onderaan zien we daar een nieuwe optie: Deze gegevens toevoegen aan het gegevensmodel. Deze optie moeten we aanvinken, voor we op OK klikken.

We krijgen dan een iets ander beeld dan normaal:


Normaliter krijgen we alleen de veldnamen. Nu zien we er het woord Bereik boven staan.

Voor het voorbeeld heb ik nu Verkoper toegevoegd aan Rijen en Artikelomschrijving aan Waarden.

Het resultaat is identiek aan het bovenste plaatje…

Excel: Speedometer met handleiding

Inleiding Echte speedometers met wijzertjes en zo kent het reguliere Excel niet. We kunnen wel zelf zoiets maken. En dat gaan we doen.
Hoe maken we zoiets?§We typen vanaf cel A1:

cijfers 1 1 1 1 1 1 1 1 1 1 10
De onderste 10 is de optelsom van de bovenste waarden.
§We maken hier een ringgrafiek van. §We wissen de labels. §We laten deze ring 270 graden laten draaien.


§Voor het onderste stuk kiezen we dan: Geen opvulling.