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

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.

magshehab_0-1626651307789.png

 

@Greg_Deckler @amitchandak 

Thanks,

1 ACCEPTED SOLUTION

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:

vkellymsft_0-1626845654275.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

9 REPLIES 9
Migasuke
Super User
Super User

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.



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Anonymous
Not applicable

Anonymous
Not applicable

i can filter already to exclude Sat, Sun or Public Holiday as i have setup a calendar table. Issue is i lose the effort allocated on those days. I want to be able to set date to next working date..

Anonymous
Not applicable

Hi. Thank you. The issue is that i want to transfer the effort to next working day

Migasuke
Super User
Super User

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!





If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Anonymous
Not applicable

magshehab_0-1626664015779.png

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:

vkellymsft_0-1626845654275.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Anonymous
Not applicable

Hi Thank you so much. I already have an existing table with many opther columns not relevant to this calculation. Do i just change the table name to my table name as the other fields are the same? I'm only a true novice 🙂  Thank you

Hi @Anonymous ,

 

Yes,you could duplicate one then apply the calculation in the new table.

 

Best Regards,
Kelly

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

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.

Top Solution Authors
Top Kudoed Authors