Should You Reconsider Your Approach to Time Conversion in Power BI?
Handling timezones in Power BI as well as any other BI tool is crucial for accurate reporting and analysis across different regions.
Power BI provides the ability to convert timestamps to other timezones. You can use the DateTimeZone.SwitchZone function in Power Query to convert a datetimezone value to a specific timezone 1.
Here’s an example of how you can use this function to convert a datetimezone value to your local timezone:
DateTimeZone.SwitchZone(DateTimeZone.From(DateTime.LocalNow()), -5)In this example, -5 represents the UTC offset for the timezone you want to convert to.
What is the problem with it?
In Power BI, there’s no built-in timezone directory. The only way to convert a timestamp to another timezone in Power BI is to explicitly define the number of hours which represents the shift from the default timezone (which is predominantly UTC). This approach doesn’t work for the vast majority of countries in the world. And here is why.
The main challenge arises because the offset between a specific time zone and UTC can change throughout the year due to DST observance. Power BI’s functions are not able to automatically adjust for these changes in offset. So when dealing with regions that have DST changes, you might need to account for these shifts manually in your calculations.
What can we do about it?
How about developing custom logic or functions in Power Query or DAX? It could consider the specific time zone rules and historical offset changes for regions with DST.
First of all, it’s incredibly tedious. All the tutorials you’ll find out there will give you a huge list of steps on how to attempt this. This will involve creating numerous conditional statements to adjust timestamps based on the date and region. And what you’ll get in the end will only work for one timezone, keep that in mind. And yes, you’ll have to rewrite this bulk of calculations in each report that has timestamps to convert.
Okay, maybe, maintain a table that maps timestamps to specific time zones, considering the DST shifts? This table could contain historical offset data for different time zones across various periods of the year.
You are unlikely to find a ready-to-use dictionary in open sources. Most you can find is the Olson database which is considered a single source of truth for this kind of data. Manually compiling this data into a table can be labor-intensive. The necessity to maintain it and reflect future changes makes this endeavor even less appealing.
General conclusion
Handling DST changes accurately requires a nuanced understanding of the specific rules and historical changes in each region’s time zone. This can make automated solutions complex and will require periodic updates to account for any alterations in time zone rules.
But I have users all over the world and I need a solution which is both scalable and maintainable. What should I do?
The short answer is: don’t do timezone conversions with Power Query M functions.
Okay, so what should we use instead? I suggest below three solutions, probably from less preferred to more preferred, in my opinion. I am going to reveal them from different points of view so that you can decide what works best for you.
Connect to Time Zone APIs within Power BI
These APIs are renowned for their accuracy and reliability, consistently providing precise information on time zone offsets, historical changes, and daylight saving time shifts. Regular updates ensure that conversions remain current, maintaining report accuracy and relevance. The integration of APIs into Power BI through connectors or custom functions streamlines the conversion process, eliminating the need for manual maintenance of time zone tables.
But keep in mind the next several considerations. Primarily, there is a dependency on the availability and reliability of external services. Any downtime or issues with the API could disrupt data retrieval and conversions within Power BI. Additionally, certain APIs have usage limits or operate on subscription models, potentially leading to increased costs or restrictions upon exceeding usage thresholds. Reports heavily reliant on real-time API data might experience performance issues due to network latency or delays in API responses impacting overall report responsiveness. Moreover, using external APIs introduces potential security concerns, particularly regarding sensitive data transmission.
Use power of Python libraries in Power Query
Pandas’ robust timezone handling capabilities ensure accurate conversions, adeptly addressing concerns related to daylight saving time and historical shifts. Integrating Python scripts into Power Query extends its capabilities, enabling customized data transformations beyond native functions.
However, debugging Python scripts within Power Query might prove more challenging compared to native functions, potentially complicating troubleshooting processes. I mean, Power Query and Python interpreter are not so well integrated so far, so you’ll definitely need to use your favorite code editor for debugging. But that’s nothing. It seems like Microsoft doesn’t update those Python libraries really often. Basically, the risk of obsolescence of the conversion rules is low (as they are updated not very often), but nevertheless it exists.
Move timezone conversions to the source level
For example, PostgreSQL handles the conversion for you by taking into account the respective time zone offsets and daylight saving time changes between the source and destination timezones:
SELECT your_timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/London';Check if your DBMS is able to do this, but I’m more than sure it does. As a nice bonus, this approach optimizes performance by reducing computational loads within Power BI, leading to faster report rendering, particularly with extensive datasets. Consistency in data presentation is ensured across reports as all timestamps are pre-converted, simplifying report creation and reducing complexity in calculations within Power BI.
What are the drawbacks? One significant limitation is the loss of access to original UTC values within Power BI. This absence might hinder certain types of analysis or comparisons against a consistent time standard, impacting international comparisons or specific reporting requirements. But if your reports contain only data which doesn’t require to be aggregated over all the clients, you’re good to go. Otherwise, it’s probably a good idea to store both UTC and converted values for each timestamp, which could potentially increase storage needs. And of course make sure your DBMS is updated at least occasionally to keep up with the convertion rules.
Well, handling timezones in Power BI turns out to be no easy task. Hope I managed to expand your arsenal in tackling this challenge.
Don’t forget to subscribe to
and join our Power BI community
