I have a Power Query query that uses a secondary function query that pulls parameters from a table in my workbook. For example, to filter by date range, I use

#"Filtered Rows" = Table.SelectRows(#"Added Custom", each [EventDatetime] >= fnGetParameterValue("EventStartDate") and [EventDatetime] <= fnGetParameterValue("EventEndDate"))

What I'd like to do is filter only if a value returned by fnGetParameterValue("AccountNumber") is not an empty string.

I tried inserting this step after the date filter step:

#"Filtered Rows1" = if fnGetParameterValue("AccountNumber") = "" then #"Filtered Rows" else Table.SelectRows(#"Filtered Rows", each [AccountNumber] = fnGetParameterValue("AccountNumber"))

I don't appear to have a syntax error, but I'm not getting any data back.

How can I conditionally filter data?


I would add an intermediate Step to Add a Column using your function (e.g. [AccountNumber Filter] ) and using your if logic, along the lines of:

if fnGetParameterValue("AccountNumber") = "" then [AccountNumber] else fnGetParameterValue("AccountNumber")

Then I would use that new column in a the Filter step, e.g.

[AccountNumber] = [AccountNumber Filter]

The intermediate step will also be clearer for review/testing etc.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.