top of page

You are learning Power Query in MS Excel

How to handle missing values (nulls) in Power Query?

There are several ways to handle missing values (nulls) in Power Query depending on your desired outcome:

1. Leaving them as is:

* This might be suitable if you plan to address nulls later in your data analysis or if they represent a valid state in your data.

2. Replacing nulls with a specific value:

* Use the "Replace Values" function. Select the column with nulls, go to "Transform" > "Replace Values." Choose "Replace null values" and enter the value you want to replace them with (e.g., 0, blank text "", or a specific code like "NA").

3. Removing rows with nulls:

* In the "Home" tab, select the column with nulls. Go to "Transform" > "Remove Rows" and choose "Remove rows with errors" or filter for nulls and then delete the filtered rows.

4. Filling nulls with values based on surrounding data:

* Use the "Fill" function. Select the column with nulls, go to "Transform" > "Fill." Choose either "Forward Fill" to copy the value from the previous row or "Backward Fill" to copy the value from the next row.

5. Filling nulls with an average/median/other statistical value:

* Consider using M code for more advanced options. You can replace nulls with the average or median of the column using functions like `Average` or `Median`.

6. Filtering or grouping based on nulls:

* You can filter or group your data to separate rows with and without nulls for further analysis.

Choosing the best method depends on your specific data and analysis goals. Consider the meaning of nulls in your context and how you want them reflected in your final results.

bottom of page