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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Keshavpooja
Frequent Visitor

Convert column from Datetimezone to Datetime - M Query

Hi Experts,

 

I am getting values from the source like '2021-02-10 20:48:24 +01:00' which has been assigned type 'DateTimeZone' in the Query Editor. I would like to store it as 'DateTime' format with the value as '2021-02-10 21:48:24'. I have tried few steps which didn't work. Could you pls help with the correct M query.

 

1. First I tried to assign it directly as DateTime type assuming that would work in the way I expected, but no luck.

 

2. Then I tried --> DateTimeZone.RemoveZone(DateTimeZone.SwitchZone([#"Date/Time Opened"],1))  -->  but didn't work either.

 

Thanks & Regards,

Kesavan.

 

 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

Please try this expression.

 

= DateTimeZone.RemoveZone([DTZ]) + #duration(0, DateTimeZone.ZoneHours([DTZ]),0,0)

 

mahoneypat_0-1633263599655.png

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
mahoneypat
Microsoft Employee
Microsoft Employee

Please try this expression.

 

= DateTimeZone.RemoveZone([DTZ]) + #duration(0, DateTimeZone.ZoneHours([DTZ]),0,0)

 

mahoneypat_0-1633263599655.png

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi @mahoneypat,

 

That's superb. It works. Thanks a lot.

 

//Kesavan.

Fowmy
Super User
Super User

@Keshavpooja 

You can add zero as the parameter SwitchZone:

DateTimeZone.RemoveZone( DateTimeZone.SwitchZone([DateTime], 0))

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi @Fowmy 

 

If I add 0 as parameter as you suggested, it becomes '2021-02-10 19:48:24'. 

 

Something seems to be wrong here. It behaves wierd.

 

 

StefanoGrimaldi
Resident Rockstar
Resident Rockstar

simple change the column to data/time/timezone first, them change it back to date/time or date/time using locale, so that should work and give you the date/time in the format you want 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Hi,

It does not work. Pls see the time closely. If i do like you said  i get only 20:48:24 but i would need 21:48:24

 

Kesavan.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors