Hours Distribution Start to End

[AllanBerces]

Hi good day,

Can anyone help me if it possible to distribute the hours from start to end date of certain activity.

DESIRED OUTPUT

OR

Thank you

[Deku · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI00jfUNzQCsUxhLFMDpVidaCUjiLQRVNpI3xjKMgDKxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [activity = _t, start = _t, end = _t, hours = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"activity", Int64.Type}, {"start", type date}, {"end", type date}, {"hours", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates([start], Duration.Days([end] - [start]) + 1 , #duration(1, 0, 0, 0))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Hours Per day", each [hours] / List.Count( [Date] )),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom1", "Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Date",{"start", "end", "hours"})
in
    #"Removed Columns"

from 

To

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Activity", Int64.Type}, {"Hours", Int64.Type}, {"Est Stat Date", type date}, {"Est End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Est Stat Date])..Number.From([Est End Date])}),
    #"Inserted Date Subtraction" = Table.AddColumn(#"Added Custom", "Remaining hours", each [Hours]/(Duration.Days([Est End Date] - [Est Stat Date])+1)),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Date Subtraction",{"Hours", "Est Stat Date", "Est End Date"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Activity", Int64.Type}, {"Custom", type date}, {"Remaining hours", type number}})
in
    #"Changed Type1"

Hope this helps.

[AllanBerces]

Hi @Ashish_Mathur thank you very much for the reply working all good.

[Ashish_Mathur]

You are welcome.

[Deku · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI00jfUNzQCsUxhLFMDpVidaCUjiLQRVNpI3xjKMgDKxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [activity = _t, start = _t, end = _t, hours = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"activity", Int64.Type}, {"start", type date}, {"end", type date}, {"hours", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates([start], Duration.Days([end] - [start]) + 1 , #duration(1, 0, 0, 0))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Hours Per day", each [hours] / List.Count( [Date] )),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom1", "Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Date",{"start", "end", "hours"})
in
    #"Removed Columns"

from 

To

[AllanBerces]

Hi @Deku thank you very much for the help, working perfectly.