Turning date and time into realtime

[Anonymous]

Hi, 

I am hoping someone can help me with this. In Power BI desktop I have imported data from our database. Since our database stores date and time in UTC, the time is not correct to what it actually is in real time. For instance, we have a bookings column showing when a booking was made. The earliest someone can make a booking is at 08:00 AM, and because of the the UTC to DST conversion, when loading the data from our Database, the time shows as 06:00 since the database does not take into account DST but stores everything in UTC time. This means that (currently), all values for time in the loaded tables is actually +2. So a booking showing as 06:00 AM is in fact 08:00 AM, adjusted for real time DST. 

Since I have imported over 70 tables in Power BI, it would take too much time to rewrite every single column containing a time lagging 2 hours. Therefore, I was wondering if it was possible in the Power Query or somehow adjust all the columns containing time to be correct, so adding 2 hours. 

Please let me know if there is any solution to this other than manually tweaking over 100 columns containing time values. 

[v-rzhou-msft]

Hi @Anonymous 

Power BI Desktop doesn't support to change time zone directly. You may refer to blogs as below.

handling-different-time-zones-in-power-bi-power-query 

How-To-Convert-UTC-to-Your-Local-Time-Zone-in-Power-Query

Best Regards,

Rico Zhou

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

[Anonymous]

Thank you for answering, 

So am I understanding it correctly that if data in the database is stored in a specific time, then imported to Power BI desktop, there is no way of changing/adjusting that data directly, other than creating custom columns? You cannot do this is directly in the imported column in PowerQuery? 

When I change the data to correct format, it does show "Date and time + 02:00:00", which is correct, since real time is two hours ahead. However this disappears when clicking "close and use", and the time is still two hours behind..

[amitchandak]

@Anonymous , refer to the second solution, if that can work for you

https://community.powerbi.com/t5/Desktop/Convert-utc-to-local-time-zone-using-Power-Query/m-p/45533

[Anonymous]

Hi and thank you for your response, 

This solution does not seem to work as I am in need of converting the imported column, and not creating a new column. I could not tell from the solution provided how to convert/transform the imported columns.. Where could I see a solution for this?

The column given in the proposed solution already seems to be a created column and have an expression tied to it. Hence, I am not working with Custom columns, simply the imported column coming from our database, which I need to switch to our current time zone without having to create custom columns for each 100 of them..