Forum Discussion

derekli1700's avatar
derekli1700
Icon for Helper III rankHelper III
1 year ago
Solved

How to make slicer for matrix table

Data 

Hi Guys,

 

Any ideas how to make a slicer visual for PRODUCT 1/2/3/4 for this matrix table? Would appreciate someone attaching a sample file because i cannot seem to figure this out for the life of me 

 

Need Help
Tutorial Requests
  • danextian's avatar
    danextian
    1 year ago

    Hi derekli1700 

     

    In the query editor, select the first four column, right click and select unpivot other columns

    Filter out empty rows from the value column, set the correct data type and apply whatever transformations necessary

    Here's a sample M code to be pasted into a blank query (remove everything before  pasting)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZVNaxsxEED/SvE5FZoZjUZzTFt6CKQ95BhycIPpJTTBJIX++65GWmmtlcHMyrDP86En+fHx8PD+ej59uj3cHO6Ofz5jWBag4CItCwRosX6ebrbI99OvFUnOa36ZfYtT5P547gjGZUHAGVEu76PfQ7dv5yEPcWhxmufuo3WDLuZFFG3xCvKyIt5pblpRlphSzRJoUtrH7wolcQlzhpA7Yap5wMc99XB6q5QkZ52TYovT6n4+v6+IOHstSq6OY501gOypH69/O2UlBZ+7oFjLo0lP307PK8QO07KQFuaTy+rwCoh1DS1eV+cCwaAt7pAvF4JKFTQEaHGKdEETupA3IqhF7llIBqgrKuTqjHPrvIF0YJqh6L3LiJjbUaUhODL3x3+bfmzCZBGoQzwOoXmd1NnmKeSykvSjEMIOWs2W6OzXNfkWp5PrXqu4aHvJBsbeURont9GayzkFhGRuxr5HnAZsozY6ywKoqT+mBXaz03InmHMkXM7BpsRx6N3tpS+72YDt2AJtZMUEM/ns55UdxHJ07Xucn4umH9fNMhWQIvfHDvp6obkGR4UWG0q5WWqFmAauuY6eXSgcW2eMXVwgHLiu+zJIkwOYbDD16iqbxgO2MZ7KxQdct5z6/EeqOZ+LZCsylAfCpsixt2Y9elkxSiO2g176HxpUNTKk4q8W2Lxf/vacXZLgbeeUtome/gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, Period = _t, #"Averaged Value" = _t, #"Vol (L)" = _t, #"Product 1" = _t, #"Product 2" = _t, #"Product 3" = _t, #"Product 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", type text}, {"Period", type date}, {"Averaged Value", type number}, {"Vol (L)", Int64.Type}, {"Product 1", Int64.Type}, {"Product 2", type text}, {"Product 3", Int64.Type}, {"Product 4", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Store", "Period", "Averaged Value", "Vol (L)"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] <> null and [Value] <> ""),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Value", type number}})
in
    #"Changed Type1"

4 Replies

    • derekli1700's avatar
      derekli1700
      Icon for Helper III rankHelper III
      1 year ago

      Hi, the slice would ideally drill down on the PRODUCT volume for each store. 
      E.g) This is what the matrix table looks like currently with the dataset

       

      When i click on PRODUCT 1 in the slicer - it would show the PRODUCT 1 volume composition in each site on the matrix table. Store A volume value for instance would change from 5282 to 4961 (as seen in the sheet below). If that makes sense

       

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi derekli1700 

     

    In the query editor, select the first four column, right click and select unpivot other columns

    Filter out empty rows from the value column, set the correct data type and apply whatever transformations necessary

    Here's a sample M code to be pasted into a blank query (remove everything before  pasting)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZVNaxsxEED/SvE5FZoZjUZzTFt6CKQ95BhycIPpJTTBJIX++65GWmmtlcHMyrDP86En+fHx8PD+ej59uj3cHO6Ofz5jWBag4CItCwRosX6ebrbI99OvFUnOa36ZfYtT5P547gjGZUHAGVEu76PfQ7dv5yEPcWhxmufuo3WDLuZFFG3xCvKyIt5pblpRlphSzRJoUtrH7wolcQlzhpA7Yap5wMc99XB6q5QkZ52TYovT6n4+v6+IOHstSq6OY501gOypH69/O2UlBZ+7oFjLo0lP307PK8QO07KQFuaTy+rwCoh1DS1eV+cCwaAt7pAvF4JKFTQEaHGKdEETupA3IqhF7llIBqgrKuTqjHPrvIF0YJqh6L3LiJjbUaUhODL3x3+bfmzCZBGoQzwOoXmd1NnmKeSykvSjEMIOWs2W6OzXNfkWp5PrXqu4aHvJBsbeURont9GayzkFhGRuxr5HnAZsozY6ywKoqT+mBXaz03InmHMkXM7BpsRx6N3tpS+72YDt2AJtZMUEM/ns55UdxHJ07Xucn4umH9fNMhWQIvfHDvp6obkGR4UWG0q5WWqFmAauuY6eXSgcW2eMXVwgHLiu+zJIkwOYbDD16iqbxgO2MZ7KxQdct5z6/EeqOZ+LZCsylAfCpsixt2Y9elkxSiO2g176HxpUNTKk4q8W2Lxf/vacXZLgbeeUtome/gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, Period = _t, #"Averaged Value" = _t, #"Vol (L)" = _t, #"Product 1" = _t, #"Product 2" = _t, #"Product 3" = _t, #"Product 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", type text}, {"Period", type date}, {"Averaged Value", type number}, {"Vol (L)", Int64.Type}, {"Product 1", Int64.Type}, {"Product 2", type text}, {"Product 3", Int64.Type}, {"Product 4", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Store", "Period", "Averaged Value", "Vol (L)"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] <> null and [Value] <> ""),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Value", type number}})
in
    #"Changed Type1"
  • v-karpurapud's avatar
    v-karpurapud
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi derekli1700 

    Could you please confirm if your query have been resolved the solution provided by danextian ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

    Thank you