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.
Hi,
I'm often encountering the same problem when getting my data ready. I'll put a small example hereunder.
We have a table which looks like this:
Date - CoworkerA - CoworkerB - CoworkerC - ... - CoworkerX
1-1-2018 - 8 - 8 - 4 - ... - 6
2-1-2018 - 8 - 8 - 2 - ... - 6
3-1-2018 - 8 - 8 - 4 - ... - 3
...
31-1-2018 - 8 - 8 - 1 - ... - 6
The "-" indicated a new column. The values are "hours worked"
I have no idea how to distribute this data into a more workable table like:
Date - CoworkerName - Hours worked
1-1-2018 - A - 8
1-1-2018 - B - 8
1-1-2018 - C - 4
2-1-2018 - A - 8
2-1-2018 - B - 8
2-1-2018 - C - 2
...
31-1-2018 - A - 8
31-1-2018 - B - 8
31-1-2018 - C - 1
Hopefully you guys have a solution or suggestion how I am to tackle these kinds of problems in the future. If there is a solution in Excel, that would also be fine. Because i mostly input data from Excel.
Solved! Go to Solution.
This is a good test case for using 'Unpivot Columns' in the Power Query Editor.
Select all the 'Coworker ...' columns first, then choose 'Unpivot' on the Transform menu
Just to make clear that I do know how to distribute the table manually. But with enourmous tables this would take way to much time.
This is a good test case for using 'Unpivot Columns' in the Power Query Editor.
Select all the 'Coworker ...' columns first, then choose 'Unpivot' on the Transform menu
Thanks! Will try this.
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 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |