CODEWITHSUNDEEP
excelpowerquerym
tabyrnes
tabyrnes

Reputation: 3

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

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:


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:

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.

Upvotes: 0

Views: 562

Answers (1)

Olly
Olly

Reputation: 7891

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:

enter image description here

Upvotes: 1

Related Questions