Count number of text cells containing string, for each string in a table column

[amineregask]

Hi everyone,

I would need your help on the following case. I have 2 tables:

• Table 1: contains a column with a list of countries
• Table 2: contains a column with text elements

I would like to know, for each country in table 1, the number of text elements in table 2 that contain at least one occurrence of that country.

In the example below, the desired output will be:

• Mexico: 2 occurrences (appears in 3rd and 4th items in table 2, even if it appears more than once in the 3rd item)
• Japan: 1 occurrence (1st item in table 2)
• Singapore: 1 occurrence (3rd item in table 2)
• Rwanda and Chile: 0 occurrence

Thank you in advance for your help, much appreciated!

[watkinnc]

Actually it looks like my typo is that it should read in Counts, not in counts...

let

Country2 = Table2[Text],

NewTable = Table1,

Counts = Table.AddColumn(NewTable, "Total", each List.Count(List.FindText(Country2,  [Text]))

in Counts

--Nate

[watkinnc]

let

Country2 = Table2[Text],

NewTable = Table1,

Counts = Table.AddColumn(NewTable, "Total", each List.Count(List.FindText(Country2,  [Text]))

in counts

--Nate

[Vera_33]

Hi @amineregask 

If your Table2 and Text column are different names, please change it accordingly

(x)=> List.Count(List.Select(Table2[Text], each Text.Contains(_,x[Country])))

[amineregask]

Hi @Vera_33 thanks a lot for your quick reply!

When I create the custom column, I get "function" values in it. Did I miss any step?

Thank you!

[Vera_33 · Accepted solution]

Hi @amineregask 

You need to remove that "each" after "Custom", before (X)=>

= Table.AddColumn(#"Changed Type1", "Custom",  (x)=> List.Count(List.Select(Table2[Text], each Text.Contains(_,x[Country]))))

@watkinnc the way seems better, but there is a typo, you can try like this

Table.AddColumn(#"Changed Type1", "Custom", each List.Count(List.FindText(Table2[Text],[Country])))

[amineregask]

Hi @Vera_33 @watkinnc 

Both solutions work, many thanks!!