Replacing Column Values That Don’t Match a String in Power Query

Replacing Column Values That Don’t Match a String in Power Query

This blog post provides a step-by-step guide for replacing column values that don’t match a string in Power Query. A comment inspired this blog post in our popular YouTube video, Replace Multiple Column Values in One Step [Power Query Tips & Tricks]. In the video we show Power Query users a way to quickly replace column values in one step using if then logic, but what if we want to replace values that do not match our logic?

Most users of Power Query are familiar with replacing specific column values, something we describe in our blog post [How To] Replace Column Values in Power Query, but what about replacing values that don’t match a set of criteria? In this blog post, we show you how to handle such a scenario using M code in Power Query. We do this through an example, follow along!

The example demonstrated in this blog post is from a sample database of clients. Want to follow along with our examples? Download the demo dataset and Power BI file (.pbix) below.

Download Files

Click to download demo data and Power BI file (.pbix)

The Problem: Replacing Non-Matching Countries

Let’s say we’re working with a dataset that provides the addresses for clients, and in the dataset, we observe various country names. Our analysis focuses only on North America, or three countries: Canada, United States, and Mexico. This means we may want to, in some instances, filter all other countries out of our data visualizations or, in other instances, we may want to compare the clients of these three countries against all other countries. So, we want to replace every other country in our dataset with a placeholder like “Other Countries”.

Performing this common data transformation manually or using traditional lookup methods would likely require a lot of time and effort, especially if the number of countries is large. However, with a little bit of smart M coding in Power Query we can automate this process.

The Scenario

  • We have a column called Country Name in our dataset
  • We want to keep only the countries Canada, United States, and Mexico
  • Any other country, such as France or Germany, should be replaced with Other Countries

The Solution: M Code for Contional Replacement

Power Query’s M coding language provides a flexible way to apply such data transformations, and in this case, the Table.ReplaceValue function will help us replace all non-matching values in one go. Here’s how we can write the code to achieve this:

// Duplicate existing Product Category column

#”Duplicated Country Name” = Table.DuplicateColumn(#”Changed Type”, “Country Name”, “Country Name Grouped”),

 

// Replace non-matching values in duplicated column of Product Category

#”Replaced Value” = Table.ReplaceValue(#”Duplicated Country Name”,

    each [Country Name Grouped],

    each if not List.Contains(

{“Canada”, “United States”, “Mexico”},

[Country Name Grouped])

    then “Other Countries”

    else [Country Name Grouped],

    Replacer.ReplaceText, {“Country Name Grouped”}

)

Let’s break this code down step by step to better understand how it works.

Step 1: Duplicate Country Name (Optional)

Before applying the transformation, we recommend duplicating the Country Name column to preserve the original column intact while making modifications to the duplicated column. In the M code above, this duplication is done using the Table.DuplicateColumn function, or it can also be done directly using Power Query’s user interface.

Now, that we have both the Country Name and Country Name Grouped columns in our dataset, we are ready to re-categorize the country names using the Country Name Grouped column.

Step 2: Replace Values with Conditional Logic

The Table.ReplaceValue function allows us to specify conditional logic for replacing values. This is really the heart of the solution, so let’s break it down:

      1. ReplaceValue: This is the main function that performs the value replacement in a column.
      2. each [Country Name Grouped]: This tells Power Query to look at the values in the Country Name Grouped
      3. each if not List.Contains({“Canada”, “United States”, “Mexico”}, [Country Name Grouped]): This is the conditional statement that checks if the value in the Country Name Grouped column is not one of the countries we want to keep—specifically Canada, United States or Mexico.
      4. then “Other Countries“: If the condition is met (i.e., the country name is not “Canada”, “United States”, or “Mexico”), the value in that row is replaced with “Other Countries”.
      5. else [Country Name Grouped]: If the condition is false (i.e., the country name is “Canada”, “United States”, or “Mexico”), the original value is retained.
      6. ReplaceText: This tells Power Query that the replacement action will involve text values.
      7. {“Country Name Grouped”}: Finally, this specifies that the replacement should happen in the Country Name Grouped column.

Why This Approach Works

This method is not only straightforward but also highly adaptable. By using List.Contains, we ensure that we can easily modify the code to handle more countries in the future if necessary. For example, if we later decide to add another country, say France, it is simple to update the list:

List.Contains({“Canada”, “United States”, “Mexico”, “France”}, [Country Name Grouped])

The conditional logic handles all the replacements in a single, efficient step, eliminating the need to write multiple lines of code for each category. This makes the solution scalable and easy to maintain, even with large datasets.

Wrapping It Up...

When it comes to data transformations, a little bit of M code can go a long way in Power Query. In this blog post, we tackled a common data transforfmation scenario – replacing values that don’t match a specific list of strings. Using the Table.ReplaceValue function with conditional logic, we were able to streamline the process and handle it in one efficient step.

The process highlighted in this blog post is not only simple and efficient, but it is also adaptable and scalable. The M code provided here can be easily adjusted to incorporate more categories to check against in the logic and can scale with larger datasets.

Try it out on your projects and let us know how it went! Feel free to leave a comment or question below if you need more help.

Need Help Getting Started With Power BI?

Our Microsoft Certified consultants can help with the implementation of Power BI in your organization

Book a meeting

Leave a Reply