Create a column that counts occurrences of a string in another table's column in PowerQuery

Question

I'm trying to organize my data and extract only the necessary information for a Power BI dashboard. I want to create another table, and in this table I will manipulate some data and display the occurrence of a particular set of characters in the column in Table 1 and put the number of occurrences in table 2.

How can I do that? I tried using "Conditional Column" but I can only use data within the table I'm working on.

I tried creating a new table with conditional column, but I can only manipulate data within the table I'm working on, and can't import data from other tables.

Answer 1

Does this do what you need?

If not, please explain better with a sample of what you are trying to do

let Table1 = Table.Buffer(#table({"Key2"},{{"Auto Cat"},{"Bus Car"},{"Car"}, {"Fog"}})),
Table2 = Table.Buffer(#table({"Key1"},{{"Auto"},{"Bus"},{"Cat"},{"Dog"}})),
ZMerge = Table.AddColumn(Table1, "RelativeJoin", (zz) => Table.SelectRows(Table2, each Text.Contains(zz[Key2],[Key1], Comparer.OrdinalIgnoreCase))),
#"Expanded RelativeJoin" = Table2 & Table.ExpandTableColumn(ZMerge, "RelativeJoin", {"Key1"}, {"Key1"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded RelativeJoin", each ([Key1] <> null)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Key1"}, {{"Count", each Table.RowCount(_)-1, Int64.Type}})
in  #"Grouped Rows"