Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vijay22kar
Helper II
Helper II

date time gets vary in power bi

Hi team,

 

I have one query regarding the date-time value in power bi. 

My data source is SharePoint and the date is  "18-12-2021 16:03"  but when importing the data in power bi then it gets changed as "2021-12-18 10:33:22 AM". time not matched with the original time.

 

Please guide.

Thanks in advance.

 

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @vijay22kar ,

 

This is caused by the different time zones in Power BI and SharePoint Online. You can change the time zone or change the API version to get the correct datetime.

 

Here are the steps of my test.


1. The sample data in SharePoint Online is as follows.

 

vkkfmsft_1-1640066128595.png

2. After loading columns using SharePoint online list connector, the datetime column changes and adds 8 hours to the original time.

 

vkkfmsft_2-1640066171135.png

 

3. Fix the datetime column.
a. I found that by default I was using API version 15, then changed it to 14 and the imported datetime column was correct.

 

vkkfmsft_0-1640066106072.png

 

vkkfmsft_7-1640066958523.png


b. Another way to fix the datetime column is to convert the time zone. First find your site's time zone under SharePoint's Site Settings -> Regional Settings -> Time zone.

 

vkkfmsft_8-1640067045822.png

 

Then change the datetime column type in Power BI to Date/Time/Timezone, add a custom column to change the time zone to the same as in SharePoint (in my example is: -8).

 

vkkfmsft_3-1640066535465.png

= DateTimeZone.RemoveZone(
    DateTimeZone.SwitchZone( [datetime], -8 )
)

vkkfmsft_4-1640066604398.pngvkkfmsft_5-1640066634148.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

2 REPLIES 2
v-kkf-msft
Community Support
Community Support

Hi @vijay22kar ,

 

This is caused by the different time zones in Power BI and SharePoint Online. You can change the time zone or change the API version to get the correct datetime.

 

Here are the steps of my test.


1. The sample data in SharePoint Online is as follows.

 

vkkfmsft_1-1640066128595.png

2. After loading columns using SharePoint online list connector, the datetime column changes and adds 8 hours to the original time.

 

vkkfmsft_2-1640066171135.png

 

3. Fix the datetime column.
a. I found that by default I was using API version 15, then changed it to 14 and the imported datetime column was correct.

 

vkkfmsft_0-1640066106072.png

 

vkkfmsft_7-1640066958523.png


b. Another way to fix the datetime column is to convert the time zone. First find your site's time zone under SharePoint's Site Settings -> Regional Settings -> Time zone.

 

vkkfmsft_8-1640067045822.png

 

Then change the datetime column type in Power BI to Date/Time/Timezone, add a custom column to change the time zone to the same as in SharePoint (in my example is: -8).

 

vkkfmsft_3-1640066535465.png

= DateTimeZone.RemoveZone(
    DateTimeZone.SwitchZone( [datetime], -8 )
)

vkkfmsft_4-1640066604398.pngvkkfmsft_5-1640066634148.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

themistoklis
Community Champion
Community Champion

@vijay22kar 

 

I tried to replicate your issue but with no luck. PowerBI returns the correct date.

 

You should check the regional settings on PowerBI and also on your PC

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.