Part V: Understanding Power Pivot and Power Query
Filling in blank fields
It’s important to note that there are actually two kinds of blank values: null and empty
string. A null is essentially a numerical value of nothing, while an empty string is equiva-
lent to entering two quotes ("") in a cell.
Blank fields are not necessarily a bad thing, but an excessive number of blanks in your data
can lead to unexpected problems when it comes time to analyze your data.
Your job is to decide whether to leave the blanks in your data set or fill them in with an
actual value. When deciding this, you should consider the following best practices:
■ Use blanks sparingly: Working with a data set is a much less daunting task when
you don’t have to test for blank values constantly.
■ Use alternatives when possible: A good practice is to represent missing values
with some logical missing value code whenever possible.
■ Never use null values in number fields: Use zeros instead of null in a currency or
number field that will be used in calculations.
Power Query will show the word null for any null value in your data. Replacing the null
values is as simple as selecting the column or columns that you want to fix, right-clicking,
and then selecting the Replace Values command.
The Replace Values dialog box shown in Figure 39.4 will activate. The key here is to enter
the word null as the Value to Find value. You can then enter the value that you want to
use instead. In this case, you can enter 0 as the Replace With value.
FIGURE 39.4
Replacing null
Filling in empty strings
It’s always a best practice to represent missing values in a field with some logical value code
whenever possible. For example, in Figure 39.5, we want to tag any record with a missing
title in the ContactTitle field with the word Undefined.
