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, Ex...
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"

     

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.