Count in how many rows a certain substring occours

[fabiomanniti]

I have this type of table in Power BI:

ID |	isPromoter |	path |
aaa-111-000 |	false |	sqe-432-w14/2aq-4ec-t66/aaa-111-000/ |
sss-342-r34 |	true |	a3e-543-1sd/34d-245-svt/s3a-bnj-klo/sss-342-r34/ |
hhy-e90-y7u |	false |	a3e-543-1sd/34d-245-svt/s3a-bnj-klo/sss-342-r34/hhy-e90-y7u/ |
... |	... |	... |

So, as you can see, the second id is contained in both in the second and in the third path; this only can happen if the user is a Promoter.

I would like another field which counts how many times each id is contained in all paths (beside itself); so it should be 0 if the user is not a promoter and >0 if it is.

ID|	isPromoter|	path |	children |
aaa-111-000 |	false |	sqe-432-w14/2aq-4ec-t66/aaa-111-000/ |	0 |
sss-342-r34 |	true |	a3e-543-1sd/34d-245-svt/s3a-bnj-klo/sss-342-r34/ |	3 |
hhy-e90-y7u |	false |	a3e-543-1sd/34d-245-svt/s3a-bnj-klo/sss-342-r34/hhy-e90-y7u/ |	0 |
... |	... |	... |	... |

I know that there is the function Text.Contains([path],[id]) but it is only true for the current row. I don't know how to do the count for all rows

[fabiomanniti · Accepted solution]

I figured it out by myself

I added a custom column and this is the code

if [ispromoter] = 1
then List.Count(List.FindText(#"PriorName"[path],[id]))-1
else 0