Table Transformation

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