Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DavidMoss
Advocate V
Advocate V

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
aaande8
Frequent Visitor

What would be the formula to filter data based on data prior to current month?

marcelammelo
Helper I
Helper I

Awesome! Thanks for sharing, buddy!

 

Danilo_Chavez
Frequent Visitor

I love you DavidMoss and MarcelBeug, this is awesome! 

You have no idea how much i needed this. Thanks guys!

descalabro
Frequent Visitor

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

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...

 

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

 

 

@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 ? 

 

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)

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

@descalabro Power Query has several Date functions to add/subtract periods from dates (can also be used with Date/Times and Date/Time/Zones):

Date.AddDays

Date.AddMonths

Date.AddQuarters

Date.AddWeeks

Date.AddYears

 

Remark: if the result would be a non existent date (e.g 1 month added to January 31), then'the result will be the last day of the month.

Specializing in Power Query Formula Language (M)

@MarcelBeug, thanks a lot. 🙂

 

I'm looking for a way to reduce PBI file size. My PBI file is connected to 8 Access files which I am not able to alter in any way. Is there a way to use this kind of filter right on the loading step, so that only a part of each table is loaded into PBI?

 

Or perhaps, is there a way to actually delete rows based on the same method as this filter?

 

I know the filter prevents data from being calculated on the actual report, but still I would like to save time when opening, refreshing and also publishing the file.

 

Once again, thank you,

 

 

TJ

Further Power Query M language or as some call it PQL documentation here

https://msdn.microsoft.com/en-us/library/mt253322.aspx

 

Hi @DavidMoss, thx for the trust, but you've probably recognized by now, that @MarcelBeug is at least on par with me by now 😉

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

You're welcome @DavidMoss

 

As a matter of fact I just copied the DateTimeZone.FixedUtcNow() function from the example, which - in my opinion - doesn't mean that time zones are taken into account.

 

Actually I spent some time on the subject of converting international times and I even created a function to convert a date/time from one Windows time zone to the date/time in any other Windows time zone in the period 2000 through 2027.

As a basis, I created about 130 files (1 for each Windows time zone) with all date/times on which the clock was adjusted in that Windows time zone, typically DST (Daylight Saving Time) switches, with UTC-date/time and corresponding local date/time directly after the switch. During creation I had to adjust the Windows time zone on my computer about 130 times...

This would be closest to a reliable conversion, but then again it should be noted that any historic date/time conversions (e.g. before 2007), become very unreliable as not all historic information is available in Windows, and any future date/times may be subject to government decisions.

Microsoft tries to keep up the pace with developments, https://blogs.technet.microsoft.com/dst2007/ but sometimes decisions are taken on short notice and still not everything is accounted for, like temporary DST suspensions in some Islamitic countries during Ramadan.

 

As a last remark: a specific point of attention with DST switches are missing times (when clocks are moved forward) and ambiguous times (when clocks are moved back).

 

Maybe too much information but I understood this would be an informative topic. Smiley LOL

Specializing in Power Query Formula Language (M)
v-haibl-msft
Employee
Employee

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.