Introduction
Power Query is a versatile tool found in both Power BI and Excel, facilitating data transformation and manipulation. Despite its efficiency in numerous data tasks, managing decimal values can sometimes pose challenges. Recently, I encountered an issue related to displaying decimal numbers, particularly those ending with zero. In this blog post, I’ll guide you through effectively handling decimals in Power Query, with a focus on displaying decimal values as desired.
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:
- 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.
I work as a data engineer at Joyful Craftsmen, where my true passion lies in the realm of data. Joyful Craftsmen allows me to continuously develop my skills, leading to steady growth. I particularly enjoy working with tools like Power BI and have recently become interested in PowerShell.
