Can You Trust Your Choices with Unreliable Data?

“Can You Trust Your Choices with Unreliable Data?”

Think of it like this: In the realm of data, where every decision counts, how much can you trust your choices when your data's reliability is in question? Your decisions are only as good as the data you use. If that data isn't dependable, can you be confident in the conclusions and actions it drives? 🤔

In our latest YouTube Community post, we posed a question: "What will Power Query return when you add the values in these columns together: 4532 + null?" The correct answer might have surprised you—it's null! And today we're about to demystify this, all in the name of data integrity!

Click here to see that community post

Null values in Power Query can have a significant impact on your calculations. When you add or perform any mathematical operation involving null values, the result will be null.

It's like a mathematical magic trick, where one plus one might not always equal two and understanding this behaviour is crucial for accurate data processing.

So, What Causes This?

• Power Query follows a convention known as "null propagation." It's a fancy term for a fundamental concept. When you perform an operation between two values, and one of them is null, the result will be null.

• Null values show that a cell is empty or doesn't have any data in it.

• But null isn't really a value at all. Unlike zero, which is a real number, null in Power Query is just a way of saying, "There's nothing in this spot."

• This is because the presence of a null value in an operation's input can potentially lead to an unknown or undefined result. You can't add a specific number to an unknown value (null).

• Therefore, Power Query plays it safe and returns a null to indicate that the result is undetermined. It’s basically saying, “I can’t figure this out.”

Embracing Data Integrity:

But…. this behaviour is in line with a principle of data integrity and helps prevent unintended results in your data transformations.

It's important to be aware of this behaviour when working with data in Power Query and handle null values as needed, depending on your specific use case.

To avoid null-related surprises, here's a pro tip: Replace null values with 0. This small but powerful step can make a big difference in your data transformations.

1. Right-click on the column that contains null values

2. Scroll down to “Replace Values” and click on it

3. In the “Replace Values” dialog box (please see image below):

   • enter null in the “Value to Find” field.

   • enter 0 in the “Replace With” field.

   • click “OK” to replace all null values in the column.

4. Ensure that your column data type is of type Number

Replace Values Dialogue Box:

Thanks for reading!

P.S. If you have any specific topics you would like me to delve deeper into, I’d love to hear them.