Forum Discussion
Display multiple columns based on slicer selection
- Anonymous7 years ago
I would solve this in the query editor. Your starting data is in a wide format which makes it far harder to work with.
I have done these transformations in the below M Code.
In my example the ABC humans and ABC bot are the other way around, as i think there is a typo in one of your original tables.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiMUtJRcnEEEoZGxkDS1AhImFkCCQWCOFYnWsnRyRnI9vXHo9DIyBREGpqYgG0xRTUjFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, AUR = _t, #"ABC B DA" = _t, #"ABC H DA" = _t, #"ABC T DA" = _t, #"FB H DA" = _t, #"FB B DA" = _t, #"FB T DA" = _t, #"ABC H MO" = _t, #"ABC B MO" = _t, #"ABC T MO" = _t, #"FB H MO" = _t, #"FB B MAO" = _t, #"FB T MO" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer", "AUR"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute", type text}, {"Attribute.2", type text}, {"Value", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Attribute.2] = [AUR])),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.2"})
in
#"Removed Columns"In order to run this create a blank query then open it in the advanced editor and paste the code in.
To visualize this in power bi create a matrix visual with customer on the rows, attribute on columns and value on the values
Hopefully this answers your question
I would solve this in the query editor. Your starting data is in a wide format which makes it far harder to work with.
I have done these transformations in the below M Code.
In my example the ABC humans and ABC bot are the other way around, as i think there is a typo in one of your original tables.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiMUtJRcnEEEoZGxkDS1AhImFkCCQWCOFYnWsnRyRnI9vXHo9DIyBREGpqYgG0xRTUjFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, AUR = _t, #"ABC B DA" = _t, #"ABC H DA" = _t, #"ABC T DA" = _t, #"FB H DA" = _t, #"FB B DA" = _t, #"FB T DA" = _t, #"ABC H MO" = _t, #"ABC B MO" = _t, #"ABC T MO" = _t, #"FB H MO" = _t, #"FB B MAO" = _t, #"FB T MO" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer", "AUR"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute", type text}, {"Attribute.2", type text}, {"Value", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Attribute.2] = [AUR])),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.2"})
in
#"Removed Columns"
In order to run this create a blank query then open it in the advanced editor and paste the code in.
To visualize this in power bi create a matrix visual with customer on the rows, attribute on columns and value on the values
Hopefully this answers your question