Forum Discussion
Anonymous
3 years agoNot applicable
Need Help on Data Transformation
Happy holidays everyone! Need help on transforming this data that comes from the client tool as follows : Name 1.1 1.1 Score 1.2 1.2 Score 2.1 2.2 Score Name 1 Attendance 100% Qual...
- 3 years ago
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwVNJRciwpSc1LScxLTgVyDA0MdAwMVIGswNLEnMySSiDLwhQq5JiSkVqUClFoBlEXqwM1yQjdJFOYQQFF+SmlySWZZRDTYMLBPo5A0hxidmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"1.1" = _t, #"1.1 Score" = _t, #"1.2" = _t, #"1.2 Score" = _t, #"2.2" = _t, #"2.2 Score" = _t]), #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Context", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Type", each if Text.Contains([Context],"Score") then "Score" else "Attribute"), #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Type]), "Type", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Pivoted Column", {{"Context", each Text.BeforeDelimiter(_, " "), type text}}), #"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"Name", "Context"}, {{"Attribute", each List.Max([Attribute]), type nullable text}, {"Score", each List.Max([Score]), type nullable number}}) in #"Grouped Rows"Before:
After:
Anonymous
3 years agoNot applicable
thank you sir! awesome work.. had to look at each step to understand what happened
pretty creative to use the 1.1 etc to group columns together.. appreciate it!
bolfri
3 years agoSolution Sage
I am happy to hear that I could help. 🙂 If some steps are unclear for you (why I did that) - just ask.
P.S. It's not important, but I am a girl 😁 Not "sir".
- Anonymous3 years agoNot applicable
so sorry >< thanks a lot ms. bolfri and happy holiday