cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
charliebyrd
Helper I
Helper I

UTC Timezone differences - Power Apps, SharePoint, Power Query

I've looked at many threads on UTC TimeZone conversions, but can't find my solution.

 

I'm going from Power Apps to SharePoint list and then to Excel through Power Query, problem is in Excel:

 - I am in and my phone is set to local UTC -4 San Juan where I use the Power Apps

 - I want the time data to be in UTC -5 Eastern.

 - SharePoint site is set to UTC -5 Eastern and the time is correct in SharePoint

 - The time is NOT correct in Excel when it comes in through Power Query, it adds 5 hours so 1:00pm is 6pm (same is true for Power BI)

 

PowerApps: 

charliebyrd_1-1605393735805.png

 

SharePoint:

charliebyrd_2-1605393765559.png

 

Excel/Power BI:

charliebyrd_3-1605393819730.png

 

Where and how do I make the conversion for the data in Power Query/Power BI??

 

@WarrenBelz 

 

Thanks!!

C

 

 

 

19 REPLIES 19
Pstork1
Dual Super User III
Dual Super User III

Times in Excel are considered to be without a timezone, which means they store as UTC.  So what is happening is that the time from SharePoint is being adjusted to UTC to store it in Excel. Since SharePoint is UTC-5 its adding 5 hours to convert it to UTC.  You could subtract 5 hours and then store it in Excel so it shows as the right time.  But then it would actually be the wrong time if you read it in to use it somewhere else.  The best solution is to understand that its being stored as Timezone Agnostic and remember to convert it whenever you read it in to antoehr app.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
WarrenBelz
Super User III
Super User III

@charliebyrd ,

Thanks for the tag, but I am sure @Pstork1 can assist you as his advice in the last post is close to what I would have said. The only suggestion I have is convert you Excel data to SharePoint and the issue should go away.

@Pstork1 I see, ok thanks! Can you give me a hint on how to convert it in Power Query? I'm seeing so many DateTime functions like DateTime.AddZone...

Hi @charliebyrd ,

 

The formula you seek for is:

DateTimeZone.ToLocal(DateTime.AddZone([Created],0))

 

In Excel via PowerQuery, you receive values as datetime type in UTC which you need to transform to datetimezone type first.  This is acomplished by DateTime.AddZone([Created],0)(0 confirm that time is UTC) and then the datetimezone values can be converted to your local time using DateTimeZone.ToLocal()

Of course, you wonder why you can add your 5 hours in  DateTime.AddZone([Created],5) to obtain the local time directly. Well, yes it can be a choice but you need to take in consideration if your country (or countries in which the data is used) don't use daylightsavings.

So to the avoid this inconvenient, please use the formula and it will take care of everything.

 

Hope it helps !

Thanks @gabibalaban I'm sure this will do the trick, but I'm struggling to make it work in the Power Query editor. What am I missing here...I can't get past the Type of the column...   

 

charliebyrd_1-1605529519998.png

 

 

Hi @charliebyrd ,

It's seems that column "EntryDateandTime" is not datetime as I supposed.

Let's try a different, but similar, approach:

Insert a step before this action:

= Table.TransformColumnTypes(ChangeColumnsName,{{"EntryDateandTime", type datetimezone}})

so we are sure now that the column "EntryDateandTIme" is now of type datetimezone (UTC time) and we can apply directly:

=Table.TransformColumns(#"previous step", DateTimeZone.ToLocal("EntryDateandTime"))

(where previous step is the name of our step above).

 

charliebyrd
Helper I
Helper I

@gabibalaban unfortunately still the same result. Ugh.

No complaints on changing Type before the next step:

charliebyrd_0-1605550486463.png

But it still does not like the transformation:

charliebyrd_1-1605550533686.png

 

@Pstork1 

Pstork1
Dual Super User III
Dual Super User III

Sorry, I'm not an expert with Power Query.  I'm assuming you are using Power Query in Excel.  Is there some reason that you aren't just using an Excel table as a Data Source and patching the results to that directly form Power Apps?  Or using a Flow?



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

@charliebyrd ,

I guess I wasn't clear in my  previous post, all you have to do is to replace "the previous step" in second formula with the real name of the first step.

Please use exactly my syntax if you want your formula to work:

= Table.TransformColumnTypes(ChangeColumnsName,{{"EntryDateandTime", type datetimezone}})

and this is your following step:

=Table.TransformColumns(#"previous step", DateTimeZone.ToLocal("EntryDateandTime"))

 

 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (48,642)