Forum Discussion

OsmanEmi's avatar
OsmanEmi
Frequent Visitor
4 years ago
Solved

Using OR function within Text.replace function - Power Query

Hi, I'm sort of new to power query and while adding a new column I used the below function which works, however I wanted to ask if their was a way to also replace more than one value. = [#"Dest...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi OsmanEmi 

    I suggest you to build a replacement table then try my M code. Currently, you will replace all words by the same word "Fact". This way will still work if you want to replace multiple words by different words.

    My Sample:

    Replacement Table:

    Use M Code to add a custom column:

    Text.Combine(
    {
  [Destination Type],
  List.Accumulate(
    List.Numbers(0,Table.RowCount(replacements)),
    [Destination Name],
    (state, current) =>
    Text.Replace(state, replacements[Find]{current},
    replacements[Replace]{current}))
    },
    " | " )

    Result is as below.

    Best Regards,
    Rico Zhou

     

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

hnguy71's avatar
hnguy71
Super User
4 years ago

Here's an alternative solution:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOz00tycjMS1cILkjNS1EwNDJWitWByDk6OcPZSfn5BQpJqakFCo4FBSiqnBOLULW5uLopxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DestinationType = _t, #"Destination Name" = _t]),
    ConcatCol = Table.AddColumn(Source, "MyNewColumn", 
        each 
            [DestinationType] & " | " & 
            Text.Combine(
                List.ReplaceMatchingItems(Text.Split([Destination Name], " "), {{"APP", "Facts"}, {"SPEND", "Facts"}, {"CARD", "Facts"}}, Comparer.OrdinalIgnoreCase), 
                " ")
        )
in
    ConcatCol