Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Creating a list between start date and end date to corresponding days of the week

Hi all

 

I have the following data.  I would like to create a list of dates between start and end dates. I have tried using a List but because I have the days of the week (attribute) listed as well as the corresponding time worked (value) , I get a list dates between the two dates, 7 times. What I need to see is the date against each week day that is between the start and end dates. I suppose basically something like a fill down or formula that adds 1 day increment to each row and then on the 8th row, it starts from that date and adds 1 day increments again. 

Can anyone help me out please?

 

  • pbi101's avatar
    pbi101
    5 years ago

    Hi @KazL,

    Please try this ...

     

    Just add the Ordinal value of [Attribute] to the [Start of Week]

    eg. Date.AddDays([Start of Week], List.PositionOf({"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},[Attribute]))

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PU9JRMtI3sNA3MjAyBLKBLBgnVidaKaQ0Fb+C8NQUAiZklOJX4FaUiV9BcGIJAQWlBHwB86ahIUjMAKIAxkH2Jk4FMG/iNgHqTZwKYN7EqQDmTdwKSvH5IhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, #"Start of Week" = _t, #"Week Ending" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Week", type date}, {"Week Ending", type date}}),
    #"Added Week Day Number" = Table.AddColumn(#"Changed Type", "Week Day Number", each List.PositionOf({"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},[Attribute]), Int64.Type),
    #"Added Date" = Table.AddColumn(#"Added Week Day Number", "Date", each Date.AddDays([Start of Week],[Week Day Number]), type date)
    in
    #"Added Date"

     

     

     

7 Replies

  • pbi101's avatar
    pbi101
    Frequent Visitor
    5 years ago

    Hi @KazL,

    Please try this ...

     

    Just add the Ordinal value of [Attribute] to the [Start of Week]

    eg. Date.AddDays([Start of Week], List.PositionOf({"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},[Attribute]))

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PU9JRMtI3sNA3MjAyBLKBLBgnVidaKaQ0Fb+C8NQUAiZklOJX4FaUiV9BcGIJAQWlBHwB86ahIUjMAKIAxkH2Jk4FMG/iNgHqTZwKYN7EqQDmTdwKSvH5IhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, #"Start of Week" = _t, #"Week Ending" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Week", type date}, {"Week Ending", type date}}),
    #"Added Week Day Number" = Table.AddColumn(#"Changed Type", "Week Day Number", each List.PositionOf({"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},[Attribute]), Int64.Type),
    #"Added Date" = Table.AddColumn(#"Added Week Day Number", "Date", each Date.AddDays([Start of Week],[Week Day Number]), type date)
    in
    #"Added Date"

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks pbi101. That worked well. Was just what I needed. 

      I appreciate your time in helping me out. 

      Cheers

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks Samarth. I have tried this but it didn't work as I get 7 lots of 7 days in the new column. This is because I have 7 rows of the same start and end date. 

      Karen

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        Anonymous , Something like this , a new column in M

        List.Dates([Start Date], Duration.Days([End Date] - [Start Date]), #duration(1,0,0,0))

         

        or

         

        List.Dates([Start Date], Duration.Days([End Date] - [Start Date])+1, #duration(1,0,0,0))