Forum Discussion
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".
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
Hi DavidMoss, thx for the trust, but you've probably recognized by now, that MarcelBeug is at least on par with me by now ;-)
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
