Forum Discussion
Power Query to Query Data for this week only - Week Starts on a Sunday
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.
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 nullPete
2 Replies
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 nullPete
This is perfect. Many thanks for your help 🙂
