How to filter a query based on the value of another query (with the possibility to show all items)

[jopezzo]

Hi All!

I am trying to create a model as follows, in Excel:

I have a table called tData, with 2 columns: Supplier and Value:

I have a list of all the suppliers + one row which is "ALL". 

I am using this list to create a drop-down menu to select a supplier or the string "ALL".

This table is called tSupplier.

The table tData is loaded. The table tSupplier is loaded as well. I do a drill-down on the Supplier to only have the value from the drop-down menu in the table tSupplier.

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

This gives me a query supplier, where there is only one value (the one selected with the drop-down menu in Excel).

Here is my query:

It is the last step that I would like to change. I would like that if the selected value in the dropdown menu is "ALL", I get all the suppliers (basically, no filter applied), otherwise, I I would like the filter as in the above screenshot.

I have uloaded a data sample here.

Any idea how to solve that?

[v-juanli-msft]

Hi @jopezzo 

The pbix link is not valid.

If Mariusz's answer sloves your problem, could you kindly accept it as a solution?

If not, feel free to let me know.

Best Regards

Maggie

[Mariusz]

Hi @jopezzo 

Try adjusting the step to something like.

= Table.SelectRows(#"Changed Type", each ( if [Supplier] = Supplier then [Supplier] = Supplier else true ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[jopezzo · Accepted solution]

Hi Mariusz,

The code you proposed did not work exactly as I wanted. With the "true" argument in the end, I always had all items (nothing filtered). With the "false" argument, I had the selected value filtered, but when I selected "ALL", the table was empty.

However, you gave me the idea how to solve it.

I created a conditional column to test whether the supplier from the drop down corresponded to the supplier in the query. If not, "ALL". Then I just had to create a filter with [Supplier] = Supplier. 

let
    Source = Excel.CurrentWorkbook(){[Name="tData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Supplier", type text}, {"Value", Int64.Type}}),
    #"Added SupplierFilter" = Table.AddColumn(#"Changed Type", "SupplierFilter", each if [Supplier] = Supplier then [Supplier] else "ALL",type text),
    #"Filtered [SupplierFilter] = Supplier" = Table.SelectRows(#"Added SupplierFilter", each ([SupplierFilter] = Supplier)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered [SupplierFilter] = Supplier",{"Supplier", "Value"})
in
    #"Removed Other Columns"