Filter Date First Monday of Week

[JackDanIce]

Hi,

I import a table of data into PQ with a column of dates.

I wish to filter this column for dates greater than or equal to the Monday of the current week

In Excel this would be:

=TODAY()-WEEKDAY(TODAY(),2)

At the moment I'm generating the week number in the data and using PQ to filter as:

= Table.SelectRows(#"Changed Type", each [WeekNum] = List.Max(#"Changed Type"[WeekNum]))

I can either generate this Monday date in an Excel function then pass this to PQ to filter the date column with OR use a standalone function to calculate this date and then filter the data column.

Preference would be to do this all in PQ and drop the [WeekNum] column from the data. I've tried to create this function but it doesn't work:

= () => Date.StartOfWeek(DateTime.LocalNow)

Can anyone help?
TIA,
Jack

 

[peter789]

Would this work for you?

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    #"Inserted Start of Week" = Table.AddColumn(#"Changed Type", "Start of Week", each Date.StartOfWeek([Date]), type datetime),
    #"Start of Week" = #"Changed Type1"[Start of Week],
    LatestWeek=List.Max(#"Start of Week"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Start of Week",{{"Start of Week", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [Start of Week] = LatestWeek)
in
    #"Filtered Rows"

Peter

 

[JackDanIce]

Hi Peter,

Thank you for reply and this does work

I thought it might be faster to calculate a single value for start of (current) week and then filter by that, but your approach works so will mark as solved and reply back if I figure it out - as much to learn for myself.

Cheers and Merry Christmas!
Jack

 

[peter789]

Jack
You're welcome.
Merry Christmas
Peter

 

[sandy666]

maybe

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Date = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    CurrentWeek = Table.AddColumn(Date, "Custom", each Date.IsInCurrentWeek([Date])),
    Filter = Table.SelectRows(CurrentWeek, each ([Custom] = true)),
    TSC = Table.SelectColumns(Filter,{"Date"})
in
    TSC

 

[JackDanIce]

Hey @sandy666 that also works (with slight tweak for additional columns), thank you for suggesting

 

[sandy666]

thank you for suggesting

You are welcome

 

[peter789]

Thanks from me as well, every day is a learning one. Much neater than my solution and solves the problem if there are no dates entered in the current week.

 

[sandy666]

Thanks from me as well