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
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?
Community SupportHi 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 you
That 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 youSo 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
)