We've seen many ways to create a gant chart, But this one is done using only Power Query.
No Complex DAX or VB required

It is all about massaging the data
And Who is the best in data massaging ? That's right… The Legendary Power Query

In this scenario i have a team of employee which are assigned to projects. I want to balance load between them and see if any of them is free for my next project

The magic steps

* Load the projects table to Power Query
* Use a Power Query Custom function join the projects table with a list of dates between start and end date
* Remove non business days
* Remove holidays by Joining the new table with a holidays table , Because if one of the employees are in holiday, well, They cant work

Lets get started !

So I have a table with Project Code, Employee Name, Start Date, End Date and number of hours

First I load the table into Power Query, Using the "From Table" option.

I created a function which gets a Start and End Dates , And return a list of dates between them, This is the M (Power Query language) code:

(StartDate,EndDate) =>
 let
 Days = Number.From((EndDate-StartDate)),
 Func = List.Transform(List.Numbers(0,Days+1,-1), each Date.AddDays(EndDate,_))
in
 Func

Gant Query Steps

1. Added a new column to the projects table , With the list of dates from start to end

2. Expanded the new column "SpreadDay" , Now each row in the data set is a date per project per employee

3. Removed Fridays and Saturdays

4. Remove Holidays  – I have a table of holidays per employee:

After loading it to Power Query , I removed the rows which occurred in holidays  , by using a left Anti Join  (Which is equivalent to left join -> keep rows where null in Sql)

Hours Per Day

I want to calculate how many hours an employee needs to work per day by counting the amount of working days he have on the project
After eliminating weekends and holidays. this is a naive approach as it assume a linear division , But it is good to get a feel of the workload.

To do this i counted the number of days each employee had to work on each project, And then divided them by the number of hours assigned to the project.

Output

I loaded the query into the OUTPUT sheet, But you can load it to data model and add dates dimension and some other stuff…

Visualizing

There's lots of things you can do with this data, Here i created a simple pivot with Cond. formatting to visualize the workload

A Demo Workbook

You can download the workbook used in this post here:

[wpdm_file id=2]

Conclusion

Power Query is a powerful tool, And i feel like i have not yet scratched it's surface . More to Come !