'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
Solution 1:[1]
Add column ... custom column ... name it children, use formula
= List.Count(List.FindText(#"PriorStepNameHere"[path],[id]))-1
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | horseyride |