Replace Multiple Text Values based on an array

[LPenatti]

Hi everyone,

I'm working in PowerQuery in PowerBI and I'm trying to write a function that replaces multiple values by checking these values inside an array.

Basically, my real data looks like the first column Customer Name, this is what comes from the ERP system, thousand of rows. The second column, Desired, is what I expect, but I don't want to create a new column, but replace values in the same column.

I want to replace every brf for BRF S.A., to avoid S.A, SA and S/A, for instance. The same for jbs and many other customers.

In addition, I want to avoid double and triple spaces where Trim doesn't work, and many other values that I want to replace too.

Example code for tests:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipKUwjWc1TSUXIKcgOx9JRidaKVkiDCeujiYI4+hnIQRyEYQ9grqRhmipdTMJK4UzHUFBRh59DgEH9f1yAFBUOglHNpcUl+bmoRiIcqq2CEIg3kxsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, Desired = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", type text}}),


#"Replaced Value" =
//replace multiple strings
let
    Substitutions = [
        #"  " = " ", //double white space
        #"   " = " ", //triple white space
        #"brf" = "BRF S.A.",
        #"jbs" = "JBS S.A."], 

    Substituted = Table.TransformColumns(#"Changed Type", {"Customer Name", each if Text.Contains(_, "brf", Comparer.OrdinalIgnoreCase) then Text.Replace(_, _, "BRF S.A.") else _, type text})
in
    Substituted

in
    #"Replaced Value"

I want to indicate the formula to look the array and then replace by the respective value.

The post that comes closest to the solution I need is this:

Replace Multiple Text Values With a Single Text Value 

But in this case the solution replaces two values by only one.

Could anyone help me to find out a solution for this?

Thanks in advance!

[Anonymous]

Actually, if you're sure that your replacement table is tight, you could just do a Merge (from the GUI, and left join on the Customer Name column.

--Nate 

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipKUwjWc1SK1YlWSoKw9cAcpyA3hWB9RzhbIRiqKAioKCZGLyYvEaLQK6kYocvLqRiuyzFRISlJoTgRzHEODQ7x93UNUlAwROMrGEEESotL8nNTixTAwBgslgwTi8kzUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t]),
    #"UPPER SPACE" = Table.AddColumn(Source, "Transform", each List.Transform(List.Select(Text.Split([Customer Name], " "), each _<>""), each Text.Clean(Text.Upper(_)))),
    SA = Table.TransformColumns(#"UPPER SPACE", {"Transform", each let a=List.Last(_), b=if Text.Combine(List.Transform(Text.SplitAny(a, ". \/#(lf)"), Text.Upper)) = "SA" then Text.Combine(List.RemoveLastN(_,1) & {"S.A."}, " ") else Text.Combine(_, " ") in b})
in
    SA

[LPenatti]

hi @CNENFRNL,

Thanks for your help. It works but I wanted to keep the correct names in the same column, not create a new one. Is it possible?

[Jakinta]

Maybe this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipKUwjWc1SK1YlWSoKw9cAcpyA3hWB9RwQbwvRKKkao8XIqhqtxDg0O8fd1DVIwROUaKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Value.Is(Value.FromText(Text.Trim(Text.AfterDelimiter([Customer Name], " ")," ")), type number) 
then Text.Proper([Customer Name]) 
else Text.Upper(Text.Trim(Text.BeforeDelimiter([Customer Name], " "))) & " S.A.")
in
    #"Added Custom"

or more general...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipKUwjWc1SK1YlWSoKw9cAcpyA3hWB9RwQbwvRKKkao8XIqhqtxDg0O8fd1DVIwROUaKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Upper(Text.Select(Text.Trim(Text.AfterDelimiter([Customer Name], " ")," "),{"A".."Z","a".."z"})) = "SA" 
then Text.Upper(Text.Trim(Text.BeforeDelimiter([Customer Name], " "))) & " S.A."
else Text.Proper([Customer Name]))
in
    #"Added Custom"

[LPenatti]

hi @Jakinta 

Thanks for your help.

This code works well but I still have a question, is it posible to keep the result in the same column instead of creating a new one?

[Jakinta · Accepted solution]

Yes, it is.

Table.TransformColumns(PreviousStep, {{"Customer Name", each if Text.Upper(Text.Select(Text.Trim(Text.AfterDelimiter(_, " ")," "),{"A".."Z","a".."z"})) = "SA" then Text.Upper(Text.Trim(Text.BeforeDelimiter(_, " "))) & " S.A." else Text.Proper(_)}})

[LPenatti]

hi @Jakinta 

Thanks! It worked 😉

[mahoneypat]

This can be done in the query editor, but it may be easier just to add a Groups column on the DAX side after the table is loaded.  You can multi select each group of values and then assign them a common value in the Group column.  It is easy to update and add new values later if needed with Edit Group.

Use grouping and binning in Power BI Desktop - Power BI | Microsoft Docs

Pat

[LPenatti]

Hi @mahoneypat 

Thanks for the idea, but I can't group customers with the same name into a groups, each one has a different address and I will need to show separate results for each of them in my dashboard.
I just want to have the standardized name for customers that has the same name, but not adding a lot of 'replace steps' in query, I'd like to learn how to do it in a single step. 

[mahoneypat]

I still think adding a Groups column would meet your need, but this article describes an approach to do it in the query editor.

Multiple replacements or translations in Power BI and Power Query – The BIccountant

Pat

[LPenatti]

hi @mahoneypat 

Thanks for sharing the hyperlink.

I understand your point of view of creating groups, but there are over than 1,000 customers in my database. I'd have to create groups for all of them to avoid the "others", and everytime a new customer is registered, I'll have to edit the group column.

Maybe I'm not fully understanding your idea but the post you sent is pretty enlightening too, I appreciate it.

[AlB]

Hi @LPenatti 

I'm not sure I understand correctly but it looks like you could use the table above for a merge. What is the data you want to transform like?

Can you show a clear example  of the input data and the expected result for that data?

Please share in  text-tabular format so that the contents of the tables can be copied to run some quick tests. And include the table above that indicates the conversions in text-tabular format too.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[LPenatti]

Hi @AlB, thanks for your reply.

The first printscreen is an example table where the first column is what my data looks like and the second column is the expected result.

I edited my post for better understanding and also added table contents in a tabular form.