In Excel’s Power Query, blank values are treated as nulls to signify the absence of data or to indicate an unknown, missing, or inapplicable value. This approach is consistent with database and programming standards, enhancing the precision of data processing, analysis, and integration with various data systems.
By default, Power Query looks for empty values and codes them as nulls. However, sometimes values that look blank, actually aren’t, and Power Query has an issue making this conversion. Let’s take a look using this penguins csv file found on GitHub:
https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv
To import the dataset into Power Query, navigate to Data > Get Data > From File > From Text/CSV, and enter the GitHub URL.
You’ll see a data preview. In the fourth row, most missing values are correctly marked as null, except for the ‘sex’ column, which inexplicably shows blanks. There are a few more blank values visible in the Power Query preview:
This issue arises because the dataset uses empty strings instead of true blanks, which Power Query interprets as valid, zero-length string data rather than a data absence.
The use of capital letters in the sex column is also somewhat jarring compared to other columns, suggesting a different coding approach. To rectify this, let’s replace these values.
Select the sex column, then go to Replace Values on the Home tab of the ribbon:
Leave the first field blank and set the second field to null:
Upon confirming, these values will display in italics, distinguishing them as a reserved keyword:
For consistency, I’ll also convert this column to proper case. To do that, right click on the column and select Transform > Capitalize Each Word:
Once done, close and load the results back to Power Query:
To the untrained eye, our modifications might seem trivial, but they significantly enhance data handling in Power Query.
Did you manage to follow these steps? Compare your file with mine to see the difference.
If you have any questions about working with null values, replacing values, or other Power Query features, feel free to ask in the comments.
Leave a Reply