Forum Discussion
Power BI Query for British Summer Time
Hi everyone,
There are already several threads regarding how to handle time differences in Power Query, but my head is spinning and I hope someone can clarify a couple of points or point me in the right direction.
I import data from a SQL server that underpins a Service Desk application. As such, I have four or five date fields to track the progress of a case: Added, Assigned, Updated, Resolved and Closed at least. After the clocks changed to BST a couple of weeks ago, all of the dates are 1 hour out.
First of all, why does Power BI Desktop change the date when my client computer and the SQL server are in the correct time-zone? That alone is making me wonder why I have to consider DAX or M routines to fix something that wasn't broken. I'd be willing to consider a dirty fix if anyone knows of a workaround.
If the only solution is to use the advice I've already found (DAX, M, Query Parameters), can the original value be updated? So far the examples I've seen appear to conclude with a corrected value in a new column. As I said, I have five dates to update and all of them are used in formulas. This is why I want to be absolutely sure of what I need to do and why; it seems to be a lot of work and processing to put the values back to what they are already set to in SQL!
Thank you, all.
7 Replies
Hi Anonymous ,
When you get datetime value to power bi desktop, it will format your datetime value to system timezone. You can consider to change it as utc date time or use datetimezone functions to change display specific timezone datetime.
BTW, power bi service side not support locale date time and they will change to UTC format.
Regards,
Xiaoxin Sheng
Thank you, but unless I've misunderstood, I don't think that's quite where the problem lies because my system timezones are all correct.
If I create a new record in my service desk application, the date will be correct, for example, 10am. If I look at the same record in SQL, it will read 10am. In Power BI, the Now() function would return 10am, therefore consistent with everything else; but when I query the SQL database, the date on that record will be 9am!
Changing my local timezone makes no difference; the record is still pulled in with 9am. It's confusing me as to where this date and time is coming from.
Hi Anonymous ,
I think it may related to daylight saving, maybe you can take a look at following blog:
Daylight Saving Time And Time Zones In M
Please understand that this link is provided with no warranties or guarantees of content changes, and confers no rights.
Regards,
Xiaoxin Sheng
Sadly not, Anonymous. The link above is the best solution I've found, but it's clumsy for my needs.
I haven't been able to ascertain if there's a way to update the original columns in a "replace value" sort of method. Otherwise, you have to repeat them to contain the calculated correct values separately. I already do a lot of time functions based on those columns so trying it is daunting.
Yes, there is a way to replace values:
https://learn.microsoft.com/en-us/powerquery-m/table-replacevalue
So your M language would have something like
#"New Step" = Table.ReplaceValue(#"Previous Step#,
each [DateTime],each <<< Some calculation or function >>> .
Replacer.ReplaceValue,{"DateTime]} )
