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

Power Query M Code Showcase
1

Hello

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

fuzzy pq
fuzzy pq820×493 111 KB

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

4

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)

5

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

6

Hi @y70852846,

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

let
    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(
        listIterations,
        #"Duplicated Column",
        (State, Current) => Table.ReplaceValue(State,lookIn{Current},replaceBy{Current},Replacer.ReplaceValue,{"source1"})
    ),

I hope this is helpful

7

Thank you

Very impressive and elegant

Now everything works great

8

Glad to hear it, cheers!

1 Like