Is there a way to use Excel VBA to update a filter in powerquery?

Question

So I have an ODBC dataset that I have filtered in PowerQuery before loading into Excel.

On one of the date columns I have put a filter:

Keeps rows where 'CREATION DATE' is after date X AND before date Y

The dataset is too big for Excel without constantly changing the filters. I would like to use VBA to automatically change the 'is after' and 'before' dates when reloading the query.

The idea is that the variable 'is after' and 'before' dates could be inserted into two cells in Excel. Lets say Sheet1.range("A1") and sheet1.range("A2"). The VBA script would insert these dates in the PowerQuery fields before reloading the query.

Would this be possible? And how?

Update: Screenshot of PowerQuery menu

No need for VBA you can refer to a range in Excel and use it in Power Query to filter whatever you want — Ricardo Diaz
I looked for such functionality but could not find it in the PowerQuery interface. Could you tell me where to look? — GreyR

Ricardo Diaz Ricardo Diaz · Accepted Answer · 2021-02-13T16:26:01

As mentioned in the comments, this doesn't require VBA you can leverage Excel Names and Power Query directly.

Steps:

Add cell names to from and to dates in Excel

Add the from date to a cell and add a name to that cell: fromDate
Add the to date to a cell and add a name to that cell: toDate

Add those cells to power query

Select the cell and click Data | From table/range

Change to date type

Drill down to the first cell (right click)

Use fromDate and toDate queries in the table date column filter

You can write the code or use the interface and adapt it: = Table.SelectRows(#"Changed Type", each ([Date] >= fromDate and [Date] <= toDate))

M code:

Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] >= fromDate and [Date] <= toDate))
in
    #"Filtered Rows"

fromDate

let
    Source = Excel.CurrentWorkbook(){[Name="fromDate"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    Column1 = #"Changed Type"{0}[Column1]
in
    Column1

toDate

let
    Source = Excel.CurrentWorkbook(){[Name="toDate"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    Column1 = #"Changed Type"{0}[Column1]
in
    Column1

Table1 Query

fromDate Query

Check the screencast

Let me know if it works

Is there a way to use Excel VBA to update a filter in powerquery?

1 Answers