Unpivot or Transpose Header in row level into Column also values

[ssk_1984]

Team help me to bring the below tables values into desired output, is there any way to pivot/unpivot/traspose the values.

Name	Suresh
Sales	10
Name	Raj
Sales	20
Name	Sunil
Sales	30

Inthe above table records are stored as one by one , actual requirement to transpose above values into 

Name	Sales
Suresh	10
Raj	20
Sunil	30

[jgeddes · Accepted solution]

One way to do this is to;

1) group by the first column choosing do not aggregate so you get all the rows

2) add an index column to the resulting nested tables

3) expand the nested tables

4) pivot the table on the first column, choosing not to aggregate the values in the second column

5) remove the index column

Here is an example code...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUQouLUotzlCK1YlWCk7MSS0GChkagLlQBUGJWSiyRiiywaV5mTko8sZA+VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"All", each Table.SelectColumns(_, {"Column2"}), type table [Column1=text, Column2=text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"All", each Table.AddIndexColumn(_, "index", 1, 1)}}),
    #"Expanded All" = Table.ExpandTableColumn(Custom1, "All", {"Column2", "index"}, {"Column2", "index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded All", List.Distinct(#"Expanded All"[Column1]), "Column1", "Column2"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"index"})
in
    #"Removed Columns"

[dufoq3 · Accepted solution]

Hi @ssk_1984, another solution:

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUQouLUotzlCK1YlWCk7MSS0GChkagLlQBUGJWSiyRiiywaV5mTko8sZA+VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    Transformed = Table.FromRows(List.Split(Source[Column2], 2), {"Name", "Sales"})
in
    Transformed

[slorin · Accepted solution]

Hi @ssk_1984 

another solution

let
Source = Your_Source,
Group = Table.Group(Source, {"Column1"}, {{"Data", each [Column2]}}),
Table = Table.FromColumns(Group[Data], Group[Column1])
in
Table

Stéphane 

[SundarRaj]

Hi @ssk_1984 , here's a quick way to solve your problem. I am attaching two images, first of the M code snippet used and second of the ouput. Thanks!

[ssk_1984]

Thank you for your quick revert, one more query rather than only two column...in my table has more than two column...i can add for each by col3, col4,col5 ...like that....Suggest me pls

[SundarRaj]

Do you mean something like this?

If that's the case, then this particular idea should work. Do match it with your idea. If this is not what you were looking for, do provide a sample set of your data and the output that you'd want. Thanks

[SundarRaj]

Could you be a little more specific with regards to your query? I didn't quite understand it correctly

[slorin · Accepted solution]

Hi @ssk_1984 

another solution

let
Source = Your_Source,
Group = Table.Group(Source, {"Column1"}, {{"Data", each [Column2]}}),
Table = Table.FromColumns(Group[Data], Group[Column1])
in
Table

Stéphane 

[dufoq3 · Accepted solution]

Hi @ssk_1984, another solution:

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUQouLUotzlCK1YlWCk7MSS0GChkagLlQBUGJWSiyRiiywaV5mTko8sZA+VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    Transformed = Table.FromRows(List.Split(Source[Column2], 2), {"Name", "Sales"})
in
    Transformed

[ssk_1984]

Hi Dufo,

i have one more query on the same request,

Now my data set /column increased earlier it depends on two column, now i have 4 different column.

values to be shown like the below samples...help me out how to convert these values in pivot/unpivot

Now	column and data present in the data set
WeekDate	ProcessMapID	FieldName	FieldValue
12-Apr-25	6229	Connect_Duration(Days)	62
12-Apr-25	6229	Connect RAG	Green
12-Apr-25	6229	A&R RAG	Green
12-Apr-25	6229	Execute_Duration(Days)	160
12-Apr-25	6229	Execute RAG	Red
12-Apr-25	6229	ProcessType	Voice
12-Apr-25	6229	TransitionType	Standard
16-Apr-25	6229	Connect_Duration(Days)	65
16-Apr-25	6229	Connect RAG	Red
16-Apr-25	6229	A&R RAG	Red
16-Apr-25	6229	Execute_Duration(Days)	180
16-Apr-25	6229	Execute RAG	Green
16-Apr-25	6229	ProcessType	Non Voice
16-Apr-25	6229	TransitionType	Standard

	Then	Required format/final format required
		only Field Name & Field Value to transformed
WeekDate	ProcessMapID	Connect_Duration(Days)	Connect RAG	A&R RAG	Execute_Duration(Days)	Execute RAG	ProcessType	TransitionType
12-Apr-25	6229	62	Green	Green	160	Red	Voice	Standard
16-Apr-25	6229	65	Red	Red	180	Green	Non Voice	Standard

[dufoq3 · Accepted solution]

Hi @ssk_1984, try this:

Ouput

Procedure

[ssk_1984]

Thank you very Much, It really helps me a lot to learn new things 🙂

[jgeddes · Accepted solution]

One way to do this is to;

1) group by the first column choosing do not aggregate so you get all the rows

2) add an index column to the resulting nested tables

3) expand the nested tables

4) pivot the table on the first column, choosing not to aggregate the values in the second column

5) remove the index column

Here is an example code...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUQouLUotzlCK1YlWCk7MSS0GChkagLlQBUGJWSiyRiiywaV5mTko8sZA+VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"All", each Table.SelectColumns(_, {"Column2"}), type table [Column1=text, Column2=text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"All", each Table.AddIndexColumn(_, "index", 1, 1)}}),
    #"Expanded All" = Table.ExpandTableColumn(Custom1, "All", {"Column2", "index"}, {"Column2", "index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded All", List.Distinct(#"Expanded All"[Column1]), "Column1", "Column2"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"index"})
in
    #"Removed Columns"