Power Query Passing Variable From Cell - Show All If Blank

[Jeffrey Mahoney]

I created a Query to bring in data from a CSV file. Working well. I added another query called Trans_ID_Filter that simply uses a cell to pass a variable to the main query. It works well, except when I want to NOT filter on that criteria. Meaning, when the cell is blank, I want to select all the records. My plan is to create criteria for many of the fields

Can I add an IF statement to test if the Trans_ID_Filter value is null?

= Table.SelectRows(#"Changed Type", each ([TRANS_ID] = Trans_ID_Filter))

 

[alansidman]

Add an if then else statement that takes you back to a step before filtering

 

[Jeffrey Mahoney]

Add an if then else statement that takes you back to a step before filtering

Ok. I don't know enough to create or where to place that statement. Can you help please.

 

[alex78]

Hi @Jeffrey Mahoney,

try

= ((x)=> ((y)=> if Table.IsEmpty(y) then x else y) (Table.SelectRows(x, each [TRANS_ID] = Trans_ID_Filter)))(#"ChangedType")

Regards,

 

[Jeffrey Mahoney]

Thank you Alex. Other than the missing space in "Changed Type", it worked. I don't know why. The variables at the beginning is a format unfamiliar to me.

So, then I thought I could just copy that, change name of the field and paste it into each column. It appears that I may have to build a formula to handle the filters for the entire table.

Can you help with the next step in adding a filter for Source?

 

[alex78]

#" Changed Type" sholud be the step name containing the Table to be filtered. So, adapt accordingly.

 

[Jeffrey Mahoney]

Ok, I watched a video that really helped me follow a simple process of adding individual filters. Also using the statement Alex provided.

Add new individual queries as connections. Filter one column and change the reference in the formula from a static value to one of the new queries based on a cell value. Keep doing that for each of the fields.

I can now filter on multiple criteria. My next goal is to create a formula to filter on a date range for which I will create another thread.