Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jopezzo
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

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
v-juanli-msft
Community Support
Community Support

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
Community Champion
Community Champion

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

 

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"

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors