Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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. An...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

Migasuke's avatar
Migasuke
Icon for Memorable Member rankMemorable Member
5 years ago

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!



  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    this is what i am after thank you Migasuke 

    • v-kelly-msft's avatar
      v-kelly-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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