Non-Trivial Relationship

[Anonymous]

Hi,

I have this really messy table, containing the staff schedule - currently in Excel.

In the real table - each person can be on several project simultaniasly, and also in a different position in each project - so this is a simpler example:

on the right side of each staff member - there is a row of all work-weeks in that year, and each cell contains the number of working hours for each week.

I need to move this data into Power BI and connect it to a date-table so I can create some user-friendly visuals for that schedule.

What do you think is the right & efficient way to do that?

I'm open to any suggestions, even if it means creating a new and improved excel to load to PBI.

Thanks.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

You can just unpivot all the work-weeks and, create an new date table in power bi with work-weeks column also. connect these two table by work-weeks columns.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[ERD]

Hi @Anonymous ,

It would be much easier to create calculations if you unpivot columns with work-weeks in Power Query:

Choose the columns, click Transform - Unpivot columns. 

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.