Forum Discussion

sonnh's avatar
sonnh
Frequent Visitor
5 years ago
Solved

Power query - Replace multiple words in column using other table.

Hello all,
I have a raw data needed to be procesed by replace multiple words by this table.
OriginReplace

,DistrictDistrict
,districtDistrict
DistDistrict

I found solution here https://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/
And write following query:
#"Added Custom" = Table.AddColumn(#"Replaced Value3", "district_test", each Text.Combine(List.ReplaceMatchingItems(Text.Split([District]," "), Convert[Liste])," ")),
But this solution is only suitable for replace single word to single word. If I want replace multiple word like below table, it can not work because Text.Split use space as delimiter.
OriginReplace

, District 01District 1
,district 002District 2
Dist 10District 10

This convert table have many rows and need to be added more words in future, so I don't want replace manually.

Can you give me advice or solution for this case.
Thanks in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi sonnh 

     

    I have adapted one custom function applied to different cases, you can have a look and modify accordingly, credit to original author:

    https://www.howtoexcel.org/power-query/bulk-replace-values/

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0lFwySwuKcpMLlEwMFSK1QGKpMAFDIzAIiAVCoYGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Origin = _t]),
    BulkReplace = (DataTable as table, FindReplaceList as list, DataTableColumn as list) =>
    let   
        Counter = List.Count(FindReplaceList),
        BulkReplaceValues = (DataTableTemp, n) => 
        let 
            ReplaceTable = Table.ReplaceValue(
                DataTableTemp,
                FindReplaceList{n},
                "District",
                Replacer.ReplaceText,
                DataTableColumn
                )
        in

            if n = Counter - 1 
                then ReplaceTable
                else @BulkReplaceValues(ReplaceTable, n + 1),
        Output = BulkReplaceValues(DataTable, 0)   
    in
        Output,

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Origin", type text}}),
    Custom1 = BulkReplace( #"Changed Type",{", District",",district","Dist"},{"Origin"})
in
    Custom1

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi sonnh 

     

    I have adapted one custom function applied to different cases, you can have a look and modify accordingly, credit to original author:

    https://www.howtoexcel.org/power-query/bulk-replace-values/

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0lFwySwuKcpMLlEwMFSK1QGKpMAFDIzAIiAVCoYGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Origin = _t]),
    BulkReplace = (DataTable as table, FindReplaceList as list, DataTableColumn as list) =>
    let   
        Counter = List.Count(FindReplaceList),
        BulkReplaceValues = (DataTableTemp, n) => 
        let 
            ReplaceTable = Table.ReplaceValue(
                DataTableTemp,
                FindReplaceList{n},
                "District",
                Replacer.ReplaceText,
                DataTableColumn
                )
        in

            if n = Counter - 1 
                then ReplaceTable
                else @BulkReplaceValues(ReplaceTable, n + 1),
        Output = BulkReplaceValues(DataTable, 0)   
    in
        Output,

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Origin", type text}}),
    Custom1 = BulkReplace( #"Changed Type",{", District",",district","Dist"},{"Origin"})
in
    Custom1