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

Power Query. Pivoting data and creating missing dates in between start and end dates

Hi,

I am building a staff utilisation dashboard.


I have a dataset with projects estimated start and end date. Each project is allocated to a project manager.  An assumption is that a project manager should dedicate an even number of hours to a project per day (4.2)

 

Project NoStartEndOwneravailable hours to work on project per day
11/04/202030/07/2020Eunice4.2
21/04/202030/07/2020Ayla4.2
328/02/202030/05/2020Martin4.2
41/04/202030/10/2020Eunice4.2
51/05/202030/06/2020Ayla4.2
61/03/202030/06/2020Martin4.2
71/04/202030/06/2020Eunice4.2
81/04/202030/07/2020Ayla4.2
928/02/202030/05/2020Martin4.2
101/05/202030/06/2020Eunice4.2


I need to be able to show how much capacity and workload we have got at any given point of time at aggragate level

The visual that I am after is something like that:

graph.PNG

To build such visual the data should be in this layout: 

DateProjectNameHours per day
1/04/20201Eunice4.2
2/04/20201Eunice4.2
3/04/20201Eunice4.2
   
30/07/20201Eunice4.2
1/04/20202Ayla4.2
2/04/20202Ayla4.2
3/04/20202Ayla4.2
4/04/20202Ayla4.2
   
29/07/20202Ayla4.2
30/07/20202Ayla4.2
….   

 

Is there way to convert the original dataset into the final one that using PowerQuery? Or is there way to do that without creating missing rows with dates inbetween Start and End date of a project?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Resource Utilisation. Timeline of Total work hours required vs staff capacity

I have figured out how to do that by creating a "list" of a ranges of values between two dates in Power Query:

Dates = { Number.From([Start])..Number.From([End]) }

 

Then, I expanded the list to new rows and created duplicate lines with project id, names and hours for each day of a project.

 

 

View solution in original post

3 REPLIES 3
Highlighted
Super User IX
Super User IX

Re: Resource Utilisation. Timeline of Total work hours required vs staff capacity

This is very difficult to read.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Resource Utilisation. Timeline of Total work hours required vs staff capacity

Fixed the formatting. Hope it is easier to read now. Please tell me if not clear I can try to explain better what I need
Highlighted
Frequent Visitor

Re: Resource Utilisation. Timeline of Total work hours required vs staff capacity

I have figured out how to do that by creating a "list" of a ranges of values between two dates in Power Query:

Dates = { Number.From([Start])..Number.From([End]) }

 

Then, I expanded the list to new rows and created duplicate lines with project id, names and hours for each day of a project.

 

 

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors