Forum Discussion
Helper IIDAX: Delete empty columns after filtering
Hello,
I have a table in my report similar to this:
ID | Zipcode | Red | Blue | Pink | Magneta |
Id1 | 20T00 | Blue | |||
Id2 | 20T00 | Red | Pink | ||
Id3 | 50R30 | Pink | Magneta | ||
Id4 | 50Q50 | Red | |||
Id5 | 50Q50 | Blue |
After applying a filter on “Zipcode”,
Filter (Zipcode) = 50R30
I would like to have my table displayed as below without empty columns (Red and Blue):
ID | Zipcode | Pink | Magneta |
Id3 | 50R30 | Pink | Magneta |
Could you pls help?
Thank you.
You have to modify your input table to get what you want.
Input:
You should transform the input table into this.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRMjIIMTAA0kDklFOaCmEpxeqA5I2Q5INSUyByAZl52QglxkCmqUGQMdQIhLxvYnpeakkiVJkJWFmgMYpJCFNMkaTRHBILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Zip = _t, Red = _t, Blue = _t, Pink = _t, Magneta = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Zip", type text}, {"Red", type text}, {"Blue", type text}, {"Pink", type text}, {"Magneta", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Zip"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Color"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Value] <> "")) in #"Filtered Rows"Follow the Unpivot and filtered steps.
Then create a matrix with stepped layout off as below
When you filter the zip, you get the expected output.
The idea is to keep the color columns as values to not show the column altogether.
If it helps, mark it as a solution
Kudos are nice too
2 Replies
- VasTg
Memorable Member
You have to modify your input table to get what you want.
Input:
You should transform the input table into this.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRMjIIMTAA0kDklFOaCmEpxeqA5I2Q5INSUyByAZl52QglxkCmqUGQMdQIhLxvYnpeakkiVJkJWFmgMYpJCFNMkaTRHBILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Zip = _t, Red = _t, Blue = _t, Pink = _t, Magneta = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Zip", type text}, {"Red", type text}, {"Blue", type text}, {"Pink", type text}, {"Magneta", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Zip"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Color"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Value] <> "")) in #"Filtered Rows"Follow the Unpivot and filtered steps.
Then create a matrix with stepped layout off as below
When you filter the zip, you get the expected output.
The idea is to keep the color columns as values to not show the column altogether.
If it helps, mark it as a solution
Kudos are nice too
MerleauThank you VasTg
This appears to solve my problem.
Althought it requires me to change my input in Power Query first, this is great.
Sorry for my late response.
I appreciate the detailed response.
Is there any way to achieve this using "Table"?
Matrix works perfectly but the end result will be exported to Excel for further analysis and formatting. Unfortunately Power Bi does not support exporting matrix "as it is" at the moment. ... Therefore, I am still looking for a way around because we need to filter the matrix for the all the 25 values in zipcode and export to Excel.
Thanks again.