Forum Discussion
Conditional Replace Using Outside table
Hi Anonymous ,
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
Community SupportHi Anonymous ,
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
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 Anonymous ,
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 youThat gets this:
Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
[Table]- v-dineshya
Hi Anonymous ,
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