Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to 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"
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.
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"
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
66 | |
27 | |
18 | |
12 |