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
Super UserTable.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.
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.