Forum Discussion
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...
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.
TJ
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".
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. :smileylol:
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.
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 ?
