cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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:

 

tSupplier.jpg

 

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

 

Suppliers.jpg


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

 

Dropdown.jpg

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:

 

Query.jpg

 

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: How to filter a query based on the value of another query (with the possibility to show all item

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"

View solution in original post

3 REPLIES 3
Highlighted
Super User V
Super User V

Re: How to filter a query based on the value of another query (with the possibility to show all item

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

 

Highlighted
Community Support
Community Support

Re: How to filter a query based on the value of another query (with the possibility to show all item

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
Highlighted
Helper I
Helper I

Re: How to filter a query based on the value of another query (with the possibility to show all item

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"

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors