cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidMoss Member
Member

Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

More as a reference for us than a question which hopefully you will find useful 🙂

 

I was just working on filtering data in Power Query based on the date. I kow you can manually filter your source data in the Power Query Editor by using the extensive date filter options in the drop down filter menu BUT I needed something better which was to remove data from the source based on a dynamic date point. Basically I needed to filter out any data prior to the start of the current month. Eg today is 4th August and i needed to remove everything rior to the 1st of August. Going forward into September the Query would remove everytning from the 1st of september etc.....

 

Answer 

Using the manual way for filtering anything before 1st august the code in the advanced editor was 

code was : Table.SelectRows(#"Changed Type", each [Date] > #date(2016, 8, 1))

 

To do it dynamically i replaced the fixed date with the following code:

Date.From(Date.StartOfMonth(DateTime.LocalNow() ))

 

Such that the complete new code is:

Table.SelectRows(#"Changed Type", each [Date] > Date.From(Date.StartOfMonth(DateTime.LocalNow() )))

 

In English that is "Date.From" returns a date datatype.

"Date.StartofMonth()" returns the StartofMOnth of the date

DateTime.LocalNow() returns the local system date. 

 

Do be careful using this on production as I am sure my local date where i am developing in Power BI desktop will be different to when i upload this to the Power BI service. So when refreshin g in the cloud it could change my cut off point for month end reporting. I believe there is a function to do with TImeZones that may resolve this if anyone want s to contribute please do....

 

Hope it saves you some time.

 

17 REPLIES 17
v-haibl-msft Super Contributor
Super Contributor

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

Thanks for sharing this, DavidMoss. I think it is a good solution if we only want to keep the current month data.

 

Best Regards,

Herbert

DavidMoss Member
Member

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

Hi Herbert, just for confirmation the code would return anything after the first day of the month of the current system's date , including the current month data and anything else in the future.

I used a similar solution for a Salesforce CRM query to clean out old orders which sales teams often leave in there to ensure only the true active 'live' orders were ETL into the dataset.

 

With further research i also discovered those DateTimeZone functions which help with to adjust for different time zones from the data sources.

descalabro Frequent Visitor
Frequent Visitor

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

Hello,

 

Thank you for your suggestion. However, I get this error:

 

«Expression.Error: It's not possible to apply the operator < to types Date and DateTime

 

Details:

Operator=&lt;
Left=01-01-2017
Right=30-08-2016 17:00:00»

 

The same message appears whether I write "<" or ">".

 

 

TJ

 

 

Tiago Jordão

DavidMoss Member
Member

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

If you do it the manual way as my 1st step in the above original explanation can you cut and paste the M code here from query editor > advanced options as it may have something to do with your local date formats ??

Worth a try anyway...

 

descalabro Frequent Visitor
Frequent Visitor

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

Sure.

 

This is the original M code (works):

 

let
    Source = Access.Database(File.Contents("D:\Users\tljordao\OneDrive - SONAE\Relatorios Individuais\Carla Costa.mdb"), [CreateNavigationProperties=true]),
    #"_Carla Costa" = Source{[Schema="",Item="Carla Costa"]}[Data],
    #"Added Custom" = Table.AddColumn(#"_Carla Costa", "Nome", each "Carla Costa"),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Start", Order.Ascending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"EntryID", "Nome"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [Start] >= #datetime(2017, 1, 1, 0, 0, 0))
in
    #"Filtered Rows"

This is the same code with your suggestion:

 

let
    Source = Access.Database(File.Contents("D:\Users\tljordao\OneDrive - SONAE\Relatorios Individuais\Carla Costa.mdb"), [CreateNavigationProperties=true]),
    #"_Carla Costa" = Source{[Schema="",Item="Carla Costa"]}[Data],
    #"Added Custom" = Table.AddColumn(#"_Carla Costa", "Nome", each "Carla Costa"),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Start", Order.Ascending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"EntryID", "Nome"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [Start] > Date.From(Date.StartOfMonth(DateTime.LocalNow() )))
in
    #"Filtered Rows"

Error (in Portuguese):

«Expression.Error: Não é possível aplicar o operador < aos tipos Date e DateTime.
Detalhes:
Operator=&lt;
Left=01-01-2017
Right=30-08-2016 17:00:00»

 

This is the last code with the correct DateTimeZone function:

 

let
    Source = Access.Database(File.Contents("D:\Users\tljordao\OneDrive - SONAE\Relatorios Individuais\Carla Costa.mdb"), [CreateNavigationProperties=true]),
    #"_Carla Costa" = Source{[Schema="",Item="Carla Costa"]}[Data],
    #"Added Custom" = Table.AddColumn(#"_Carla Costa", "Nome", each "Carla Costa"),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Start", Order.Ascending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"EntryID", "Nome"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [Start] > Date.From(Date.StartOfMonth(DateTimeZone.FixedUtcNow() )))
in
    #"Filtered Rows"

 

Same error from before.

 

 

TJ

 

 

MarcelBeug Super Contributor
Super Contributor

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

Apparently [Start] has DateTime format and the error can be prevented with DateTime.From instead of Date.From:

DateTime.From(Date.StartOfMonth(DateTimeZone.FixedUtcNow()))

 

Note that the result of DateTimeZone.FixedUtcNow() depends on the timezone on which the code is run, e.g. with me in the Netherlands (CET = UTC +1:00) the beginning of this month (UTC) is 1-1-2017 1:00.

 

If your data has mixed timezones, then Power Query has no suitable functions to convert those to 1 universal time (UTC).

There are some functions that allow adding/subtracting zone hours / minutes, but nothing dynamic takling into account DST switches and other clock adjustments (e.g. due to government decisions).

 

As a last remark: note that all functions that are related to system date/time should be interpreted as "... at the date/time of last query refresh".

Specializing in Power Query Formula Language (M)
DavidMoss Member
Member

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

@descalabro wow @MarcelBeuganswered that one quickly. I am amazed by the quality & speed of voluntary support offered here on this PBIcommunity. A pat on the back to us all.

 

We could tell your original data that you ingested is of Date/time format as in the manual M code we see #datetime(2017,1,1,0,0,0) Subsequently when filtering the data with the 'dynamic' manner you need to filter using the m code function for Date/Time and thats what Marcels is saying in more down to earth language.

 

In my oroginal example i had ingested date (ONLY) formatted data and hence the M code to filter it was also a function for filtering Dates as opposedd to Date/Time which you need.

 

Cool to see that you guys have also accomodated timezones into your code.

 

I ask my M code expert @ImkeF to confirm what we have said, if you are there please ? 

 

DavidMoss Member
Member

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

Thnaks @MarcelBeug for your contribution to this....much appreciated. David

Highlighted
descalabro Frequent Visitor
Frequent Visitor

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

Hello @MarcelBeug, thanks for solving my situation. I tohught about changing my column format but I completely forgot to change Date to DateTime.

 

Another question: is it possible to add/subtract days or months to this function, so that I may get a dynamic time interval to filter by?

 

@DavidMoss, thumbs up for you. 🙂

 

 

TJ

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 283 members 2,698 guests
Please welcome our newest community members: