Forum Discussion
Table Transformation
Hi all,
I have a table with a format similar to:
| Day | 1 | Type | Automobile |
| Sale | 24 | Cost | 12 |
| Location | TX | Make | XYZ |
I want to convert it to a format:
| Day | Sale | Location | Type | Cost | Make |
| 1 | 24 | TX | Automobile | 12 | XYZ |
Any guidance is much appreciated! Thank you
anshpalash amitchandak is on right track but his solution will not work once you have more than one set of data. To overcome this, we need to group data together and I assumed there is going to be 3 rows for each record set.
You can tweak the PQ as you see fit, in PQ, start a new blank query, click advanced editor and paste the below M code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckmsVNJRMgTikMqCVCDlWFqSn5uflJmTqhSrE60UnJiTqgAUNjIBEs75xSUg1UZgKZ/85MSSzPw8kHRIBJDwTcwGq42IjAIrgJptgDDcObEIzVQDZGMNsJgbjGSwo5OzUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}, {"Col3", type text}, {"Col4", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Group", each if Number.Mod([Index],3)=0 then [Index]/3 else null, Int64.Type), #"Filled Up" = Table.FillUp(#"Added Custom",{"Group"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Index"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Col1]), "Col1", "Col2"), #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Col3]), "Col3", "Col4"), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column1", {"Group"}, "Attribute", "Value"), #"Pivoted Column2" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column2",{"Group"}) in #"Removed Columns1"✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
4 Replies
anshpalash , Try code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSVVQ0lEyMgESzvnFJUDK0EgpVidaySc/ObEkMz8PJB0SASR8E7PBaiMio5RiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t, #"1" = _t, Type = _t, Automobile = _t]), #"Demoted Headers" = Table.DemoteHeaders(Source), #"Pivoted Column" = Table.Pivot(#"Demoted Headers", List.Distinct(#"Demoted Headers"[Column1]), "Column1", "Column2", List.Max), #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Column3]), "Column3", "Column4", List.Max), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column1", {}, "Attribute", "Value"), #"Pivoted Column2" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value", List.Max) in #"Pivoted Column2"anshpalash , file after signature if needed
anshpalash amitchandak is on right track but his solution will not work once you have more than one set of data. To overcome this, we need to group data together and I assumed there is going to be 3 rows for each record set.
You can tweak the PQ as you see fit, in PQ, start a new blank query, click advanced editor and paste the below M code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckmsVNJRMgTikMqCVCDlWFqSn5uflJmTqhSrE60UnJiTqgAUNjIBEs75xSUg1UZgKZ/85MSSzPw8kHRIBJDwTcwGq42IjAIrgJptgDDcObEIzVQDZGMNsJgbjGSwo5OzUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}, {"Col3", type text}, {"Col4", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Group", each if Number.Mod([Index],3)=0 then [Index]/3 else null, Int64.Type), #"Filled Up" = Table.FillUp(#"Added Custom",{"Group"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Index"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Col1]), "Col1", "Col2"), #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Col3]), "Col3", "Col4"), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column1", {"Group"}, "Attribute", "Value"), #"Pivoted Column2" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column2",{"Group"}) in #"Removed Columns1"✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Thank you so much!
