Forum Discussion
Power BI - Difficulty getting the data ready
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.
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
3 Replies
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.
