Doorgaan naar hoofdcontent

Excel: Dynamische grafiek op basis van een geparametriseerde Power Query tabel

Dynamische grafieken in Excel

Grafieken in Excel zijn dynamisch te maken met allerlei complexe formules (bijv. index, vergelijken en verschuiving). Makkelijker is het de grafiek te baseren op een Excel tabel (te maken via Invoegen => Tabel).

Nog leuker wordt het als een dergelijke tabel het resultaat is van een Power Query die we met behulp van keuzelijsten kunnen aansturen. Hieronder een voorbeeld.

De tabel met parameters

Deze heb ik linksboven geplaatst. De naam van deze tabel is Parameters (via Formules => Namen beheren). Deze tabel wordt in Power Query gebruikt voor het oppikken van variabelen uit Excel, in dit geval DatumBegin en DatumEind. Deze variabelen worden via Gegevens => Gegevensvalidatie begrensd door de datum uit de eerste kolom van het blad Data.

De gebruikte gegevens

Deze staan op het blad Data en lopen van 1-1-2020 tot en met 20-8-2020. In de tweede kolom staan willekeurige bedragen. Deze gegevens zijn via From Table / Range ingeladen in Power Query

De grafiek

De grafiek linksonder is gebaseerd op de twee kolommen van de tabel rechts. Deze tabel is geladen uit Power Query waar de gegevens uit het blad Data op basis van de variabelen voorbewerkt zijn. 

Power Query

In Power Query heb ik een functie fnGetParameter gemaakt die ervoor zorgt dat de parameters uit Excel gebruikt kunnen worden in een query.

De code is als volgt:

(ParameterName as text) =>
let
    //in the Excel file there has to be a parameter table named Parameters
    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},"Value")
in
    Value

Verder is er in Power Query een query met de naam Data, gebaseerd op de gegevens uit het blad Data en gekoppeld aan de variabelen uit Excel. De code is als volgt:

let
    strDatumBegin =fnGetParameter("DatumBegin"),
    strDatumEind =fnGetParameter("DatumEind"),
    intDagBegin = Date.Day(strDatumBegin),
    intJaarBegin = Date.Year(strDatumBegin),
    intMaandBegin = Date.Month(strDatumBegin),    
    intDagEind = Date.Day(strDatumEind),
    intJaarEind = Date.Year(strDatumEind),
    intMaandEind = Date.Month(strDatumEind),    
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"datum", type date}}),
    FilteredRows = Table.SelectRows(ChangedType, each [datum] >= #date(intJaarBegin, intMaandBegin, intDagBegin) 
    and [datum] <= #date(intJaarEind, intMaandEind, intDagEind))
in
    FilteredRows

Zodra de variabelen worden aangepast, levert deze query een andere dataset en laadt deze in Excel. De grafiek past zich dan vanzelf aan.

VBA

Steeds als de variabelen in Excel worden aangepast, moet de query in Power Query worden uitgevoerd. Om dit een zetje te geven heb ik achter ThisWorkbook het volgende VBA script toegevoegd. Het script checkt of er in de cellen C3 of C4 iets veranderd is. Zo ja, dan wordt de query ververst.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$3" Or Target.Address = "$C$4" Then
        ActiveWorkbook.RefreshAll
    End If
End Sub

Dynamische grafiek

In plaats van einddatum 20-1-2020 nu 31-1-2020 gekozen. En prompt krijgen we een andere grafiek.




Reacties