Trying to change multiple values in one step

[jonclay]

Hi everyone
I'm using the following code in an attempt to change multiple values all in one step:

= Table.ReplaceValue(
#"Renamed Columns",
each [Type],
each if [Type] = "157420000" then "A"
else if [Type] = "157420002" then "B"
else "C",
Replacer.ReplaceValue,{"Type"})

The issue I'm facing is that the code works if only one value is present in the Type field, but does not work if more than one comma-separated values are shown in the Type field. We use Dynamics CRM and the Type field is a multi-select option set field, so any ID can have multiple Types. I have shown some images below of what I mean:

ID 16638 has two comma separated types:

ID 44406 has just the one type:

However, when I run the above code, I'm getting 44406 showing as A (correctly) and 16638 showing as C when it should be B.

I've wondered if I should be using Text.Contains or List.Contains, but I just can't seem to get the syntax correct.

Any help would be much appreciated.

Thank you
Jon

[parry2k]

@jonclay good to hear. cheers!!

[parry2k]

@jonclay if you want to use the replace function and not add a new column then change replace 

= Table.ReplaceValue( #"Changed Type", each [Type], each fnGetType([Type]),Replacer.ReplaceValue,{"Type"})

[parry2k · Accepted solution]

@jonclay here is what you can do, create a new blank query -> click advanced editor and paste the following M code, call this new query fnGetType (you can use whatever name you want)

(value) =>
let

    valueToList = Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)(value),
    valueToType = Text.Combine(List.Transform(valueToList, each if _ = "157420000" then "A" else if _ = "157420002" then "B" else "C"), "," )
    
in
    valueToType

now add a new column in your table with the following expression

fnGetType([Type])

[jonclay]

Hi @parry2k 

Fantastic, thank you so much. That works perfectly.

Best wishes,
Jon

[jonclay]

Hi @parry2k 

Yes please, that's exactly what I need.

Many thanks
Jon

[parry2k]

@jonclay So I guess you want each comma-separated value and then have multiple types, also comma-separated, correct?