Replacing values in another column using a conversion table Fuzzy match and full match


I have a table with several columns (green color)
I made a conversion table (light blue color) of values that I want to replace in the source column for all channel values that exist in the conversion table.

The replacement only if “C_Name” does not contain a value ( empty)

In a second step, I would like to prepare another conversion table and do the same using fuzzy match

For example, all the values that exist in the conversion table and contain the subtext “TSE” in the channel column will replace respectively the values in the source column

Thanks in advance

I managed to do it but in a long and ineffective way

I would appreciate help or an idea how to do it in a shorter and more elegant way

Thanks in advance for any ideas
Replace-values-based-on-a-list___OK.xlsx (23.1 KB)

hope I defined the problem clearly enough…

Although I managed to solve it and in the example file works quite quickly

The problem is when I run it on a large amount of data it takes a long time for the query to refresh…

I would appreciate any idea how to do the same but in an efficient and elegant way

Hi @y70852846,

Loading the input lists into memory before using them inside List.Accumulate can have a positive impact. See modifications below:

    listIterations = List.Buffer( {0..List.Count(FindReplace[Channel])-1} ),
    lookIn = List.Buffer( FindReplace[Channel] ),
    replaceBy = List.Buffer( FindReplace[New source]),

    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Channel", "source1"),

    Custom1 = List.Accumulate(
        #"Duplicated Column",
        (State, Current) => Table.ReplaceValue(State,lookIn{Current},replaceBy{Current},Replacer.ReplaceValue,{"source1"})

I hope this is helpful

Thank you

Very impressive and elegant

Now everything works great

Glad to hear it, cheers!

1 Like