Count how often a list of strings occur in another tables fields

[nicoolai]

I have two tables:

Table A with a list of identifiers, nothing more.

Table B with a list of TEXT queries.

I want to add a column to Table A, that counts how many entries in Table B that contains the identifier (from Table A).

Does this make sense?

How can I do this in power query, in Table A? 

Is this even a good way to get this done? Should I be doing this in some other way?

I can't really think of a way to link the tables as Table B is all dynamic free text query logs that MIGHT contain the identifiers from Table A (zero or many times).

[AMeyersen · Accepted solution]

@nicoolai ,

if the amount of data is fairly small, you can brute force it:

1. do a cross join from Table A with Table B
   https://learn.microsoft.com/en-us/power-query/cross-join
2. Expand Table B
3. Filter "text column contains ID column"
   = Table.SelectRows(#"previous step", each Text.Contains([TableB.LogEntries], [TableA.IDs]))
4. group by IDs and count rows

Before you try this approach, calculate the number of records the cross join produces ([#Rows TableA] * [# Rows TableB]). If this number is huge (hundreds of millions of rows) this is not going to work.

[Vijay_A_Verma]

Need the sample data for both the tables to provide the right solution.