Forum Discussion
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=<
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.
Further Power Query M language or as some call it PQL documentation here
https://msdn.microsoft.com/en-us/library/mt253322.aspx
