Forum Discussion
tgjones43
Helper IV
Helper IVCount 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 colu...
In that case
=List.Count( List.Select( Text.Split( Text.Combine( List.Transform(Record.ToList(_),each Text.From(_)) ,",") , "," ),each _ ="CL") )
tgjones43Helper IV
Helper IVHi 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?
- Zubair_Muhammad
Community Champion
Just another way could be yo use this Custom Column
For example for CL
=List.Count( List.Select( Text.Split( Text.Combine(Record.ToList(_),","), "," ),each _ ="CL"))
- tgjones43
Thank you Zubair_Muhammad, that works nicely. Only problem is that, unlike in my example, I have a couple of numeric columns in my table, which lead to an error. Is there a way to ammend your code so that it looks at the [Spot] columns only?
Thanks EugenioJunior, the splitting advice helps, but it is the code to create the final five columns in my example that I really needed.
- Zubair_Muhammad
In that case
=List.Count( List.Select( Text.Split( Text.Combine( List.Transform(Record.ToList(_),each Text.From(_)) ,",") , "," ),each _ ="CL") )