Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Data Table Dynamic

Hi Team,

 

I have data like below and I need Slicer in same data (see Slicer).

Raw data and Scenario 1 is PowerBI expected and then while select Slicer i need Scenario 2.

 

Raw Data,



Expected Chart:



while select Slicer data , I need below scenario:

 

Thanks,

KVs

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    Add an index column unpivot your data except the index in the query editor and you will get tow column -Attribute and Value.

     

    Now use your attribute on a matrix and index and a slicer place index on the rows of the matrix and hide the column result attach and code for unpivot attach..

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvV0UdJRMjTScwx3BTNMgWSmuQWQDDVSitWJVgp3DQJJmITrpRaVgFkQJWYgJYZgJYFQvTBDTMD6M03BSkwgpgRHgGX8oaYAVRqC1FgagNQYg9UEu8CMCfUEKQmOMEI4BmhTLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t, Col5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}, {"Col3", type text}, {"Col4", type text}, {"Col5", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

     

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    Add an index column unpivot your data except the index in the query editor and you will get tow column -Attribute and Value.

     

    Now use your attribute on a matrix and index and a slicer place index on the rows of the matrix and hide the column result attach and code for unpivot attach..

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvV0UdJRMjTScwx3BTNMgWSmuQWQDDVSitWJVgp3DQJJmITrpRaVgFkQJWYgJYZgJYFQvTBDTMD6M03BSkwgpgRHgGX8oaYAVRqC1FgagNQYg9UEu8CMCfUEKQmOMEI4BmhTLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t, Col5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}, {"Col3", type text}, {"Col4", type text}, {"Col5", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi MFelix ,

       

      Thanks for Unpivot query however I need scenario 2 answer,I have selected only two columns and table result shows same column result.

       

      Can you help me .

       

       

       

      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        Hi Anonymous ,

         

        Have you followed the explanations about the way you need to unpivot and setup a matrix visualization? Have you tried the attach PBIX file I have send out.

         

        As you can see on the image below when I select a column the table changes accordingly:

         

        Just to rephrase the steps are:

        • Add Index column
        • Select all columns unless Index columns
        • Unpivot
        • Matrix visualization:
          • Rows: Index
          • Columns: Attribute
          • Values: Values
          • Turn off Wrap text
          • Turn off totals
          • Reduce size of index column until is not visible
        • Slicer: Attribute

        Any questions please tell me.