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.

Answer 1

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: