Function to Replace values in one column based on entries in a mapping table Without adding columns

[modi123p]

Hi,

I m working on translating content of all columns in large tables with more than 50 text columns and more than 2 million rows

I achieve it by merging with Mapping table.

However, this creates a new column for every column that needs to be replaced and it requires 50 merge operations.

Alternately I need to Unpivot --> Merge --> Pivot columns. But as the number of rows grow, this becomes unfeasible and extremely time consuming.

Found one earlier post in the same community which also links to you tube video that uses Replacer.ReplaceText

But Im unable to get it working.

How to create a function that loops through column values and replace values based on mapping entries in another table and calls another function based on a condition?

Is it faster to use the Replacer in place of Merge?

Sample Excel tables are provided below:

Data Table
ColA	ColB	ColC	ColD
A	When	This	A1
B	What	That	A2
C	Where	Them	A3
D	Why	US	ZZ

Mapping Table
TextToReplace	Replace With
A	AA
B	BB
C	CC
D	DD
When	EE
What	FF
Where	GG
Why	HH
This	II
That	JJ
Them	KK
There	LL
A1	MM
A2	NN
A3	OO
A4	PP

Output Table: Replace If Exact match Else call Translate Function
ColA	ColB	ColC	ColD
AA	EE	II	A1
BB	FF	JJ	A2
CC	GG	KK	A3
DD	HH	fnTranslate(US, en)	fnTranslate(ZZ, en)

Thanks,

Mannu

[dax]

Hi @modi123p  

Because you need to replace multiple columns, so I think you could try to unpivot the table, then try below replace function to see whether it work or not

let BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
    let
        //Convert the FindReplaceTable to a list using the Table.ToRows function
        //so we can reference the list with an index number
        FindReplaceList = Table.ToRows(FindReplaceTable),
        //Count number of rows in the FindReplaceTable to determine
        //how many iterations are needed
        Counter = Table.RowCount(FindReplaceTable),
        //Define a function to iterate over our list 
        //with the Table.ReplaceValue function
        BulkReplaceValues = (DataTableTemp, n) => 
        let 
            //Replace values using nth item in FindReplaceList
            ReplaceTable = Table.ReplaceValue(
                DataTableTemp,
                //replace null with empty string in nth item
                if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
                if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
                Replacer.ReplaceText,
                DataTableColumn
                )
        in
            //if we are not at the end of the FindReplaceList
            //then iterate through Table.ReplaceValue again
            if n = Counter - 1 
                then ReplaceTable
                else @BulkReplaceValues(ReplaceTable, n + 1),
        //Evaluate the sub-function at the first row
        Output = BulkReplaceValues(DataTable, 0)   
    in
        Output
in
    BulkReplace

Use this in your Data Table(your Mapping Table in code is my replacer table)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQrPSM0DUiEZmcVAytFQKVYnWskJLJFYApYAU45GYAlniI6iVLBMai5Ixhgs4wKWqQSSocFAIipKKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ColA = _t, ColB = _t, ColC = _t, ColD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColA", type text}, {"ColB", type text}, {"ColC", type text}, {"ColD", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    Custom1 = Query1(#"Unpivoted Columns", replacer, {"Value"}),
    #"Added Index" = Table.AddIndexColumn(Custom1, "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

You could refer to bulk-replace-values  for details.

Best Regards,
Zoe Zhi

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

[ImkeF · Accepted solution]

 @dax 

your solution will create wrong values for columns "ColD". (explanation can be found here: https://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/  )

Also, this approach doesn't allow detection on non-matches where another function shall be applied as well.

 @modi123p ,

I attached a file with some alternative solutions. Would be very interested to hear which one performs best (although I wouldn't expect wonders, as there's a lot to do...)

You have to fill in your own code into "fnTranslate" (currently it just returns a text-string, but my solution will apply whatever you put into the function to the values of the respective field).

It doesn't us pivot-operations, as their clever (and expensive) logic is not needed here. Instead I split the table into a list of columns using "Table.ToColumns" and after the transformations stitch them back together using Table.FromColumns.

[modi123p]

@ImkeF, @dax 

Thank you both for the suggestions.

I m looking at replacing entire sentences(content) not just one or two words.

I will try both these approaches and will let you know which worked fine for me.

Thank you.

Mannu