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.
I have a table in this structure (from a SharePoint List):
Row ID | Date | Evaluator Name | Question 1 | Question 2 | Question 3 | Question 1 Comments | Question 2 Comments | Question 3 Comments | Question 1 Point | Question 2 Point | Question 3 Point | Question 1 Missed Reasons | Question 2 Missed Reasons | Question 3 Missed Reasons |
1 | 9/18/2020 | Jake | Good | Bad | Good | Comments here | Comments here | Comments here | 3 | 1 | 3 | Reason 1, Reason 2 | Reason 2 | Reason 2, Reason 4 |
2 | 9/19/2020 | Mary | Bad | Good | Good | Comments here | Comments here | Comments here | 1 | 3 | 3 | Reason 1 | Reason 2 | Reason 3 |
3 | 9/23/2020 | William | Neutral | Neutral | Good | Comments here | Comments here | Comments here | 2 | 2 | 3 | Reason 2 | Reason 2 | Reason 2 |
4 | 9/23/2020 | Jake | Bad | Good | Bad | Comments here | Comments here | Comments here | 1 | 3 | 1 | Reason 3, Reason 4 | Reason 4 | Reason 1 |
I'd like to transform it (unpivot, pivot, etc.) to obtain this result (for example):
Row ID | Date | Evaluator Name | Question | Response | Comments | Points | Missed Reasons |
Question 1 | Good | Question 1 Comments | |||||
Question 1 | |||||||
Question 1 |
Basically, I'd like to group all the question columns, comments columns, points columns, and reason columns into their own respective columns with the corresponding rows.
Is there a way to transform my data to get that end result?
Solved! Go to Solution.
You will need to find the delimiter between Question 1 and Comments, Points, etc.
For example, if you have Question 1: Comments
Question 1: Points
Question 1: Missed Reasons
If you don't have that delimiter, you can use a conditional column to look for text that contains Missed Reasons, Point, Comments and use that to determine which part of the question it answers.
Please see the attached file for reference. I will upload it under the signature.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
You will need to find the delimiter between Question 1 and Comments, Points, etc.
For example, if you have Question 1: Comments
Question 1: Points
Question 1: Missed Reasons
If you don't have that delimiter, you can use a conditional column to look for text that contains Missed Reasons, Point, Comments and use that to determine which part of the question it answers.
Please see the attached file for reference. I will upload it under the signature.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |