As you delve deeper into the realm of data-driven decision-making, it becomes crucial to present data in a visually appealing and easily understandable format. One fundamental aspect of data presentation is the effective use of number formatting. Adding a thousand separators is a simple yet significant technique that enhances readability and comprehension.

While it may seem like a straightforward task, the significance of using correct symbols for separating numbers in Power BI cannot be underestimated. Incorrectly formatted data can lead to misinterpretation and inaccurate reporting, undermining the very purpose of data analysis.

Among the various formatting techniques, the inclusion of thousand separators holds particular importance. It is worth noting that different regions adopt distinct symbols to represent thousands, further adding to the complexity.

Commas and points are the most used symbols for separating thousands, but their usage varies across different regions. Some regions employ commas as thousand separators and points for decimals, while others reverse this convention. Recognizing and accounting for these subtle differences is essential when working with data from diverse geographical sources.

In this blog post, you will learn the nuances of number formatting in Power BI, focusing specifically on the proper usage of thousand separators.

Use Normal Comma Thousand Separator in Power Query

The typical way to separate thousands in numbers is by using commas, while decimal points represent the decimal portion. This is the standard setup commonly used in Power BI and Power Query by default. But what if your setup doesn’t follow this convention and you prefer to use the familiar comma as the thousand separator? Well, good news! Adjusting this in Power Query is a breeze, allowing you to seamlessly incorporate the normal comma thousand separators into your data formatting.

When you load data into Power Query, it uses three steps which you can see in the APPLIED STEPS pane. The Changed Type step shows Power Query trying to figure out the appropriate datatype for each column in the dataset.

To apply the appropriate format, Power Query uses the Table.TransformColumnTpes M function which you can see in the formula bar.

The magic of this function lies in its ability to transform column data types, and it does so with the help of three handy parameters. Now, the function doesn’t necessarily need all three parameters to work.

While the first two parameters are compulsory, the third one, known as “culture,” is completely optional. Since it can work with only these two parameters, Power Query is happy to skip the culture parameter.

If you wish to use the standard thousand separators, you can include them by adding a comma and “en-US” at the end of the syntax.

This culture parameter helps Power Query recognize and apply the desired formatting for thousand separators, ensuring consistency and accuracy in your data representation. By adding this parameter to the syntax, you can effortlessly leverage the appropriate thousand separators in your Power Query transformations and enhance the clarity of your data.

If you ever find yourself in a situation where you need to apply a thousand separators from different cultures, you can refer to this comprehensive list of abbreviations for various cultures which you can add to the Table.TransformColumnTypes syntax.

What If you’re looking for a hassle-free method to adjust the culture settings in Power Query? Then the Using Locale approach is your go-to solution. With no coding required and a user-friendly interface, it simplifies the process. Follow these easy steps to implement this approach:

1. Right-click on the column name and select Change Type, then Using Locale in the contextual menus.

2. Choose a Data Type and Locale from the Change Type with Locale window. This window provides a sample view of how the numbers will display based on the selected locale which is beneficial. Click on the OK button to implement the changes.

Use Normal Thousand Separator in Power BI Interface

Power BI window. It’s a breeze! All you need to do is tweak the regional settings. Ready to make the change? Just follow these simple steps:

1. Click on the File menu.

2. Select Options and settings, then click on Options.

3. In the Options window, go to the CURRENT FILE section and click on Regional Settings. In the Local for import tab that opens on the right, select the locale that suits your needs.

Add Thousand Separator from Report View

Adding a thousand separator from the Report View in Power BI is a simple process. To begin, ensure that the column you wish to format is a numeric column, as only numeric columns offer the option to add a thousand separator. Additionally, the specific formatting of the separator will be determined by the current locale setting in Power BI. When you take note of these, adding a thousand separator from the Report View in Power BI is as simple as following these steps.

1. Select the column you want to add thousand separators from the Data field.
2. Click on the comma icon in the Column tools pane.

Conclusion

In the world of data visualization, attention to detail can make a significant difference in the effectiveness of conveying information. Adding a thousand separators to your Power BI reports may seem like a small adjustment, but its impact on data comprehension and the visual appeal is substantial.

By following the simple steps outlined in this blog post, you can effortlessly incorporate a thousand separators into your Power BI visualizations, enhancing readability and ensuring accurate data representation. Remember to consider regional variations in symbol usage, such as commas and points, to accommodate diverse audiences.