Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 , 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
thaks a lot. its working
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |