Forum Discussion
Split values into months based on working days for each month between 2 dates
Hi I need you help
I want the split the Hours based on number of working days in a month for all months between 2 dates
Sample table:
| Project | Resource Role | Status | Start Date | End Date | Requested Hours |
| ABCD2021 | CTA | Ready to Staff | 23/11/2020 | 31/12/2020 | 48.5 |
| ABCD2022 | RM | Ready to Staff | 23/11/2020 | 31/12/2020 | 2 |
I want the output as per below table
| Project Code | Resource Role | Status | Start Date | End Date | Requested Hours in Stage | Time Period | Time Period Start | Time Period End | workdays | Hours |
| ABCD2021 | CTA | Ready to Staff | 23/11/2020 | 31/12/2020 | 48.5 | 30/11/2020 | 23/11/2020 | 30/11/2020 | 6 | 10.03448 |
| ABCD2022 | CTA | Ready to Staff | 23/11/2020 | 31/12/2020 | 48.5 | 31/12/2020 | 1/12/2020 | 31/12/2020 | 23 | 38.46552 |
| ABCD2023 | RM | Ready to Staff | 23/11/2020 | 31/12/2020 | 2 | 30/11/2020 | 23/11/2020 | 30/11/2020 | 6 | 0.413793 |
| ABCD2024 | RM | Ready to Staff | 23/11/2020 | 31/12/2020 | 2 | 31/12/2020 | 1/12/2020 | 31/12/2020 | 23 | 1.586207 |
Thanks in Advance for your solution
Regards
MD
Hi Anonymous
Here is a first step to get you to "Time Period Start" and "Time Period End". For workdays you'll need a list with what days are workdays in the period. The last column (Hours) I do not understand.Place the following M code in a blank query to see the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydjEyMDJU0lFyDnEEkkGpiSmVCiX5CsEliWlpQAEjY31DQ32gGgMQx1Tf0AjGMbHQM1WK1YEbYgTS7kvQDGNDJDOMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Resource Role" = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t, #"Requested Hours" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Resource Role", type text}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}, {"Requested Hours", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Time Period Start", each let aux_ = [End Date] in List.Generate(() => [Start Date], each Duration.Days(Duration.From(_ - aux_)) < 0 , each Date.StartOfMonth(Date.AddMonths(_,1)))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Time Period Start"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Time Period End", each List.Min({Date.EndOfMonth([Time Period Start]), [End Date]})), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Time Period Start", type date}, {"Time Period End", type date}}) in #"Changed Type1"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
3 Replies
- AlB
Community Champion
Hi Anonymous
Here is a first step to get you to "Time Period Start" and "Time Period End". For workdays you'll need a list with what days are workdays in the period. The last column (Hours) I do not understand.Place the following M code in a blank query to see the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydjEyMDJU0lFyDnEEkkGpiSmVCiX5CsEliWlpQAEjY31DQ32gGgMQx1Tf0AjGMbHQM1WK1YEbYgTS7kvQDGNDJDOMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Resource Role" = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t, #"Requested Hours" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Resource Role", type text}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}, {"Requested Hours", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Time Period Start", each let aux_ = [End Date] in List.Generate(() => [Start Date], each Duration.Days(Duration.From(_ - aux_)) < 0 , each Date.StartOfMonth(Date.AddMonths(_,1)))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Time Period Start"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Time Period End", each List.Min({Date.EndOfMonth([Time Period Start]), [End Date]})), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Time Period Start", type date}, {"Time Period End", type date}}) in #"Changed Type1"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
CNENFRNLA generic solution applicable to a flexible span of period, e.g. 2020/11/8 to 2021/1/16,
let #"Total Workdays" = (#"Start Date" as date, #"End Date" as date) => List.Accumulate({0..Duration.Days(#"End Date"-#"Start Date")},0,(s,c)=>if Date.DayOfWeek(Date.AddDays(#"Start Date",c),Day.Monday)<5 then s+1 else s), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydjEyMDJU0lFyDnEEkkGpiSmVCiX5CsEliWlpQAEjY31DQ32gGgMgx9hQ39AIxjGx0DNVitWBG2IE0u5LmhlGYANCXINDUF0ApC2Q9Bia6YPZIHdaWijFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Resource Role" = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t, #"Requested Hours" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Resource Role", type text}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}, {"Requested Hours", type number}},"en-GB"), #"Added Custom" = Table.AddColumn( #"Changed Type", "Custom", each Table.FromRows( List.Accumulate( {1..(Date.Year([End Date])-Date.Year([Start Date]))*12+Date.Month([End Date])-Date.Month([Start Date])}, {{[Start Date], List.Min({[End Date], Date.EndOfMonth([Start Date])})}}, (s,c) => let next = Date.AddMonths([Start Date],c) in s & {{Date.StartOfMonth(next), List.Min({[End Date], Date.EndOfMonth(next)})}} ), {"Period Start", "Period End"} ) ), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Period Start", "Period End"}, {"Period Start", "Period End"}), #"Added Workdays" = Table.AddColumn(#"Expanded Custom", "Workdays", each #"Total Workdays"([Period Start], [Period End])), #"Added Hours" = Table.AddColumn(#"Added Workdays", "Hours", each let total = #"Total Workdays"([Start Date], [End Date]) in Number.Round([Requested Hours]*[Workdays]/total,2)) in #"Added Hours"