cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YasminYas Regular Visitor
Regular Visitor

Pivot help

Hi All,
I have sample data 
I want to convert a column into rows with Different row headers

Have Sample data with Name, Date, Update, Arriaval, Fault, Group, material and Close column

I want to show one row for each Name with Group number1,Material1,close1 , Group number2,Material2,close2..etc

.Attached Sample data file below
https://www.dropbox.com/s/rki3lhmh6augvqk/Sample%20data.xls?dl=0   Thanks!

N1.PNGN2.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Pivot help

HI @YasminYas,

 

Maybe you can refer to following steps to do unpivot table and create a matrix visual to display similar result.


Steps:

1. Use custom steps to transform table data structure.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpKzAKSjkFAwlzf0EjfyMDQHMj2zEvJLMtMKU3MAXL884CES2JmTiWMgyntn5aGpMjdEFkHUCpWh6bWGdHXOmP6WmdCX+tM6WudGX2tM6evdRb0tc6SvtYZGtDZPjqXK4bYChYnINsvMxdIOvl5+kJsNIXZGO7q6u0TCVLt5oZuQz7Y8PLU1GyweRBuPsjaTGRl4AyIMMePPpaaDYSl5gNhqcVAWGqJbmksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FirstName = _t, LastName = _t, ScreenName = _t, CreateDate = _t, ActivityUpdates = _t, ActivityU = _t, WEEK = _t, UPDATES = _t, NEWARRAIVAL = _t, #"ON/OFF_" = _t, #"ON/OFF" = _t, MPFCommunityDigest = _t, GroupName = _t, ANS1 = _t, Ans2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstName", type text}, {"LastName", type text}, {"ScreenName", type text}, {"CreateDate", type date}, {"ActivityUpdates", type text}, {"ActivityU", type text}, {"WEEK", type text}, {"UPDATES", type text}, {"NEWARRAIVAL", type text}, {"ON/OFF_", type text}, {"ON/OFF", type text}, {"MPFCommunityDigest", type text}, {"GroupName", type text}, {"ANS1", type text}, {"Ans2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"FirstName", "LastName", "ScreenName", "CreateDate", "ActivityUpdates", "ActivityU", "WEEK", "UPDATES", "NEWARRAIVAL", "ON/OFF_", "ON/OFF", "MPFCommunityDigest"}, {{"Contents", each Table.UnpivotOtherColumns(Table.AddIndexColumn(Table.SelectColumns(_,{"Ans2", "ANS1", "GroupName"}), "Index", 1, 1), {"Index"}, "Attribute", "Value"), type table}}),
    #"Expanded Contents" = Table.ExpandTableColumn(#"Grouped Rows", "Contents", {"Index", "Attribute", "Value"}, {"Index", "Attribute", "Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Contents", "Attribute Index", each [Attribute]&"_"&Text.From([Index]))
in
    #"Added Custom"

2. Create a custom sort order table to do custom on column fields:

Table = DISTINCT(ALL(T4[Attribute Index],T4[Index]))

5.png

 

3. Create matrix visual with raw table records and sort order table fields.

6.png

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
3 REPLIES 3
Community Support Team
Community Support Team

Re: Pivot help

HI @YasminYas,

 

Maybe you can refer to following steps to do unpivot table and create a matrix visual to display similar result.


Steps:

1. Use custom steps to transform table data structure.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpKzAKSjkFAwlzf0EjfyMDQHMj2zEvJLMtMKU3MAXL884CES2JmTiWMgyntn5aGpMjdEFkHUCpWh6bWGdHXOmP6WmdCX+tM6WudGX2tM6evdRb0tc6SvtYZGtDZPjqXK4bYChYnINsvMxdIOvl5+kJsNIXZGO7q6u0TCVLt5oZuQz7Y8PLU1GyweRBuPsjaTGRl4AyIMMePPpaaDYSl5gNhqcVAWGqJbmksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FirstName = _t, LastName = _t, ScreenName = _t, CreateDate = _t, ActivityUpdates = _t, ActivityU = _t, WEEK = _t, UPDATES = _t, NEWARRAIVAL = _t, #"ON/OFF_" = _t, #"ON/OFF" = _t, MPFCommunityDigest = _t, GroupName = _t, ANS1 = _t, Ans2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstName", type text}, {"LastName", type text}, {"ScreenName", type text}, {"CreateDate", type date}, {"ActivityUpdates", type text}, {"ActivityU", type text}, {"WEEK", type text}, {"UPDATES", type text}, {"NEWARRAIVAL", type text}, {"ON/OFF_", type text}, {"ON/OFF", type text}, {"MPFCommunityDigest", type text}, {"GroupName", type text}, {"ANS1", type text}, {"Ans2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"FirstName", "LastName", "ScreenName", "CreateDate", "ActivityUpdates", "ActivityU", "WEEK", "UPDATES", "NEWARRAIVAL", "ON/OFF_", "ON/OFF", "MPFCommunityDigest"}, {{"Contents", each Table.UnpivotOtherColumns(Table.AddIndexColumn(Table.SelectColumns(_,{"Ans2", "ANS1", "GroupName"}), "Index", 1, 1), {"Index"}, "Attribute", "Value"), type table}}),
    #"Expanded Contents" = Table.ExpandTableColumn(#"Grouped Rows", "Contents", {"Index", "Attribute", "Value"}, {"Index", "Attribute", "Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Contents", "Attribute Index", each [Attribute]&"_"&Text.From([Index]))
in
    #"Added Custom"

2. Create a custom sort order table to do custom on column fields:

Table = DISTINCT(ALL(T4[Attribute Index],T4[Index]))

5.png

 

3. Create matrix visual with raw table records and sort order table fields.

6.png

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Highlighted
YasminYas Regular Visitor
Regular Visitor

Re: Pivot help

Hi
Can share me the PBIX

Community Support Team
Community Support Team

Re: Pivot help

Hi @YasminYas ,

 

I attached sample file at below.

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |