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|
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!
Solved! Go to Solution.
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.
Thanks @Zubair_Muhammad for the file but I cannot open it, I get a message saying 'it's incompatiable with your current version of Power BI' (my company is using October 2018).
Is there anything else we could try?