Searching a column in table A using data from a column in table B

[mikelebo99]

I have a need to search a string in Column 1 from Table A with column 1 Table B so that if a match exists, it displays the value of Column 2 in Table B. 

Table A = Outlook 365 Data (Subject [Text])

Table B = External Vendor Info (Name/ ID# [Text])

My goal is to create a new column that will search Table A Subject Column with any value in the ID# from Table B.

If a result is found, I want to display the Name from Table B in that new column. 

I have found several posts that are close but cannot seem to find one that does this specific case scenario. 

Any help is appreciated. 

Michael 

[dufoq3]

Hi @mikelebo99, what about this?

Result (Table1 with matching ID's from Table2)

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjRSitWJVnJz9zAxBbMcHR3NzJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vJ3VvBNrMxXcM7JzMtMVlB2dHI2NFIILE0sKkktyqlU8E1NLcnMS1fSUTI0MlaK1YFocSnNza2E63F2cTUyVohMTUTVYGJqBtfglZ+Rp+CSn6qg7ObuYWKq4JufV5KBotrcwlIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Calendar Meeting" = _t, ID = _t]),
    Table2CalendarMeetings = List.Buffer(Table2[Calendar Meeting]),
    Ad_Table2ID = Table.AddColumn(Table1, "Table2 ID", each 
        [ a = List.PositionOf(Table2CalendarMeetings, [Column1], Occurrence.First, (x,y)=> Text.Contains(x, y, Comparer.OrdinalIgnoreCase)), //First match position
          b = if a = -1 then null else Table2[ID]{a}
        ][b] )
in
    Ad_Table2ID

[Caelan]

Hi, do you expect there to be more than 1 match for any row in table A? And if so, how do you want it to handle that?

I would merge query B with query A, and select column 2 from query B. The type of join you want would depend on what relationship you believe exists between the tables (you can test if the relationship is what you expect after, if necessary).

[mikelebo99]

No, each row should only have one match. 

[kpost]

when you say "Search", do you mean that they don't have to match exactly, so you can't just do a left join? 

[mikelebo99]

Here is a real world example. 

The subject of the calendar meeting might say "JOC Mayo Clinic #ABC12 Quarterly Meeting".

My other column from table 2 has "ABC12" and another column that has an ID of 213ABC456. 

I need to search "ABC12" with the subject string in Table 1 have Table 1 display in a new column the ID from Table 2. 

I have tried this - 

maxx(filter( Table2, search(Table1[Program Name], Table2[Assigned Program],,0) >0 ),Table2[LOB]) but it is just giving me blanks. 

Thanks!

[kpost]

Are you guaranteed to have a hashtag before the value?

If so, could you create a custom column like this that extracts the first string following a hashtag, then do a left join on that column (given the fact that you said the second table is guaranteed to have only one match), or at least get rid of the necessity to "search" the string, and instead you can just match it exactly using a calculated column if you don't want to merge or join the tables?

In either case I think this would simplify things.

 

[mikelebo99]

I wish that was the case, but no, it might or might not be there. 

[v-jingzhan-msft]

Hi @mikelebo99 

You may try my solution as below. I also attached a demo pbix at bottom. Hope this would be helpful. 

Table A:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vJ3VvBNrMxXcM7JzMtMVlB2dHI2NFIILE0sKkktyqlU8E1NLcnMS1eK1cGh2ASb4lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Subject = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Subject", type text}}),
    Custom1 = List.Zip({#"Table B"[Name],#"Table B"[ID]}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let vSubject = [Subject] in List.Last(List.First(List.Select(Custom1, each Text.Contains(vSubject, _{0})))))
in
    #"Added Custom"

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[mikelebo99]

Ive been trying to get this to work but running into an expression error.

= Table.AddColumn(#"Renamed Columns2", "SFID", each let vsubject = [Subject] in List.Last(List.First(List.Select(Custom1, each Text.Contains(vSubject, _{0})))))

Expression.Error: The name 'vSubject' wasn't recognized. Make sure it's spelled correctly.

[v-jingzhan-msft]

@mikelebo99 In power query, it is case-sensitive. You need to keep these two places Case consistent. Otherwise it will be treated as two names which caused this error. 

In addition, the custom list is to extract the Name and ID columns from Table B to be used in Table A. It won't affect any visuals if you directly use Table B columns in them. Do you have other columns in Table B that also need to be brought into Table A? 

Best Regards,
Jing

[mikelebo99]

Getting close as I was able to make some progress fixing the capitilization. The new column is getting created as a table and it does not let me expand the table to see the value. 

On the visual it is appearing like this. SFID in your example is Table B "Custom".

= Table.AddColumn(#"Renamed Columns2", "Custom", each List.Zip({#"PHM Assignment List"[Payee FB Number], #"PHM Assignment List"[Account ID]}))

Followed by:

= Table.AddColumn(Custom1, "SFID", each Table.AddColumn(#"Renamed Columns2","Account ID", each let vSubject = [Subject] in List.Last(List.First(List.Select(Custom1, each Text.Contains(vSubject, _{0}))))))

[v-jingzhan-msft · Accepted solution]

Hi @mikelebo99 

"Custom1" is not created with adding a custom column. It is created in the formula bar directly just like below. You can right click #"Renamed Columns2" step, select "Insert Step After" then modify the code in the formula bar. 

For the second step, it is created with adding a custom column. But it is not based on the "Custom1" step. Instead, it is based on the previous step of "Custom1". You need to modify the step name. 

You can also edit the code in Advanced editor directly. It looks like this.

Best Regards,
Jing

[mikelebo99]

That absolutely worked! Thank you for the help!!!!

[mikelebo99]

The custom list also broke my visuals as I have other columns that are referenced.