Using a Nested If with List. Accumulate function to eliminate additional conditional column

[pstaggers]

I would like to be able to eliminate the need for the added conditional column (line 15 - image attached shows line #s).  I think it could be accomplished with another if statement (or possibly a nested if under the List.Accumulate function at line 5).  I have attempted several different ways without success.  Does anyone have a suggestion? 

//Determine if ProductName exists in MergedText
MatchToProduct = Table.AddColumn(#"Inserted Merged Column", "ProductExactMatch",
each List.Accumulate
(
ProductTable[ProductName],
"",
(state, current) =>
if List.Contains(Text.Split( [MergedText], "| "), current)
then state & " " & current
else state
)),
#"Added Conditional Column" = Table.AddColumn(MatchToProduct, "Custom", each if [ProductExactMatch] = "" then "NOT VALIDATED" else [ProductExactMatch]),

Idea for List.Accumulate courtesy of: https://www.myonlinetraininghub.com/create-a-list-of-matching-words-when-searching-text-in-power-que...
-->List Exact Matches - Case Sensitive

[Vera_33 · Accepted solution]

Hi @pstaggers 

I can't add the condition to line 5, but can combine it to the same step, verify if it is "" then return the value

//Determine if ProductName exists in MergedText
MatchToProduct = Table.AddColumn(#"Inserted Merged Column", "ProductExactMatch",each 
[a=List.Accumulate
(
ProductTable[ProductName],
"",
(state, current) =>
if List.Contains(Text.Split( [MergedText], "| "), current)
then state & " " & current
else state
),
b=if a="" then "NOT VALIDATED" else a][b])

[AlexisOlson · Accepted solution]

The reason you can't simply add the logic at line 5 is that, at that point, you don't know whether the List.Accumulate is going to return any matches or not.

@Vera_33's suggestion of using a record is one way to resolve this. Another would be to define the accumulation as a variable like this:

MatchToProduct = Table.AddColumn(#"Inserted Merged Column", "ProductExactMatch",
    each
        let
            matches = List.Accumulate( [...] )
        in
            if matches = "" then "NOT VALIDATED" else matches),

[pstaggers]

Hi Alexis, both @Vera_33 and your solutions worked (Thank you both!  I really appreciate it.)  Now on to the business of making one (or both) into a custom function so I can call it repeatedly  (I often need to search user inputted text strings with littlt to no validation to find matches within different lists of known good values.)  Again, super helpful to see a couple of solutions here.  Thanks!

[Vera_33 · Accepted solution]

Hi @pstaggers 

I can't add the condition to line 5, but can combine it to the same step, verify if it is "" then return the value

//Determine if ProductName exists in MergedText
MatchToProduct = Table.AddColumn(#"Inserted Merged Column", "ProductExactMatch",each 
[a=List.Accumulate
(
ProductTable[ProductName],
"",
(state, current) =>
if List.Contains(Text.Split( [MergedText], "| "), current)
then state & " " & current
else state
),
b=if a="" then "NOT VALIDATED" else a][b])