Multiple replace of values, some of which may be separated by a comma

[jonclay]

Hi everyone

Our database contains 8 values (e.g. 154720000, 154720001 and so on) that I replace with more meaningful text each time I create a report by using the Replace Values functionality in Power BI.

Is there a way that would let me replace all 8 values, with different meaningful text values, in one go? I've tried using an If statement as shown below, but this doesn't work for me as sometimes a row contains multiple values that are separated by a comma:

= Table.ReplaceValue(
#"PREVIOUS STEP",
each [type],
each if [type] = "157420000" then "Type A"
else if [type] = "157420001" then "Type B"
else if [type] = "157420002" then "Type C"
else if [type] = "157420003" then "Type D"
else if [type] = "157420004" then "Type E"
else if [type] = "157420005" then "Type F"
else if [type] = "157420006" then "Type G"
else if [type] = "157420007" then "Type H"
else "NULL",
Replacer.ReplaceValue,{"type"}
)

So, if a row in my database contains the value of "157420007" then the value is correctly changed to "Type H". However, if the row contains e.g. "154720000, 157420007" then the value is chaned to NULL, whereas I'd be looking for it to be changed to "Type A, Type H".

I've convinced myself that the solution lies in the above If statement, but I can't work out how to make the values change if they're separated by a comma.

Any help would be much appreciated.

Many thanks
Jon

[johnbasha33 · Accepted solution]

@jonclay 

It seems like you're looking to replace multiple values in a column with different meaningful text values, even when those values are separated by a comma. To achieve this in Power BI, you can use a combination of techniques such as splitting the values, replacing each value individually, and then joining them back together. Here's a step-by-step approach:

1. **Split the Values**:
First, split the values in the column by the comma delimiter. You can use the `Text.Split` function to achieve this.

2. **Replace Values Individually**:
For each split value, apply the replacement logic using a loop or iterator function like `List.Transform`.

3. **Join the Values Back**:
After replacing the values, join them back together using the comma delimiter. You can use the `Text.Combine` function for this.

Here's an example implementation of this approach:

```m
let
Source = YourPreviousStep, // Replace with your actual previous step
SplitValues = Table.TransformColumns(Source, {"YourColumnName", each Text.Split(_, ", "), type list}),
ReplaceValues = Table.TransformColumns(SplitValues, {"YourColumnName", each List.Transform(_, each
if _ = "157420000" then "Type A"
else if _ = "157420001" then "Type B"
else if _ = "157420002" then "Type C"
else if _ = "157420003" then "Type D"
else if _ = "157420004" then "Type E"
else if _ = "157420005" then "Type F"
else if _ = "157420006" then "Type G"
else if _ = "157420007" then "Type H"
else "NULL"
), type text}),
JoinValues = Table.TransformColumns(ReplaceValues, {"YourColumnName", each Text.Combine(_, ", "), type text})
in
JoinValues
```

Replace `"YourPreviousStep"` with the name of your previous step, and `"YourColumnName"` with the actual name of the column containing the values to be replaced. This script should split the values by comma, replace each value individually, and then join them back together, producing the desired result even if multiple values are present in a single cell.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[v-zhengdxu-msft · Accepted solution]

Hi @jonclay 

Thanks for the reply from @johnbasha33 , please allow me to provide another insight:

Please try to use this function:

Text.Replace(text as nullable text, old as text, new as text) as nullable text

This function returns the result of replacing all occurrences of text value in text value with text value.

Here's the link for your reference:

Text.Replace - PowerQuery M | Microsoft Learn

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jonclay]

Hi everyone

Thank you for your time and your solutions. All of them seem to work for me in slightly different ways 🙂

Best wishes
Jon

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}}),
    Custom1 = Table.ToRows(replace),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.ReplaceMatchingItems(Text.Split([Type],", "),Custom1),", "))
in
    #"Added Custom"

Hope this helps.

[v-zhengdxu-msft · Accepted solution]

Hi @jonclay 

Thanks for the reply from @johnbasha33 , please allow me to provide another insight:

Please try to use this function:

Text.Replace(text as nullable text, old as text, new as text) as nullable text

This function returns the result of replacing all occurrences of text value in text value with text value.

Here's the link for your reference:

Text.Replace - PowerQuery M | Microsoft Learn

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnbasha33 · Accepted solution]

@jonclay 

It seems like you're looking to replace multiple values in a column with different meaningful text values, even when those values are separated by a comma. To achieve this in Power BI, you can use a combination of techniques such as splitting the values, replacing each value individually, and then joining them back together. Here's a step-by-step approach:

1. **Split the Values**:
First, split the values in the column by the comma delimiter. You can use the `Text.Split` function to achieve this.

2. **Replace Values Individually**:
For each split value, apply the replacement logic using a loop or iterator function like `List.Transform`.

3. **Join the Values Back**:
After replacing the values, join them back together using the comma delimiter. You can use the `Text.Combine` function for this.

Here's an example implementation of this approach:

```m
let
Source = YourPreviousStep, // Replace with your actual previous step
SplitValues = Table.TransformColumns(Source, {"YourColumnName", each Text.Split(_, ", "), type list}),
ReplaceValues = Table.TransformColumns(SplitValues, {"YourColumnName", each List.Transform(_, each
if _ = "157420000" then "Type A"
else if _ = "157420001" then "Type B"
else if _ = "157420002" then "Type C"
else if _ = "157420003" then "Type D"
else if _ = "157420004" then "Type E"
else if _ = "157420005" then "Type F"
else if _ = "157420006" then "Type G"
else if _ = "157420007" then "Type H"
else "NULL"
), type text}),
JoinValues = Table.TransformColumns(ReplaceValues, {"YourColumnName", each Text.Combine(_, ", "), type text})
in
JoinValues
```

Replace `"YourPreviousStep"` with the name of your previous step, and `"YourColumnName"` with the actual name of the column containing the values to be replaced. This script should split the values by comma, replace each value individually, and then join them back together, producing the desired result even if multiple values are present in a single cell.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!