Power Query to Query Data for this week only - Week Starts on a Sunday

[msommerf]

Good morning all,

Please can someone assist with the following?

I am new to Power Query and this is driving me crazy.

I am attempting to query data and my dataset is very large. I am looking to pull data for this week only.

I have attempted to use Date.IsInCurrentWeek but this returns data with Monday as the start of the week.

My week starts on a Sunday.

I have attempted to use:

= Table.AddColumn(#"Expanded {0}1", "Is This Week", each Date.IsInCurrentWeek([Inspection Due],Day.Sunday))

but this throws up an error.

Is there a way to query the data so I get this weeks data Sunday through to Saturday?

Any assistance appreciated.

[BA_Pete · Accepted solution]

Hi @msommerf ,

All of the Date.IsInCurrent/NextXXX etc. functions use the system clock/region settings to determine week start/end dates. I assume you are trying to report on financial week but run your system on a regular Mon-Sun working week.

Try something like this:

if
[date] >= Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Sunday)
and
[date] <= Date.EndOfWeek(Date.From(DateTime.LocalNow()), Day.Sunday)
then 1
else null

Pete

[msommerf]

This is perfect. Many thanks for your help 🙂