How to find text from list in cell and return that text

[Bertenvanloover]

Hi, I could use some help finding a way to do the manipulation below in Power Query:
I want to create 2 custom columns "Approver_Name1" and "Approver_Name2" that shows
* The Name of a person that was mentioned in column "T1Comment" based on table "Approver_Name_Table" and "No Approver" when there is no hit based on that table

* In case there are 2 names mentioned in the column "T1Comment" the second name should end up in "Approver_Name2"

Purpose: this table shows a history log of changes to the status of Documents, where each Document has a unique DocumentId. And I'd like to create an overview per approver so that that person gets an action list.

DocumentId	TransitionName	T1Comment	SubmittedDate	Approver_Name1	Approver_Name2
1	Send for Approval	To be checked by Tom	22/02/2023 11:00	Tom	No Approver
1	Send back for Editing	Please change extension	22/02/2023 11:01	No Approver	No Approver
1	Approved	Approved	22/02/2023 11:02	No Approver	No Approver
2	Send for Approval	Capital mistake please check tom	22/02/2023 11:05	Tom
2	Send for Approval	Checked by Tom & Bob	22/02/2023 11:06	Tom	Bob
3	Send for Approval	Tom/bob	22/02/2023 11:00	Tom	Bob
3	Send for Approval	Checked byJay	22/02/2023 11:02	Jay	No Approver
3	Send for Approval	2nd check jay	22/02/2023 11:03	Jay	No Approver
3	Send for Approval	Send for Approval to Joe	22/02/2023 11:05	Jeoffrey	No Approver
3	Approved	Approved	22/02/2023 11:10	No Approver	No Approver
3	Send back for Editing	Change Editing Required	11/09/2023 09:11	No Approver	No Approver

Approver_Name_Table

Input	Reference_Name
Bob	Bob
bob	Bob
Tom	Tom
Joe	Jeoffrey
Jay	Jay
and so on...	...

This is only my second Power Query to date, so I'm a complete rookie. Thanks for the help!

Similar to: https://community.powerbi.com/t5/Power-Query/Query-to-create-column-comparing-text-value-to-column-i... 

Related to: https://community.powerbi.com/t5/Power-Query/Find-Text-from-List-in-Rows-with-same-ID-as-current-row... 

[Bertenvanloover]

Awesome, works like a charm! Thanks a lot for making my week 🙂

[ImkeF · Accepted solution]

Hi @Bertenvanloover ,
one approach could be like so:

let
    Source = Excel.CurrentWorkbook(){[Name = "Main"]}[Content], 
    #"Changed Type" = Table.TransformColumnTypes(
        Source, 
        {
            {"DocumentId", Int64.Type}, 
            {"TransitionName", type text}, 
            {"T1Comment", type text}, 
            {"SubmittedDate", type any}, 
            {"Approver_Name1", type text}, 
            {"Approver_Name2", type text}
        }
    ), 
    #"Added Custom" = Table.AddColumn(
        #"Changed Type", 
        "Custom", 
        each Table.FromRows(
            {
                List.Distinct(
                    Table.SelectRows(Approver_Names, (a) => Text.Contains(Text.Lower([T1Comment]), Text.Lower(a[Input])))[Reference_Name]
                )
            }
        )
    ), 
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Approver1", "Approver2"})
in
    #"Expanded Custom"

It has a different order of the values compared to your sample. Not sure if this is critical, else let me know.
Please also check the file enclosed.