How to get weekdays from a date range

[stampedeadam]

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!

[stampedeadam]

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(_)))

[KeyurPatel14]

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

[wdx223_Daniel · Accepted solution]

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