How to deal with null in Power Query

One of the common Data Preparation questions Power BI and Excel Users ask is how to deal with null in Power Query. Do you delete them or replace them with zero?

There is no straight answer to the question.

First, there are several things that can be done about the null values.

Secondly, there are several factors responsible for having null values in your query.

And lastly, there is the issue of how the null values impact your final solution (reports, dashboards).

As you know already, Power Query accepts different types of values like primitive, lists, records, tables, and functions. When the M language is unable to evaluate the content of a cell into any of these types, you get a null. In lay man’s terms, when a cell is empty, it evaluates to null.

The first thing you must do before dealing with the null data is to determine why you have the null there in the first place and if you need to act on it.

Here are possible actions you can take to deal with null in Power Query

• Leave as is. Do not tamper with it.
• Enter values in the cells from the Source
• Delete the row with null
• Fill it with a value(s) in Power Query
• Replace with value(s) in Power Query
• Complete missing data with Python / R Script

Let’s consider when to use each of the following:

Leave as is

If the column with the null value is a column that optionally allows data entry/input, and leaving the affected cells empty is the right thing, then you have no choice.

For example, you have First Name, Last Name and Middle Name fields in your data. You will surely have a lot of nulls in the Middle Name column and there is nothing you can do about it. So, You’ll just leave it as is.

Enter values in the cells from Source

Using the same example as above, what if there are some empty cells in the First Name Column? That means you need to investigate the reason for that. If it comes down to error or omission from the original data entry, then the best way is to correct the error/omission from source.

Delete the row(s) with the null.

There are two popular scenarios here. For both scenarios, you can find a way to filter out the row that has null from the data column.

Scenario 1

Sometimes, null appear on some columns because only some of the columns have data in some rows and some do not. For Example, you have totals in one or some of the columns in your data.

Scenario 2

The entire row is just empty. Something you will see very well when you connect to a flat file.

Fill it with Values in Power Query

If you have empty cells because one of the values has been entered in prior cells or later cells and it is being used to Identify the group, you can use the Fill option in Power Query to Fill up or Fill Down.

Replace with Values in Power Query

If all the empty cells in a column are supposed to have the same value that is known to you, then you can use the Replace Value feature in Power Query to replace null with the correct value.

Sometimes, we don’t want to leave some empty cells and we usually just replace nulls with “NA” to indicate absence of entry on a text column.

Some other times, we don’t want to leave a values column as empty and will like to record the value as 0 (zero) rather than empty.

Complete missing data with Python or R Script.

If you are working on building a machine learning model for example, and will like to auto complete missing values in your data, you can use R or Python Script buttons in Power Query.

Check out the two article below to see how to complete this.

Python In Power Query

R In Power Query

Conclusion

At the end of the day, dealing with null values in Power Query demands investigation. The first things you need to figure out are:

Does this column allow null? You may have to leave it like that.

Can I load this data with the null? 

Do I know what is missing?

Should I replace the empty cells with something?

You can read about how Power Query works generally here.

You can check out a free Data Cleaning Sample course here