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