Replace Multiple Values in Excel and M-Code

Today we are going to discuss about numerous methods in Excel and Power Query to replace multiple values.

Our dataset includes a list of names and country codes that are separated by commas, along with a replacement table containing columns for country codes and country names.

Our goal is to replace country codes with their corresponding country names.

Method-1:

= List.Transform(Lst, (f) => Text.Combine(List.ReplaceMatchingItems(Text.Split(f,", "), Table.ToRows(Source)),", "))

Lst -> List of names with Country Codes

Source -> Replacement Table with Code and Name columns.

Method-2:

let

Source = each Excel.CurrentWorkbook(){[Name=_]}[Content],

Result = List.Accumulate(Table.ToRows(Source("Table2")), Source("Table1"), (s,c) => Table.ReplaceValue(s,c{0},c{1},Replacer.ReplaceText,Table.ColumnNames(Source("Table1"))))

in

Result

Table1 -> Table of names with country codes.

Table2 -> Replacement Table.

Method-3:

=REDUCE(A4:A13,C6:C10,LAMBDA(a,v,SUBSTITUTE(a,v,OFFSET(v,,1))))

A4:A13 -> Name with Country codes column,

C6:C10 -> Column Code in replacement table. OFFSET function will help us to pick Country Names by offsetting one column.

Method-4:

=LET(x,C6:D10,i,INDEX,REDUCE(A4:A13,SEQUENCE(ROWS(x)),LAMBDA(a,v,SUBSTITUTE(a,i(x,v,1),i(x,v,2)))))

C6:D10 -> Replacement table,

A4:A13 -> Name with Country codes column.