How to Count Multiple Values in the Same Cell

[mumair]

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

[Anonymous · Accepted solution]

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

[mumair]

Thank you! Worked perfectly