Power Query Filter - "Not In Last 7 Days"?

[Andrew_UK]

Hi All,

I'm still quite new to Power query, moderately experienced with Excel, SQL, Powerpivot...

With Power Query I need to build a table that is coming from 2 distinct sources; One needs to be a live feed of all invoices over the last 7 days and that comes from our database (that's already set up), the other is a list of invoices which have been "checked by finance" and they sit in an excel file.

Power Query can do the whole append for me but it can't seem to filter the way I want on the data I'm receiving from the Finance Spreadsheet. I can see the option to Filter for all data within the last 7 days - but I can't see the filter to filter for all data OLDER than 7 days.

I've tried entering Before in the date field and then changing the date to Today() in the formula bar, I also tried Get_Date and NOW() more out of hope than expectation... I've also tried setting it to all data within the last 7 days and then manually inserting the word NOT but that's not working either.

Does anyone have any ideas on this? It seems like it would be a bit of an oversight for Power Query not to have this function.

Thanks in advance!!

Andrew

 

[Matt Allington]

Read my blog post about calendar tables here. Create a Custom Calendar in Power Query - PowerPivotPro

i show a technique to have a variable end date based on today's date. You could adapt that to do what you need.

 

[Andrew_UK]

Read my blog post about calendar tables here. Create a Custom Calendar in Power Query - PowerPivotPro

i show a technique to have a variable end date based on today's date. You could adapt that to do what you need.

Matt,

This is brilliant!! A really neat workaround.

It still leaves me slightly questioning why the designers didn't build it in... but still solution found!!

Andrew