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:
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".
Anonymous
3 years agoNot applicable
so sorry >< thanks a lot ms. bolfri and happy holiday