Forum Discussion
Solved
Unpivot or Transpose Header in row level into Column also values
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 Sal...
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"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
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
TransformedHi 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
