CODEWITHSUNDEEP
powerquerym
Fabio Manniti
Fabio Manniti

Reputation: 171

Power Query M: Count in how many rows a certain substring occours

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

Upvotes: 0

Views: 1248

Answers (1)

horseyride
horseyride

Reputation: 21318

Add column ... custom column ... name it children, use formula

= List.Count(List.FindText(#"PriorStepNameHere"[path],[id]))-1

Upvotes: 1

Related Questions