Conditional Replace Using Outside table

[allarsen]

Hello,
I'm trying to replace some text using an outside table.

The original data looks like this:

Criteria

Thing 1, Thing 2, Thing 3

Thing 2, Thing 5, Thing 1

The Replacement table looks like this:

Criteria1	Criteria1	Replace
Thing 1	Thing 2	Thing 1

The intended out come is this:

Criteria

Thing 1, Thing 3

Thing 1, Thing 5

The code I have is as follows:

I'm getting the following error:

Not sure what I'm doing.

Thanks!

[v-dineshya]

Hi @allarsen ,

Thank you for reaching out to the Microsoft Community Forum.

From your screenshot, you're using Table.AddColumn and within it, you have two function expressions (two separate (x) => lambdas) passed as arguments. However, Table.AddColumn expects:

Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table

Note: You're passing two function arguments instead of a function and an optional type. This is why you're getting the error M is interpreting the second function as the type parameter.

You should combine your logic into a single function (only one (x) =>) and use it for the column.

= Table.AddColumn(
#"Added Criteria Column",
"Criteria Replace",
(x) =>
let
match = Table.SelectRows(
#"Conditional Replacement",
each Text.Contains(x[Criteria], [Criteria1]) and Text.Contains(x[Criteria], [Criteria2])
),
result = if Table.IsEmpty(match)
then Text.Replace(x[Criteria], [Criteria2] & ", ", "")
else Text.Replace(x[Criteria], [Criteria1] & ", ", match{0}[Replacement] & ", ")
in
result
)

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[allarsen]

Thanks for your help.

That text give the following error (which I don't understand and can't find documentation for):

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

[v-dineshya]

Hi @allarsen ,

Please refer below M code

= Table.AddColumn(
#"Added Criteria Column",
"Criteria Replace",
(x) =>
let
match = Table.SelectRows(
#"Conditional Replacement",
(r) => Text.Contains(x[Criteria], r[Criteria1]) and Text.Contains(x[Criteria], r[Criteria2])
),
result = if Table.IsEmpty(match)
then Text.Replace(x[Criteria], match{0}[Criteria2] & ", ", "")
else Text.Replace(x[Criteria], match{0}[Criteria1] & ", ", match{0}[Replacement] & ", ")
in
result
)

Note:  (r) => as the row variable inside Table.SelectRows.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[allarsen]

That gets this:

Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
[Table]

[v-dineshya]

Hi @allarsen ,

Option 1:

= Table.AddColumn(
    #"Added Criteria Column",
    "Criteria Replace",
    (x) =>
        let
            match = Table.SelectRows(
                #"Conditional Replacement",
                (r) => Text.Contains(x[Criteria], r[Criteria1]) and Text.Contains(x[Criteria], r[Criteria2])
            ),
            result = if not Table.IsEmpty(match)
                then Text.Replace(x[Criteria], match{0}[Criteria1] & ", ", match{0}[Replace] & ", ")
                else x[Criteria]
        in
            result
)

Option 2:

= Table.AddColumn(
#"Added Criteria Column",
"Criteria Replace",
(x) =>
let
match = Table.SelectRows(
#"Conditional Replacement",
(r) => Text.Contains(x[Criteria], r[Criteria1]) and Text.Contains(x[Criteria], r[Criteria2])
),
result = if Table.IsEmpty(match) then
x[Criteria]
else
Text.Replace(
x[Criteria],
match{0}[Criteria1] & ", ",
match{0}[Replacement] & ", "
)
in
result
)

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[allarsen]

So close! That replaced the [Criteria1] with the [Replacement], but left [Criteria2] intact.

Table.AddColumn(

    #"Added Criteria Column",

    "List Replace",

    (x) =>

        let

            match = Table.SelectRows(

                #"Conditional Replacement",

                (r) => Text.Contains(x[Criteria], r[Criteria1]) and Text.Contains(x[Criteria], r[Criteria2])

            ),

            result = if not Table.IsEmpty(match)

                then

                Text.Replace(

                    Text.Replace(x[Criteria], match{0}[Criteria1] & ", ", match{0}[Replace] & ", "),

                    ""

                    )

                else x[Criteria]

        in

            result

)

Thank you for all your help!

[v-dineshya · Accepted solution]

Hi @allarsen ,

Table.AddColumn(
#"Added Criteria Column",
"List Replace",
(x) =>
let
match = Table.SelectRows(
#"Conditional Replacement",
(r) => Text.Contains(x[Criteria], r[Criteria1]) and Text.Contains(x[Criteria], r[Criteria2])
),
result = if not Table.IsEmpty(match) then
let
items = Text.Split(x[Criteria], ", "),
filtered = List.RemoveItems(items, {match{0}[Criteria1], match{0}[Criteria2]}),
updated = List.InsertRange(filtered, 0, {match{0}[Replace]}),
combined = Text.Combine(updated, ", ")
in
combined
else
x[Criteria]
in
result
)

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[allarsen]

So close!
That replaces the [Criteria1] with the [Replacement] value, but it doesn't replace [Criteria2] with ""

= Table.AddColumn(
#"Added Criteria Column",
"Criteria Replace",
(x) =>
let
match = Table.SelectRows(
#"Conditional Replacement",
(r) =>
Text.Contains(x[Criteria], r[Criteria1])
and Text.Contains(x[Criteria], r[Criteria2])
),

result = if Table.IsEmpty(match) then x[Criteria]
else
Text.Replace(
Text.Replace(
x[Criteria],
match{0}[Criteria1] & ", ",
match{0}[Replacement] & ", "
),
match{0}[Criteria2] & ", ",
""
)
in
result
)

[wdx223_Daniel]

=let a=List.Buffer(Table.ToList(#"Conditional Replacement",each {List.RemoveLastN(_),List.Last(_)})) in Table.AddColumn(#"Added Criteria Column","Criteria Replace",each let b=Text.Split([Criteria],","),c=List.Select(a,(x)=>List.RemoveItems(x{0},b)={}){0}? in if c is null then [Criteria] else Text.Combine(List.RemoveItems(b,c{0})&{c{1}},","))

[Omid_Motamedise]

Hi @allarsen 

For such itterative action, List.Accumulate can be used.

to see how, just whach the following video

https://www.youtube.com/watch?v=G8PRbWuDcmQ

[PwerQueryKees]

I am guessing you new at PowerQuerey M language. Seems like you are taking the trial and error approach to learning... 

Although I could probably solve, this will exceed the time I can spend on it and you would probably end up with a solution you don't understand.
I suggest you search for some good resource for leaning powerquery. 
You could start here: Solved: Where to Learn Step by Step Data Prep In Power Que... - Microsoft Fabric Community

[allarsen]

Yes, very new.

Thanks, I've been looking for some good resources for learning. Most of the documentation expects people already to be fairly fluent.

[PwerQueryKees]

Table.AddColumn can only add 1 column at a time. It seesm you are trying to add 2 columns with one Table.AddColumns() call. have a look here: Table.AddColumn - PowerQuery M | Microsoft Learn.

[allarsen]

I am only trying to add one column, but I want two replacements to be made. Was that not clear from the explanation of what I was trying to do? Are additional details needed?

The referenced article does not address the specific use case I am attempting, at least not in a way that I can understand it.