TimeZone problems

[RvdHeijden]

I got a questions concerning the Timezone settings.

Im not sure if that is the problem but im getting my data from an oData feed in Sharepoint.

BUt when i use a date column there is a difference of 2 hours.

In Sharepoint it reads for example 08-05-2017 09:54 but in Sharepoint it's 08-05-2017 07:54

Anyone have any idea on how to change this ?

Do i need to change the settings and/or change the formula ?

[MarcelBeug]

Most probably the times are converted from UTC to local (or vice versa), somewhere in Power Query (M).

If you provide some fake example data and the relevant part of the Power Query code, then we will be able to judge what needs to be changed.

[RvdHeijden]

@MarcelBeug

ive check the data in the Query Editor and all dates where set tot +00:00, where it should read +02:00 right ?

You can change the Time to 'Local Time' but then it adds 2 hours (which is good) but sometimes it adds 1 hour, why is that ?

[MarcelBeug]

It all depends on what is happning to your data, e.g.:

• Changing the data type of a column from datetimezone to datetime, will convert the datetimezone to local datetime
• So does function DateTime.From
• Functions DateTime.Date and DateTime.Time will just take the date and time from the datetimezone data, without conversion to local date/time.
• Loading datetimezone data into the data model, will just cut off the zone informtion, so no conversion to local.

The reason that the difference is sometimes 1 hour and sometimes 2 hours is simple: Daylight Saving Time.
In The Netherlands, the difference is 2 hours in Summer and 1 hour in Winter.

So you shouldn't try to subtract 1 or 2 hours from your datetimes, but make sure there won't be a conversion from UTC to local time as outlined above.

This will be my last reaction for now, as I'm heading for a dentist appointment to get rid of 2 wisdom teeth,

[RvdHeijden]

@MarcelBeug

So just changing the datatype wont help in this case ?

I changed the datatype and it looks to do the trick.

Good luck at the dentist, let's hope they wont remove to much 'wisdom' 🙂

[RvdHeijden]

@prateekraina ive already changed the data type to date/time/timezone the date/time had a + 00:00

So when i changed the time to 'Local Time' it had +01:00 or +02:00.

when the data refreshes from a Sharepoint list the dates wil be in Local time right ? or do i still need to change the formula

[prateekraina]

Hi @RvdHeijden,

I think it should work after refresh as well.

[prateekraina · Accepted solution]

Hi @RvdHeijden,

In order to verify whether the timezone of the source and destination matches you can do the following:

In Query Editor, change the type of column to "Date/Time/Timezone".

This will let you decide whether to use any formulas to make it align with the source.

Thanks !!