Split Date range and create duplicate rows but only for workdays

Hi  Anonymous ,

Using below M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc31DcyMDJU0gEyzWFMMyA2BWJjEGFhoaoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, DurationDays = _t, Workdays = _t, Efforthrs = _t, Utilisation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"DurationDays", Int64.Type}, {"Workdays", Int64.Type}, {"Efforthrs", Int64.Type}, {"Utilisation", Percentage.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Start Date])..Number.From([End Date])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Date.From([Custom])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "DateList", each if Date.DayOfWeek([Custom.1],Day.Monday)=5 or Date.DayOfWeek([Custom.1],Day.Monday)=6 then null else [Custom.1]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [DateList] <> null and [DateList] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Custom.1"})
in
    #"Removed Columns"

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!