How to replace blank cells with text in Power BI?

by

How can you replace these blank values with the string “Unknown” to ensure all records are accounted for in your regional sales analysis?

Replace empty cells with string in Power BI

In Power BI, we can easily handle blank values using both Power Query and DAX. We will explore both methods using a fictitious ‘Customers’ table with the following columns: CustomerID (Number), Name (Text), City (Text), and TotalPurchases (Currency).

Method 1: Using Power Query

  1. In Power BI Desktop, click ‘Transform data’ on the Home tab.
  2. In Power Query Editor, select the ‘City’ column.
  3. Go to ‘Transform’ tab and click ‘Replace Values’.
  4. In the dialog box, leave ‘Value To Find’ empty and enter “Unknown” for ‘Replace With’.
  5. Click ‘OK’ to apply the changes.

M code for this operation:

= Table.ReplaceValue(Customers, null, "Unknown", Replacer.ReplaceValue, {"City"})

Method 2: Using DAX Calculated Columns

  1. In Power BI Desktop, go to the ‘Data’ view.
  2. Select the ‘Customers’ table.
  3. Click ‘New Column’ and enter the following DAX formula:
City (Updated) = IF(ISBLANK(Customers[City]), "Unknown", Customers[City])

Real world application example

For a more nuanced approach, you might want to distinguish between truly unknown cities and those you can infer from other data. For instance, if you have a ‘State’ column, you could create a more informative replacement:

City (Detailed) = 
VAR StateKnown = NOT(ISBLANK(Customers[State]))
RETURN
    IF(ISBLANK(Customers[City]),
        IF(StateKnown, "Unknown City in " & Customers[State], "Completely Unknown"),
        Customers[City]
    )

This formula creates three categories: known cities, unknown cities in known states, and completely unknown locations. This granularity can provide more accurate insights for your sales team.

Handling Duplicates

When replacing blanks, be cautious of creating unintended duplicates. For example, if you have two blank cities in the same state, they’ll both become “Unknown City in [State]”. To mitigate this, consider adding a unique identifier:

City (Unique) = 
VAR BlankCity = ISBLANK(Customers[City])
VAR UniqueID = IF(BlankCity, "Unknown_" & Customers[CustomerID], Customers[City])
RETURN UniqueID

This ensures each replaced value is unique, preventing false aggregations in your visuals.

In case of errors

  • If replacements don’t occur, check for hidden spaces using TRIM().
  • For large datasets, consider using Power Query for better performance.
  • If dealing with actual null values instead of empty strings, use null instead of “” in Power Query’s ‘Value To Find’ field.
  • Remember, while replacing blanks improves analysis, it’s crucial to maintain data integrity. Always keep the original data and create new columns for cleaned data. This allows you to trace back to the source if needed and provides flexibility in how you handle missing information in different contexts.