Count textcodes in a combined column

[Anonymous]

Hello everyeone,

I have created a combined column in my table which contains textcodes from multiple columns. It looks like this :

RCOM; RPOT; RAM*; RBC*; RCOM

I would like to be able to count, for example, the number of times where the RCOM textcode appears in my column. How can I do that ?

[v-ljerr-msft]

Hi @Anonymous,

Based on my test, you should be able to use the following M query to add a new custom column in Query Editor under Add Column tab to get the the number of times where the RCOM textcode appears in your column. 

=List.Count(List.PositionOfAny(Text.Split(Text.Trim([Column1]),";"),{"RCOM"},Occurrence.All))

Regards

[Anonymous]

It works ! Thanks @v-ljerr-msft!

Do you think it's possible to do this on partial parts of text ?

If i have for example RCOM01, RCOM02, RCOM03, i would like to be able to count the number of times when "RCOM" appears...

Thank you again 🙂

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

Yes, the formula below should work in this scenario. 

=List.Count(List.PositionOfAny(List.Transform(Text.Split(Text.Trim([Column1]),";"),each Text.Start( _ , 4)),{"RCOM"},Occurrence.All))

Regards