Forum Discussion
Conditional Replace Using Outside table
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!
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
14 Replies
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.
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.
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 CommunityHi Anonymous
For such itterative action, List.Accumulate can be used.
to see how, just whach the following video
https://www.youtube.com/watch?v=G8PRbWuDcmQ
=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}},","))
Hi 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 youHi 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
