Power BI Practice: Handling Nonnumeric Values in Power BI's Table Columns

Scenario:

You have a query for a table named Sales. Sales has a column named CustomerlD. The Data Type of CustomerlD is Whole Number. You refresh the data and find several errors. You discover that new entries in the Sales table contain nonnumeric values. You need to ensure that nonnumeric values in the CustomerlD column are set to 0.

Solution: From Query Editor, select the CustomerlD column and click Remove Errors. Does this meet the goal?

No, this solution does not meet the goal. Clicking "Remove Errors" in the Query Editor will remove the rows containing errors, but it will not replace nonnumeric values with 0.

To achieve the goal of setting nonnumeric values in the CustomerID column to 0, you should use the "Replace Errors" option in the Query Editor.

Here’s how you can do it:

1. Open the Query Editor.

2. Select the CustomerID column.

3. Go to the "Transform" tab. 4. Click on "Replace Errors".

4. Enter 0 as the value to replace errors with.

This will ensure that any nonnumeric values in the CustomerID column are replaced with 0, rather than removing the rows entirely.

Practical Exercise:

Handling Nonnumeric Values in a CustomerID Column in Power BI

Objective: To practice replacing nonnumeric values in the CustomerID column with 0 using Power BI's Query Editor.

Steps:

1. Open Power BI Desktop:

• Launch Power BI Desktop on your computer.

2. Load Data:

• Click on "Get Data" and select the source of your Sales table (e.g., Excel, SQL Server).

• Load the Sales table into Power BI.

3. Open Query Editor:

• Click on "Transform Data" to open the Power Query Editor.

4. Select the CustomerID Column:

• In the Query Editor, select the Sales table from the left pane.

• Click on the CustomerID column to highlight it.

5. Replace Errors:

• Go to the "Transform" tab in the ribbon.

• Click on "Replace Errors".

• In the dialog box that appears, enter 0 as the value to replace errors with.

• Click "OK".

6. Apply Changes:

• Click on "Close & Apply" to apply the changes and return to the main Power BI window.

7. Verify the Data:

• Check the Sales table to ensure that all nonnumeric values in the CustomerID column have been replaced with 0.

8. Document the Process:

• Write a brief report documenting the steps you took to handle nonnumeric values.

• Include screenshots of key steps, such as selecting the CustomerID column and replacing errors.

9. Expected Outcome:

All nonnumeric values in the CustomerID column should be replaced with 0, ensuring that the column contains only numeric values.

Give it a try!

A sample table you can use to practice the steps for handling nonnumeric values in the CustomerID column:

In this table, the CustomerID column contains some nonnumeric values (e.g., "ABC" and "XYZ"). You can use this table to follow the steps in the practical exercise and replace those nonnumeric values with 0.