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("
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