data transform

[tangutoori]

Dear Team,

pls try to solve the below scenario.

SOURCE
Name	Field	Previous	Current	Change Date
Naren	Age	24	25	16-03-22
Naren	Location	India	USA	16-03-22
Naren	Vehicle	Tayota	Honda	18-03-22
Rajesh	No of Kids	0	1	19-03-20
Rajesh	No of Kids	1	2	19-03-22
Rajesh	Status	active	inactive	19-03-22
OUT PUT
Name	Change Date	Age Previous	Age Current	Location Previous	Location Current	Vehicle Previous	Vehicle Current	No of Kid Previous	No of Kid Current	Status Previous	Status Current
Naren	16-03-22	24	25	India	USA
Naren	18-03-22					Tayota	Honda
Rajesh	19-03-20							0	1
Rajesh	19-03-22							1	2	Active	Inactive

Regards,

Narender.

[amitchandak · Accepted solution]

@tangutoori , In Power Query, Paste this code in a blank Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kssSs1T0lFyTE8FkkYmIMIUSBia6RoY6xoZKcXqIBT55CcnlmTmg5ieeSmZiUA6NNgRl+qw1IzM5ByQsSGJlfklINUe+XkpINrQAkl9UGJWanEGUNQvXyE/TcE7M6UYyDEAKQNhS7BSAzxKQcqMEErRTA0uSSwpBSlLTC7JLAM5JzMPzkRoiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Field = _t, Previous = _t, Current = _t, #"Change Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Field", type text}, {"Previous", type text}, {"Current", type text}, {"Change Date", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Field", "Change Date"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Field", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Max)
in
    #"Pivoted Column"

Or refer to file attached

[tangutoori]

thaks a lot. its working