Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

From gantt chart to daily task

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.

 

JoseNovelda_2-1666521285777.png

 

JoseNovelda_1-1666521217280.png

 

Does anyone what steps to follow to achieve this?

 

Thank you very much for your time.

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @Anonymous ,

 

My Sample:

RicoZhou_1-1666598973767.png

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.

RicoZhou_0-1666598958795.png

 

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.

View solution in original post

7 REPLIES 7
v-rzhou-msft
Community Support
Community Support

Hi @Anonymous ,

 

My Sample:

RicoZhou_1-1666598973767.png

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.

RicoZhou_0-1666598958795.png

 

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
Not applicable

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
Not applicable

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.

 

JoseNovelda_0-1666607631703.png

 

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.

 

JoseNovelda_1-1666608228038.png

 

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

 

JoseNovelda_2-1666608465230.png

 

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!

 

 

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.

RicoZhou_0-1666667794701.png

 

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
Not applicable

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

 

JoseNovelda_0-1666694617491.png

 

 

Then I go to power bi.

 

Make the first table to see the result

 

JoseNovelda_1-1666694653541.png

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

JoseNovelda_2-1666695098277.pngJoseNovelda_3-1666695121379.pngJoseNovelda_4-1666695152901.png

 

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

JoseNovelda_5-1666695200109.pngJoseNovelda_6-1666695231201.png

 

Why is this happening?

 

Sorry, unfortunately I know very little about queries. 

 

Thank you very much Rico for your help and patience. 

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
Not applicable

Hi Rico,

 

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

 

JoseNovelda_0-1666782341588.pngJoseNovelda_1-1666782347212.png

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.