Forum Discussion

Anonymous

Not applicable

9 years ago

Solved

Count textcodes in a combined column

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
9 years ago
Hi Anonymous,

Yes, the formula below should work in this scenario. :smileyhappy:

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

Regards

3 Replies

v-ljerr-msft
Microsoft Employee
9 years ago
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. :smileyhappy:

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

Regards
- Anonymous
  Not applicable
  9 years ago
  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
    Microsoft Employee
    9 years ago
    Hi Anonymous,
    
    Yes, the formula below should work in this scenario. :smileyhappy:
    
    =List.Count(List.PositionOfAny(List.Transform(Text.Split(Text.Trim([Column1]),";"),each Text.Start( _ , 4)),{"RCOM"},Occurrence.All))
    
    Regards