Iterating through a list for text matching operations in Power Query

[tigertedd]

I have a Description column in the current table, and column Supplier 1 - 15 in a table of categories.

I need to find if anything in the column Supplier 1 matches any part of the description. For example, the description could be "Card - ATM 03112023", and somewhere in the Supplier 1 column, there would be an item called "ATM".

Then from this I need to find the position of ATM in the supplier 1 column, and return a category at the same position (essentially an XLOOKUP in Power Query). 

If it doesn't match anything, it should look in the next column, Supplier 2.

There is also the added complication that Suppliers 1-5 relate to my joint account, 6-10 relate to my personal account, and 11-15 relate to my bsuiness account.

To achieve this I've come up with this lengthy code:

if [Account] = "Joint" then
    if List.PositionOf(Categories[Supplier 1], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 1], each Text.Contains(_, [Description]))}
    else if List.PositionOf(Categories[Supplier 2], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 2], each Text.Contains(_, [Description]))}
    else if List.PositionOf(Categories[Supplier 3], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 3], each Text.Contains(_, [Description]))}
    else if List.PositionOf(Categories[Supplier 4], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 4], each Text.Contains(_, [Description]))}
    else if List.PositionOf(Categories[Supplier 5], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 5], each Text.Contains(_, [Description]))}
    else null
else if [Account] = "Personal" then
    if List.PositionOf(Categories[Supplier 6], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 6], each Text.Contains(_, [Description]))}
    else if List.PositionOf(Categories[Supplier 7], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 7], each Text.Contains(_, [Description]))}
    else if List.PositionOf(Categories[Supplier 8], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 8], each Text.Contains(_, [Description]))}
    else if List.PositionOf(Categories[Supplier 9], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 9], each Text.Contains(_, [Description]))}
    else if List.PositionOf(Categories[Supplier 10], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 10], each Text.Contains(_, [Description]))}
    else null
else if [Account] = "Business" then
    if List.PositionOf(Categories[Supplier 11], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 11], each Text.Contains(_, [Description]))}
    else if List.PositionOf(Categories[Supplier 12], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 12], each Text.Contains(_, [Description]))}
    else if List.PositionOf(Categories[Supplier 13], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 13], each Text.Contains(_, [Description]))}
    else if List.PositionOf(Categories[Supplier 14], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 14], each Text.Contains(_, [Description]))}
    else if List.PositionOf(Categories[Supplier 15], each Text.Contains(_, [Description])) >= 0 then Categories[Combined]{List.PositionOf(Categories[Supplier 15], each Text.Contains(_, [Description]))}
    else null
else null

To be honest, Chat GPT helped me come up with this. However, Chat GPT is now telling me that Text.Contains can't deal with iterating through the list created by Categories[Supplier 1]. It only took about 6 hrs for it to tell me this!

Are there any alternatives that can do a similar job. Chat GPT seems to think it is impossible now, but I'm hoping some human ingenuity can come up with a solution.

I had this working find in Excel, using SEARCH and lots of nested IFERRORS. But now I need to move it into Power Query, and can't quite get the right results. Any help will be greatly appreciated.

[wdx223_Daniel]

you can structure a nested list to contain your supplier category.

=let CategoryLists={{"Joint",{"Supplier 1","Supplier 2","Supplier 3","Supplier 4","Supplier 5"}},

                                 {"Personal",{"Supplier 6","Supplier 7","Supplier 8","Supplier 9","Supplier 10"}},

                                 {"Business",{"Supplier 11","Supplier 12","Supplier 13","Supplier 14","Supplier 15"}}},

        Cols=List.Skip(CategoryList,each _{0}<>[Account]){0}?{1}?

  in

      if Cols=null then null else List.Skip(List.Combine(Table.ToColumns(Table.SelectColumns(Category,Cols,1))),(x)=>not Text.Contains([Description],x)){0}?