Get dates from day of week in a date range (start date, end date) and year in PowerQuery M

Question

I need to expand a list given the inputs: start date, end date and days of the week that something should happen. This needs to be done by using M in Power Query.

The date range can span multiple weeks, for example:
start date: 04/15/19
end date: 04/29/19
day of week: 1

I would expect to get 3 rows:

04/15/19
04/22/19
04/29/19

or encompass one week & day:
start date: 04/15/19
end date: 04/15/19
day of week: 1

I would expect to get 1 row:

04/15/19

I was using: Get date from weeknumber, dayofweek and year PowerQuery M as a first step in understanding how to approach the solution.

Thanks in advance.

Can you update your question to include whatever formula(s) you've tried so far? — John Ferguson
If you have a date table that contains the entire date range you plan to work with, then you can write a function that simply returns a filtered subset of the date table based on start date, end date, and day of the week. — Alexis Olson
@AlexisOlson Also a valid approach. A date table would however require updating as time went on. start date and end date are not of a set range. The solution below does away with an intermediate date table. Thanks for the feedback! — tabyrnes
I agree. Often you already have an existing date table though for other purposes. — Alexis Olson

Olly Olly · Accepted Answer · 2019-03-28T08:54:05

You can use a function to list all dates in your chosen range, and filter to the chosen weekday:

(DateStart as date, DateEnd as date, DayOfWeek as number) =>
let
    #"Date List" = List.Dates(DateStart,Duration.Days(DateEnd-DateStart)+1,#duration(1,0,0,0)),
    #"Date Table" = Table.FromList(#"Date List", Splitter.SplitByNothing(), {"Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Date Table",{{"Date", type date}}),
    #"Filtered Dates" = Table.SelectRows(#"Changed Type", each (Date.DayOfWeek([Date], Day.Sunday) = DayOfWeek))
in
    #"Filtered Dates"

Invoked as a test:

let
    Source = fnListWeekdaysInRange(#date(2019, 4, 15), #date(2019, 4, 29), 1)
in
    Source

Which returns:

Get dates from day of week in a date range (start date, end date) and year in PowerQuery M

1 Answers