Dynamic column filtering using Parameter table

[jafa1970]

Hi, I want to be able to control filtering of a dataset by using a parameter table to define which column to use ("Unit Type"), and which value to filter by ("Unit Name"). I have the following code which works, but is extremely slow. Can anyone suggest an alternative approach?

= Table.AddColumn(Source, "Unit Filter", each if fnGetParameter("Unit Type") = "COMPANY" then (if fnGetParameter("Unit Name") = [COMPANY_NAME] then 1 else 0)
else
if fnGetParameter("Unit Type") = "CENTRE" then (if fnGetParameter("Unit Name") = [CENTRE_NAME] then 1 else 0)
else
if fnGetParameter("Unit Type") = "SECTION" then (if fnGetParameter("Unit Name") = [SECTION_NAME] then 1 else 0)
else
0)

I then filter the [Unit Filter] field to keep the "1" values. I did try just having the result being TRUE / FALSE, rather than setting to 1 or 0, but that was just a slow.

Thanks in advance,
Phil

 

[alansidman]

I like to use this approach to a parameter query.

https://www.youtube.com/watch?v=gK2yBpiITvI

 

[jafa1970]

Thanks Alan, the fnGetParameter is doing a similar thing as it brings back a value from a table in Excel, just does not need a separate query to do so.

I might try putting the values in as variables at the top of the query the way the video showed though, and maybe that will be quicker if not going back to the table for each row, who knows. Will see how that goes.

 

[jafa1970]

To round this out, the solution was part way between the original approach noted in the original post and the approach in the video linked by Alan.

I set up a query to bring back the Unit Type and Unit Name (using the fnGetParameter from above) and then referenced the query result in the AddColumn formula instead of using the function call each time. Refresh times went back to normal for the dataset.