Convert Date Time From Local Time to UTC time Using Power Query in Power BI

A few days ago, I was working on a dashboard for one of my clients. The dashboard compared sales data from two different sources:

• An Excel report (recorded in GST – local time)
• A database (stored in UTC – Universal Time Coordinated)

Initially, the sales IDs and timestamps didn’t match. After some research, I realised the issue wasn’t the sales IDs at all, it was the time zone difference. Excel was showing data in GST, while the database stored the same transactions in UTC.

To fix this, I needed to convert the local time to UTC so both datasets could align correctly.

In this tutorial, I will show you how to convert a Date and time from Local Time to UTC using Power Query.

Steps to Convert Local Time to UTC in Power Query

For this example, I have a simple Excel file that contains two columns: Sales ID and Local Time GST. Check the screenshot below.

Now follow the steps below:

1. Open Power BI Desktop. Load the Excel dataset. Go to Transform Data to open Power Query.

2. In the Power Query editor, go to the top menu and click Add Column -> Custom Column. Give the column a name, for example: GST_TimeZone. In the Custom column formula box, type the following formula:

DateTimeZone.From([LocalTime_GST])

3. Now add another Custom Column. Name it UTC_Time. Enter the formula:

DateTimeZone.ToUtc([GST_TimeZone])

4. Now we can use this UTC_Time in our report.

This way, you can convert the local time to UST using the Power Query editor in Power BI.

Sometimes, data from different sources doesn’t match because of time zones. In our example, Excel showed GST (local time), and the database used UTC. Using Power Query, we can easily convert local time to UTC.

Also, you may like:

• Power BI Date Slicer
• Power BI sort table by Date
• Remove Date Hierarchy in Power BI
• Add a Conditional Column in Power BI