Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Display multiple columns based on slicer selection

Hi all,   I have couple of challenges, I feel that the second one may not be possible in PBI based on my research but any alternatives would be appreciated.   1) Trying to slice the column based ...
  • Anonymous's avatar
    Anonymous
    7 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