Need help with dynamic Filtered Row in Power Query

[asecgeek]

I have a worksheet with a pivot table displaying sales data. There is a named Named Range called SalespersonID. When I enter a Salesperon's ID and click a button, the Power Query refreshes, and the pivot table displays the data filtered data. I do this by having Filtered Row formula step in the Power Query read = Table.SelectRows(dbo_company_Quad_Report, each ([salesrep_id] = SalespersonID)) This works great.

1. I'd like to have a way to include the option to "show all", which can be done manually by a) removing the Filtered Row step. or b) by setting Filtered Row step to = Table.SelectRows(dbo_ashw_Quad_Report, each true) but I don't know how to achieve this from a button on the worksheet.
2. I know I could just leave out the "Filtered Row" step -- thus showing all rows -- and have users apply filters on the pivot table, but this is NOT what I want.

Any help is appreciated.

 

[asecgeek]

Correction: in both examples above, the source should read dbo_company_Quad_Report

 

[XL Pro]

You should be able to accomplish this by modifying the M-code to read as:
= Table.SelectRows(dbo_company_Quad_Report, each ([salesrep_id] = SalespersonID or SalespersonID = null ))

Then when no SalespersonID is supplied, SalespersonID = null, reverts to null = null which is a true statement

 

[asecgeek]

In the words of Kate Hepburn, "You are my knight in shining armor."

Many thanks.