M Query Guide

Transformations

M Replacing Values

Replacing Values

M replacing values uses Table.ReplaceValue for data cleaning.

Introduction to M Replacing Values

Replacing values is an essential transformation task in data processing that helps clean and standardize data sets. In Power Query M language, the Table.ReplaceValue function is used to replace existing values in a table with new ones. This function is particularly useful for data cleaning and preparation, ensuring consistency across your data.

Understanding Table.ReplaceValue

The Table.ReplaceValue function in Power Query allows you to specify the value to find, the value to replace it with, and the column(s) where the replacement should occur. The basic syntax is as follows:

Let's break down the parameters:

  • table: The table to perform the replacement on.
  • oldValue: The value you want to replace.
  • newValue: The value you wish to replace with.
  • replacer: A function that specifies how the replacement should be done, typically Replacer.ReplaceText or Replacer.ReplaceValue.
  • columns: A list of columns where the replacement should be applied.

Example: Replacing Text Values

Suppose you have a table containing a column named "Status" with values "Pending", "Completed", and "In Progress". You want to replace "Pending" with "Awaiting" to better reflect your business terminology. Here is how you can achieve this:

Example: Replacing Numeric Values

In another scenario, let's assume you have a table with a column "Quantity", and you want to replace all occurrences of 0 with Null to handle missing data appropriately. Here's how you can do it:

Conclusion

Using Table.ReplaceValue in Power Query M is a powerful way to ensure your data is clean, consistent, and ready for analysis. Whether you're dealing with text or numeric values, this function allows you to streamline your data transformation process efficiently. In the next post, you will learn about Transposing Tables in Power Query, which offers another layer of data manipulation capabilities.

Transformations

Next
Transposing Tables