From gantt chart to daily task

[Anonymous]

Hi all and thank you in advance for your help,

I would like to find a way to transform existing data on a gantt chart format (first image) and change it to a daily task planner (second image). This would help me to accurately stimate daily workload.

Does anyone what steps to follow to achieve this?

Thank you very much for your time.

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

My Sample:

Please try this code to create a new table in Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlYwVNJRMrYAEkYGRkb6hgb6RgbIHDOlWB2oSiOguCGKSkNkjqlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, #"Duration (hours)" = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"End Date", type date}, {"Start Date", type date}, {"Duration (hours)", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Day", each { Number.From([Start Date])..Number.From([End Date]) }),
    #"Expanded Day" = Table.ExpandListColumn(#"Added Custom", "Day"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Day",{{"Day", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Weekend Y/N", each let _dayweek = 
Date.DayOfWeek([Day],Day.Monday)+1
in 
if _dayweek = 6 or _dayweek = 7 then 1 else 0),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "EndDay", each Date.AddDays(
    [Start Date],
Number.RoundUp([#"Duration (hours)"]/8)-1
+
List.Sum(
let _task = [Task]
in    
Table.SelectRows(#"Added Custom1",each _task = [Task])[#"Weekend Y/N"]))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([#"Weekend Y/N"] = 0)),
    #"Added Custom3" = Table.AddColumn(#"Filtered Rows", "Hours", each if [Day] < [EndDay] then 8 else Number.Mod([#"Duration (hours)"],8)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Duration (hours)", "Start Date", "End Date", "Weekend Y/N", "EndDay"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Task", "Hours", "Day"})
in
    #"Reordered Columns"

Result is as below.

Best Regards,
Rico Zhou

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

[Anonymous]

OMG it does work!

Wow, that is very cool Rico. Sorry, I am a real begginer. I have very little knowledge of power bi. Actually it is the first time that I use Power Query.

There is one thing though. I´ve noticed that your code "Remove" the first table and create the second one.

What would I have to do to keep both tables?

Thank you very much Rico!

[Anonymous]

ok! I actually googled it and found a work around.

Just create another table with the original table. Please let me know if you have a better idea.

Now ... Unfortunatelly there is still a problem.

The idea is that this power bi file will be extracting data from an excel file. Eventually, the excel file will be updated with new tasks. See below.

But, when I refresh power bi still shows this table which doesn´t include Task 3. 

This problem still happens even if I create a power bi file from scratch with the updated excel data.

Any idea on how to solve this issue?

Thank you very much for your help!

[v-rzhou-msft]

Hi @Anonymous ,

Here I change my data source from enter data to excel. I think you can click "Refresh All" to refresh your underlying data after you update the data in excel.

As long as both tables are fetching data from the excel, they will be refreshed to the latest data.

Best Regards,
Rico Zhou

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

[Anonymous]

Hi Rico,

Thank you for your answer. There is something wrong and I don´t know what it is ...

Sorry, it doesn´t allow me to attach the files. Anyway, I put this information in Book3.xlsx

Then I go to power bi.

Make the first table to see the result

And then make the query pasting the code that you sent. But I still get the old result.

Please notice that somehow power bi is using the old source. 

Why is this happening?

Sorry, unfortunately I know very little about queries. 

Thank you very much Rico for your help and patience. 

[v-rzhou-msft]

Hi @Anonymous ,

I think you don't need to copy and paste data into Power BI manually. You can connect to Excel directly in Power BI.

For reference:

Connect to Excel in Power BI Desktop

Best Regards,
Rico Zhou

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

[Anonymous]

Hi Rico,

Everything was right from the beginning. The problem was that my file was in spanish. 

I didn´t know how Power Query Editor works at all. This video helped me to figure it out. 

https://www.youtube.com/watch?v=hw6-DNhgOos

As I mentioned before I am new with this stuff. And I am so glad I am learning now.

Thank you very much for your patience, your time and your help.