Forum Discussion

mumair's avatar
mumair
Helper I
9 years ago
Solved

How to Count Multiple Values in the Same Cell

The way we imported the data set wasn't the best and now we are left with a column that looks something like this:

 

Failed Tanks

5S, 5P

1SP, 2SP, 3SP

N/A

7S

 

And so on. The cells which have an N/A or just one tank is not a problem. The cells that have 2 or more tanks though are confusing me. I was wondering if there was a way to output the following:

 

Count of Failed Tanks

2

3

0

1

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi mumair,

     

    I think power query will be suitbale to achieve your requirement:

     

    Logic: if "Failed Tanks" equal to 'N/A', return 0; other cases, split text with ',' to list and count it.

     

        #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each if [Failed Tanks]<> "N/A" then List.Count(Text.Split([Failed Tanks],",")) else 0)

     

     

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi mumair,

     

    I think power query will be suitbale to achieve your requirement:

     

    Logic: if "Failed Tanks" equal to 'N/A', return 0; other cases, split text with ',' to list and count it.

     

        #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each if [Failed Tanks]<> "N/A" then List.Count(Text.Split([Failed Tanks],",")) else 0)

     

     

    Regards,

    Xiaoxin Sheng