1. Home
  2. Training Library
  3. Big Data
  4. Courses
  5. Loading Data into Power BI

Replacing Errors

Contents

keyboard_tab
Course Introduction
Troubleshooting in Power Query
Start course
Overview
Difficulty
Intermediate
Duration
40m
Students
62
Ratings
4/5
starstarstarstarstar-border
Description

In this course, we’ll review the Power BI Desktop interface. Then, we’ll show you how to load data into Power BI Desktop and how to save your file. We will also explain data profiling and look at the various data profiling options in Power Query like column quality, column value distribution and column profiling, and the benefits of using these.  

Lastly, we will look at how to resolve cell-level errors, empties, and inconsistencies in Power Query. This section will cover how to replace errors, replace values, remove rows, and how to identify the root cause of the error using Power Query. The demos in this course will provide you with practical examples that will help you to troubleshoot when encountering issues while loading data into Power BI.

Learning Objectives

  • Understand how to load data into Power BI and how to optimise functionality and size by using data profiling
  • Understand how to resolve errors, empties, and data inconsistencies in Power Query

Intended Audience

  • Anyone who would like to learn about importing data into Power BI
  • Anyone who needs to resolve cell-level errors or empties in a Power BI model or who would like to understand data profiling to improve the functionality of their model

Prerequisites

  • Some basic knowledge of, or experience in, working with large datasets
  • Some experience with Power BI (not mandatory)

Resources

The files used in this course can be found in the following GitHub repo: https://github.com/cloudacademy/loading-data-power-bi 

Transcript

In this example we have N/A errors included in the data set. You can follow along using the PowerBI NA.pbix file. The Excel source includes N/A errors. If you cannot fix the N/A errors in the Excel source, you could fix these in Power BI in the Power Query user interface. In Power Query, I have Column quality selected. This shows that there are three errors in the PromoGroups column of the PromoGroups query. You can check the error message behind the error by selecting the error cell. The error message will then show in the previous section at the bottom.

The error message states Invalid cell value '#N/A' For this example, let's assume we want all promo groups with errors to be classified as OTHER. To replace errors, you select the column PromoGroups, right-click the column header and select Replace Errors. Enter the value you want to replace errors with in the value box, in this case, I will make these OTHER, select OK. OTHER is now reflected in the PromoGroups column. And there are zero errors showing.

Note that you can only replace errors in one column at a time when using the user interface. For the next example, I will look at null values. I've opened a previous example we did, where we included empty values in the PromoGroups column. The empty cells were identified as null values when loaded into Power BI. I had saved this example as PowerBI Null.pbix and you can use this to follow along.

In Power Query, I have Column quality selected. And this shows that there are three empty cells in the PromoGgroups column. Right-click the PromoGroups column header, and select Replace Values, enter the value you want to replace in the Value To Find box. I want to find null values and I will replace this with OTHER again, select OK. You can see that OTHER is now reflected in the PromoGroups column, and there are zero empty cells. Another way to do this is to right-click the value in the column, and then select Replace Values. This way the Value To Find box will be pre-populated with the value of the cell you selected.

When you are dealing with a text column, as we are in this instance, there are additional advanced options available. The first is Match entire cell contents. If this is selected Power Query won't replace values where Replace with value is only part of the value to find value.

Next, we have the option to replace using special characters, this could be checked to insert special character codes, such as a carriage return, or a non-breaking space. Replace null with OTHER and press OK. When selecting replaced values, you do have the option to replace values in multiple columns at once, to do this you must select multiple columns before replacing values. I will return to the same original example, and select all columns, I hold down the CTRL key and select each column where I would like to replace values. You could also select the first column, hold down the SHIFT key, and then select the last column, and then all columns will be selected. Now, right-click and select Replace Values. Replace null with OTHER and press OK. This change will now be applied to all columns.

About the Author
Avatar
Bianca Burger
Chartered Accountant and Finance Business Partner
Students
63
Courses
1

Bianca is a chartered accountant and finance business partner who works with Power BI regularly to create useful, interactive dashboards to analyze financial metrics.  She has worked as a lecturer and as a financial analyst in FMCG companies assisting sales and marketing teams with reviewing and understanding their financial results and forecasts, and identifying risks and opportunities for improvement.  Bianca enjoys using technology to automate and simplify financial metrics.