Facing challenges in pivoting the data

[Anonymous]

Hi Team,

Please help me to transform the below data in required format

I have some data in the below format.

Attribute	Value
Emp name	John
Dept	Mathematics
Emp ID	123
Emp name	Daniel
Dept	Science
Emp ID	124
Emp name	Mohammed
Dept	History
Emp ID	125
Emp name	Andreas
Dept	English
Emp ID	126
Emp name	Richard
Dept	Geography
Emp ID	127

Now i need to transform this as below.

Emp ID	Emp name	Dept
John	Mathematics	123
Daniel	Science	124
Mohammed	History	125
Andreas	English	126
Richard	Geography	127

Regards,

Priyanga

[wdx223_Daniel]

=Table.Combine(List.Transform(Table.Split(PreviousStepName,3),each Table.PromoteHeaders(Table.Transpose(_))))

[ImkeF · Accepted solution]

Hi @Anonymous ,
that is basically an unstacking exercise where you need an ID column for each row. You can create that as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc0/C8IwEAXwryI3d/H/LLRohS46hgxHejQHzSUkWfz2VhGJcbx378dTCjoXVoKOoIGrtwK6UdBSyMs9YLbkMLNJ7/hV7dvlsd5sv8HHtihMc6nvhkkM1XJXy8FbdI7G0l44ZR8ftd3X9iRjJEwl7WSaOdmaHmp6Y2Mx/qyeyU8Rg/3bPYLWTw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute", type text}, {"Value", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

See steps: "Add Index" and "Integer-Divide Column".

[Anonymous]

Thanks a lot!!!  @ImkeF , @wdx223_Daniel