Forum Discussion
Split Date range and create duplicate rows but only for workdays
Hi,
For the table below, I wish to split the row into multiple rows but only for workdays. I've managed to split the rows with some research and fantastic assisrtance but still a little stuck. Any solution, help or guidance is much appreciated.
Thanks,
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,
KellyDid I answer your question? Mark my post as a solution!
9 Replies
Hi Anonymous ,
If you already managed to create a new rows, you have most of the job done.
To keep only workdays you need to have a table, which would have column with Date and column with information, if there is a Public Holiday (I assume you want to exclude them as well).
Once you have these 2 columns in this table, add there a new column with Days of a Week (use Date.DayOfWeek).
As a last step in this table - add a conditional column, something like: If Days of Week contains Saturday or Sunday or Public Holiday is true, then give me "Not Working Day" else "Working Day".
Merge this table to your existing table, and apply filter only on "Working Day".
Let me know, if that helps!this is what i am after thank you Migasuke
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,
KellyDid I answer your question? Mark my post as a solution!
Hi Anonymous ,
Does this mean you were not able to expand the rows?
If yes, there is other post with solution: https://community.powerbi.com/t5/Power-Query/Help-creating-an-academic-year-calendar-in-Power-Query/m-p/1936262#M57116
And then you can apply steps I mentioned earlier.
