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

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

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.

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-2017Right=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...

DavidMoss

Advocate V

10 years ago

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.

Tips and Tricks

17 Replies

v-haibl-msft
Microsoft Employee
10 years ago
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
  Advocate V
  10 years ago
  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

9 years ago

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=<
Left=01-01-2017
Right=30-08-2016 17:00:00»

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

Tiago Jordão

DavidMoss

Advocate V

9 years ago

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

9 years ago

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

Danilo_Chavez
Frequent Visitor
8 years ago
I love you DavidMoss and MarcelBeug, this is awesome!
You have no idea how much i needed this. Thanks guys!
marcelammelo
Helper I
8 years ago
Awesome! Thanks for sharing, buddy!
aaande8
Frequent Visitor
6 years ago
What would be the formula to filter data based on data prior to current month?

Forum Discussion

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

17 Replies

Related content

Dynamic Filter

Power BI Dynamic Filter/Slicer

Dynamic measure filtering

Dynamic Filtering

dynamic x-axis based on filter