Forum Discussion

stampedeadam's avatar
stampedeadam
Regular Visitor
3 years ago
Solved

How to get weekdays from a date range

Hi everyone I am new here and hoping someone can help me find a solution to this powerquery problem:

 

Let's say I have two columns with dates in them, one called "startdate" and one called "enddate".  What I need to make is another column that has all the weekdays included between those two dates. The order that the weekdays appear is not important.

 

(NB - I am in the UK, and we write the date as DD/MM/YY).  I am using powerquery from within powerBI.

 

So, for example, if I had 11/03/23 as the startdate and 16/03/23 as the enddate, the column would have "Saturday Sunday Monday Tuesday Wednesday Thursday" or the same in a different order.

 

I would be grateful for any help or suggestions.  Thanks!

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    = List.Transform({Number.From([StartDate])..Number.From([EndDate)},each Date.DayOfWeekName(Date.From(_)))

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    = List.Transform({Number.From([StartDate])..Number.From([EndDate)},each Date.DayOfWeekName(Date.From(_)))

  • KeyurPatel14's avatar
    KeyurPatel14
    Responsive Resident
    3 years ago

    HI stampedeadam ,
    Please I have implemented the same question and also got the expected output.
    Here is the M Code for the same:
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWNzTUNzIwMlbSAXPMIJxYHYicOZKckQlULhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "AllDates", each let
    alldateinnumber = {Number.From([Start Date])..Number.From([End Date])},
    alldateindate = List.Transform(alldateinnumber,each Date.From(_))
    in
    alldateindate),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "AllDates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"AllDates", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "WeekDay", each Date.DayOfWeekName([AllDates]))
    in
    #"Added Custom1"


    Please refer the below image for better understanding:

    I hope this will solve your problem and if you face any difficulties then please let me know.
    And if this helps you then give it a kudos and accept it as a solution.

    Thank you,
    KeyurPatel

  • stampedeadam's avatar
    stampedeadam
    Regular Visitor
    3 years ago

    Thank you very much indeed to both KeyurPatel14 and wdx223_Daniel for their kind assistance

     

    wdx223_Daniel's solution was the best fit for what I am trying to do.

     

    In case anyone else has a similar requirement and searches for this in the future, I needed to add one square bracket to wdx223_Daniel's solution for it to work (I have inserted it into his solution and made it red/bold):

     

    = List.Transform({Number.From([StartDate])..Number.From([EndDate])},each Date.DayOfWeekName(Date.From(_)))