Power Query Precision: Avoid Rounding Errors

Are you having trouble getting accurate results when doing calculations in Power Query? Do you add up 0.1 + 0.1 + 0.1 and end up with 0.30000000000000004 instead of 0.3? This is a common problem, and there’s a reason for it.

In this article, we will explain what causes this problem and how you can fix it. The key is understanding how Power Query handles precision in calculations.

Table of contents

• 1. The Problem
• 2. How Computers Store Numbers
• 3. Why Do We Get Differences?
• 4. Power Query and Numbers
  • 4.1. Double-precision
  • 4.2. Decimal-precision
  • 4.3. Why Are Only Some Numbers Wrong?
• 5. Handling Precision in Power Query
  • 5.1. Precision Type
  • 5.2. Rounding Numbers
• 6. Conclusion

1. The Problem

Imagine you start a sequence that starts at 0 and should end at 0.3. And in each step, you increment with 0.1. You would expect Power Query to give you a list of numbers that looks like this: 0, 0.1, 0.2, 0.3. But when you use a Power Query formula, you might be surprised. The list of numbers actually looks like this: 0, 0.1, 0.2.

Let’s generate this series with List.Generate. The result you get is:

= List.Generate(
     () => 0,        // Start with 0
     each _ <= 0.3,  // As long as values are <= to 0.3
     each _ + 0.1)   // add 0.1 in each step
// Returns { 0, 0.1, 0.2 }

You would expect Power Query to return 0.3 as the last value. Why is this happening?

Let’s take a closer look by counting up to 0.4 instead of 0.3. We will also change the numbers into text so we can see the details more clearly.

= List.Generate(
     () => 0,                // Start with 0
     each _ <= 0.4,          // As long as values are <= to 0.4
     each _ + 0.1,           // add 0.1 in each step
     each Text.From( _ ) )   // Turn number into text value
// Returns { 0, 0.1, 0.2, 0.30000000000000004, 0.4 }

You get a list of numbers that looks like this: 0, 0.1, 0.2, 0.30000000000000004, 0.4. Can you see the problem? The third number on the list is not exactly 0.3, it is 0.30000000000000004. This is because of the way Power Query handles precision in calculations. Let’s delve into how computers handle precision.

2. How Computers Store Numbers

Computers use a special way to store numbers called binary floating point. This system helps the computer understand and calculate very big and very small numbers quickly. Systems sometimes need to know the distance between galaxies and, other times, the space between atoms. For different use cases, chosen systems have different precision.

Consider this:

• When you work in construction, it does not matter whether an apartment building it is 80m or 80.00001m high.
• A computer chip designer does not care for measurements bigger than 1 meter, but the precision of 0.0001 meters makes a big difference to this designer.

The result of this dynamic range is that the numbers that can be represented are not uniformly spaced. The fact that floating-point numbers cannot precisely represent all real numbers leads to many surprising situations. This is related to the finite precision with which computers generally represent numbers.

3. Why Do We Get Differences?

It’s because computers can’t always store fractions like 1/3 with precision. Instead, they have to use decimal numbers like 0.33 or 0.3334. But, even with these decimal numbers, if you multiply them by 3, they will not always add up to 1.

To avoid these problems, computers use floating-point numbers that have a limited number of digits. These numbers are faster; in our example, the rounding error to the 17th decimal usually does not make a big difference.”

4. Power Query and Numbers

Power Query uses two ways to store numbers: Double-precision and Decimal-precision. Both ways round numbers to the nearest value each system can understand. This means that sometimes, there might be small mistakes before any calculations happen.

4.1. Double-precision

Power Query’s default way to store numbers is called Double-precision. It uses a special way to write fractional values. This system can handle bigger numbers than Decimal-precision. But, it can’t always write decimal numbers like $0.30 precisely.

4.2. Decimal-precision

The other way Power Query stores numbers is called Decimal-precision. This system stores numbers in a different way, which can handle a wider range of values and can write numbers like $0.30 precisely. This is different from Double-precision.

4.3. Why Are Only Some Numbers Wrong?

Sometimes, the precisions of numbers shown in Power Query seem correct, but others appear wrong. How come?

This has to do with the floating-point number and can have two explanations.

1. Sometimes, the rounding errors in the input numbers can cancel each other out. This means that one rounding error is positive and the other is negative, so they balance each other.
2. Another reason is that the number shown is not exactly 0.2, but it is very close to 0.2. The computer system chooses the closest possible number to 0.2 that it can use and shows that instead of a more exact number.

5. Handling Precision in Power Query

So Power Query uses a system to store numbers that round numbers to the nearest value that it can understand. This can cause small rounding errors, even before any calculations happen.

So, our issue is with the Double-Precision implementation. But there is a way to make sure your numbers are more accurate.

5.1. Precision Type

To return the same calculation with more precision, you can provide a formula with a Precision Type.

The previous formula shown returns the same results as:

= List.Generate(
     () => 0, 
     each _ <= 0.4, 
     each Value.Add(_, 0.1, Precision.Double ), 
     each Text.From(_) )
// Returns { 0, 0.1, 0.2, 0.30000000000000004, 4 }

The difference is that this example makes use of the Value.Add function. The benefit of using this function is that it has an optional third argument where you can specify the Precision for your calculation. By default, it makes use of the Precision.Double type, but you can provide it with Precision.Decimal.

= List.Generate(
     () => 0, 
     each _ <= 0.4, 
     each Value.Add(_, 0.1, Precision.Decimal), 
     each Text.From(_) )
// Returns { 0, 0.1, 0.2, 0.3, 0.4 }

And the Decimal Precision can store numbers with much more detail. In this case, that results in the correct numbers.

Other Value functions that support the Precision Type parameter are Value.Subtract, Value.Multiply,Value.Divide, Value.Compare, Value.Equals. But you also find the precision Parameter in the list functions List.Sum, List.Product and List.Average.

5.2. Rounding Numbers

Alternatively, instead of using the precision parameter, you can go for a different approach. By using a rounding function like Number.Round, you can return the closest single decimal value.

= List.Generate(
     () => 0, 
     each _ <= 0.4, 
     each Number.Round( _ + 0.1, 1 ), 
     each Text.From(_) )
// Returns { 0, 0.1, 0.2, 0.3, 0.4 }

For this example, that worked out, but be careful with rounding. You may end up rounding numbers to a value you’re not looking for. Choose carefully.

6. Conclusion

In conclusion, when working with numbers in Power Query, it’s important to know that the program uses a Double-precision floating point system by default. This can cause small rounding errors even before any calculations happen. But, you can provide the Precision Type Precision.Decimal or use a rounding function, to make sure the numbers are as accurate as you need them to be.

Remember, the precision you choose will depend on the type of numbers you’re working with and what you’re using them for. Now, you can go ahead and make your calculations with confidence!

Happy querying!