Decimal Display in Power Query

Displaying Decimal Values in Power Query

A common hurdle in Power Query involves the display of decimal values. Imagine having a decimal number like 1.10, but Power Query insists on displaying it as 1.1. This can be inconvenient when you wish to maintain the same formatting for your data.

Custom Formatting

To ensure precise display of your decimal values in Power Query, you can employ a custom formatting script. This script not only retains decimal zeros but also allows you to manipulate them as standard decimal numbers, with a minor issue.

How It’s Done?

In my example, there is a column named “amount” with values stored in decimal format. What I Want is to display additional zeros so it will appear as -28.80 instead of -28.8 by making these steps:

1. Begin by adding a new column and selecting “Custom Column.” Then, input the following code and click “OK”:

This code employs the Number.ToText function, which converts the numerical value represented by column [amount] into a text string with exactly two decimal places of precision.

The Output:

Power Query vs. Excel Distinction

It’s crucial to distinguish that while you can display decimal zeros in Power Query, you can’t treat them as decimal types within Power Query itself. Nevertheless, this limitation doesn’t apply when you load your data into Excel.

Excel’s Decimal Handling

In Excel, you can work with these custom-formatted decimal values just like any other decimal number. This means you can perform calculations, create charts, and more without any issues. Let’s load our example.

At first glance, they may appear to be the same data type, and the addition column which threw error does not show anything.

In Power Query, it was a text type (decimals_zero_display). Let’s see what happens when we add it with the amount column. Will it throw a #VALUE error? What do you think? Well, it works perfectly fine.

Have you noticed what changed? Yes, it now displays only one decimal point. Unfortunately, but what if we add a number with the same decimal point? Yet it will work perfectly fine displaying two decimals points again.

Conclusion

While it’s just a small trick, the most reliable way to display a decimal number in Excel is to set it up manually. Power Query is a powerful tool for data transformation, and with a bit of scripting, you can ensure your decimal values display correctly while retaining their functionality. Remember that while Power Query excels at data preparation, Excel provides a robust environment for in-depth analysis.