Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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% | Quality | 85% | Adherence | 60% |
Name 2 | Attendance | 50% | Productivity | 0% | SLA | 75% |
The output I am trying to achieve would be :
Name | Attribute | Score |
Name 1 | Attendance | 100% |
Name 1 | Quality | 85% |
Name 1 | Adherence | 60% |
Name 2 | Attendance | 50% |
Name 2 | Productivity | 0% |
Name 2 | SLA | 75% |
Tried unpivoting columns/unpivoting other columns one column at a time and also simultaneously but I'm not getting the desired result. To add context, I am required to create a visual to show the count of 0% score instances per attribute/metric.
Any solution would be a huge help 🙇
Solved! Go to Solution.
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:
Proud to be a Super User!
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!
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".
Proud to be a Super User!
so sorry >< thanks a lot ms. bolfri and happy holiday
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:
Proud to be a Super User!
User | Count |
---|---|
84 | |
84 | |
68 | |
62 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |