Incorrect Power BI Report Data After Refresh

You just spent a lot of time designing your report and now it’s time to upload it to the Power BI Service. After setting up the credentials for the data source, you hit refresh to make sure the data can be pulled from the source. The refresh completes without error and you switch over to the report. But wait, these numbers are incorrect and not at all what you see in Power BI Desktop. How could this be? It may be because of different locale settings between Power BI Desktop and the Power BI Service. Let me explain with an example. [Click here if you want to jump directly to the solution]

---

The Problem

To illustrate the issue I created a .csv File with the following content:

The first column indicates which decimal separator was used to write the number “0.05”, the value of the number is then written in the “Number” column and in the column “String” the literal representation of the number in the file is provided.

When loading this .csv file locally in Power BI Desktop, everything works as expected.

Now I publish the report to the Power BI Service and refresh the data from the .csv file. After the refresh, the data in the Power BI Service is displayed incorrectly. We expected the same value of 0.05 as indicated in the column “String” but instead, we get the number 5 formatted with two zeros after the decimal separator.

This is not just a formatting problem! The data in the Power BI Service is way off, in this example by a factor of 100. So what happened exactly and how can you fix it?

---

The Context

In the simple example, it is easy to spot: Somehow Power BI changed something about the interpretation of the decimal separator. But why does everything work locally and break in the Service? The answer is a different locale setting in Power BI Desktop and Service.

– Power BI Locale Settings

Check the language setting in Power BI located at “File > Options and settings > Options > Current File > Regional Settings”

This setting influences how your data is interpreted. After creating a new report, the locale is initially configured and is influenced by the settings on your current machine.

– Power BI Service

Depending on which language you selected in the Power BI Service in “Settings > Language”, data is formatted differently to match the usual format of your language. But this only changes how data is displayed, not the actual data itself. Your Power BI Tenant has a physical location and you can check where your Power BI Data is stored currently. Check this in the Power BI Service and click on “Help & Support > About Power BI”. In the subsequent window, your data location is displayed.

At this point you might ask:

So if i change the locale to something similar as the location of my power bi tenant everything works, right?

Changing your local setting can help Power BI Desktop to behave identically to the Power BI Service, provided you find the correct locale, and this is something you should absolutely do. After changing the locale and refreshing the data in Power BI Desktop, you can expect to have the same incorrect data as in the Service. Still wrong, but at least consistently wrong:

This means the actual problem is a mismatch between the source data and the way Power BI tries to interpret the data when loading it into the report. How the data is provided is dependent on the source system and often this can’t be changed. Here is a way to fix it directly when loading the data in Power BI:

---

The Solution

In Power BI Desktop:

1. Go to the Power Query Editor
2. Right click on your column and Select “Change Type > Using Locale…”
3. Provide the data type and the matching locale setting to convert your data reliably
4. Quit Power Query Editor and refresh the data
5. Ensure the data was loaded correctly in the local report
6. Upload the report to Power BI Service
7. Refresh data in Power BI Service
8. Ensure the data was loaded correctly in the online report