cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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.

 

 

1 ACCEPTED 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

View solution in original post

3 REPLIES 3
Frequent Visitor

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

View solution in original post

Thanks! Will try this.

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors