Table Transformation

[anshpalash]

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 

[parry2k · Accepted solution]

@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.⚡

[anshpalash]

Thank you so much!

[amitchandak]

@anshpalash , file after signature if needed

[amitchandak]

@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"