Forum Discussion
Solved
Count text values across multiple columns for each row
Hello all, I have searched the forum for a solution to this but haven't managed to find what I need.
I have the following dataset and need to count the occurrences of each two letter code in columns [Spot 1] to [Spot 10] for each row. The result I'm hoping for is shown in the final 5 columns, as highlighted in bold.
| Site | Category | Spot 1 | Spot 2 | Spot 3 | Spot 4 | Spot 5 | Spot 6 | Spot 7 | Spot 8 | Spot 9 | Spot 10 | NO | EA | CL | RS | SC |
| A | A | NO | NO | NO | NO | NO | NO | NO | NO | NO | NO | 10 | null | null | null | null |
| A | B | EA | EA | EA | CL | EA | EA | EA | EA | CL | EA | null | 8 | 2 | null | null |
| B | A | SC | NO | NO | NO | NO | NO | NO | NO | NO | NO | 9 | null | null | null | 1 |
| B | B | RS | RS | RS | RS | CL | CL | RS | RS | RS | RS,CL | null | null | 3 | 8 | null |
It is complicated by there being some occurrences of two codes, separated by a comma, as you can see in the last row for [Spot 10].
I'd prefer to do this in the Query Editor. Any help would be greatly appreciated!
In that case
=List.Count( List.Select( Text.Split( Text.Combine( List.Transform(Record.ToList(_),each Text.From(_)) ,",") , "," ),each _ ="CL") )
14 Replies
Hi EugenioJunior, yes I could do that. So in the example, [Spot 10] is transformed into two columns; [Spot 10.1] and [Spot 10.2]
So it worked?
