How to Use PowerQuery to Replace Specific Values in a Column

PowerQuery is a feature in Excel that allows you to connect, transform, and analyze data from various sources. You can use PowerQuery to perform various transformations on your data, such as filtering, grouping, pivoting, merging, and more. One of the common transformations is to replace certain values in a column based on some criteria, while leaving other values unchanged. For example, you may want to replace commas with decimals in a column that contains numbers in different formats, or you may want to replace blank values with a default value in a column that has missing data.

To replace certain values but not all in a column, you can use the Table.ReplaceValue function in PowerQuery. This function takes five arguments:

• table: the table that contains the column you want to transform
• oldValue: the value you want to replace
• newValue: the value you want to use instead of the oldValue
• replacer: a function that determines how to replace the values
• columnsToSearch: a list of columns that you want to apply the transformation to

The replacer argument can be one of the predefined functions in PowerQuery, such as Replacer.ReplaceText, Replacer.ReplaceValue, or Replacer.ReplaceNull. Alternatively, you can use a custom function that takes two arguments: the current value and the old value, and returns the new value. For example, you can use a custom function that checks if the current value matches a certain condition, and then replaces it with the new value, otherwise returns the current value unchanged.

Procedures

To use the Table.ReplaceValue function in PowerQuery, you need to follow these steps:

1. Load your data into PowerQuery by clicking on Data > Get Data > From File/From Web/From Database etc.
2. Select the column that you want to transform, and click on Transform > Replace Values.
3. In the Replace Values dialog box, enter the oldValue and the newValue that you want to use, and click OK. This will create a step in the query that uses the Replacer.ReplaceValue function by default.
4. If you want to use a different replacer function, or a custom function, you can modify the code in the formula bar or the advanced editor. For example, you can change the replacer function to Replacer.ReplaceText if you want to replace text values, or you can use a custom function that checks the condition of the current value, such as each if Text.Upper(_) = "X" then "1" else "-1".
5. If you want to apply the transformation to multiple columns, you can change the columnsToSearch argument to a list of column names, such as {“Column1”, “Column2”, “Column3”}.
6. Click on Close & Load to load the transformed data into Excel.

Comprehensive explanation

To illustrate how to use the Table.ReplaceValue function in PowerQuery, let’s use an example scenario. Suppose you have a table that contains sales data from different regions, and you want to transform the Unit Price column, which has numbers in different formats. Some numbers use commas as decimal separators, such as 1,88, while others use periods, such as 1.88. You want to replace the commas with periods, so that all the numbers have the same format, and you can perform calculations on them. However, you don’t want to replace the commas that are used as thousands separators, such as 1,000.00. Here is how you can do it using PowerQuery:

1. Load the table into PowerQuery by clicking on Data > Get Data > From Table/Range.
2. Select the Unit Price column, and click on Transform > Replace Values.
3. In the Replace Values dialog box, enter “,” as the oldValue and “.” as the newValue, and click OK. This will create a step in the query that uses the Replacer.ReplaceValue function by default, and replaces all the commas with periods in the Unit Price column.
4. However, this will also replace the commas that are used as thousands separators, which is not what we want. To fix this, we need to use a custom function that only replaces the commas that are used as decimal separators. To do this, we can modify the code in the formula bar or the advanced editor. We can use the following custom function:

   each if Text.Length(_) - Text.PositionOf(_, ",") = 3 then Text.Replace(_, ",", ".") else _

   This function takes the current value and checks if the position of the comma is three characters away from the end of the text. If so, it means that the comma is used as a decimal separator, and it replaces it with a period. Otherwise, it returns the current value unchanged.

5. After modifying the code, the query will look like this:

   = Table.ReplaceValue(#"Changed Type", each [Unit Price], each if Text.Length(_) - Text.PositionOf(_, ",") = 3 then Text.Replace(_, ",", ".") else _, Replacer.ReplaceValue, {"Unit Price"})

6. Now, the Unit Price column has all the numbers in the same format, with periods as decimal separators. However, the data type of the column is still text, which means we cannot perform calculations on it. To fix this, we need to change the data type of the column to decimal number. To do this, we can click on the ABC icon next to the column name, and select Decimal Number from the drop-down menu. This will create another step in the query that uses the Table.TransformColumnTypes function to change the data type of the column.
7. After changing the data type, the query will look like this:

   = Table.TransformColumnTypes(#"Replaced Value",{{"Unit Price", type number}})

8. Now, the Unit Price column has the correct data type and format, and we can perform calculations on it. For example, we can add a new column that calculates the Total Price by multiplying the Unit Price and the Quantity. To do this, we can click on Add Column > Custom Column, and enter the following formula:

   [Unit Price] * [Quantity]

   This will create a new column called Custom, which contains the Total Price for each row. We can rename the column by double-clicking on the column header, and entering a new name, such as Total Price.

9. After adding the new column, the query will look like this:

   = Table.AddColumn(#"Changed Type1", "Total Price", each [Unit Price] * [Quantity])

10. Finally, we can click on Close & Load to load the transformed data into Excel. We can see the result in a new worksheet, which contains the original columns and the new Total Price column. We can also see the query steps in the Queries & Connections pane, and we can edit or refresh the query as needed.

Result

Here is the result of the scenario, showing the original table and the transformed table:

Other approaches

There may be other ways to achieve the same result using PowerQuery, depending on the data and the criteria. For example, you can use the following approaches:

• Use the Text.ReplaceRange function to replace a specific range of characters in a text value, such as the last character. For example, you can use the following custom function to replace the last character of the current value with a period, if it is a comma:

  each if Text.End(_, 1) = "," then Text.ReplaceRange(_, Text.Length(_) - 1, 1, ".") else _

• Use the Text.Split function to split a text value into a list of subtexts based on a delimiter, such as a comma. Then, use the List.Combine function to combine the list of subtexts into a single text value, using a different delimiter, such as a period. For example, you can use the following custom function to split the current value by commas, and then combine it by periods:

  each Text.Combine(Text.Split(_, ","), ".")

• Use the Number.FromText function to convert a text value to a number value, using a specific culture or locale that matches the format of the text value. For example, you can use the following custom function to convert the current value to a number value, using the “fr-FR” culture, which uses commas as decimal separators:

  each Number.FromText(_, "fr-FR")