Forum Discussion

fabiomanniti's avatar
fabiomanniti
Helper III
4 years ago
Solved

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

Need Help
  • fabiomanniti's avatar
    fabiomanniti
    4 years ago

    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

1 Reply

  • fabiomanniti's avatar
    fabiomanniti
    Helper III
    4 years ago

    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