Count text values across multiple columns for each row

[tgjones43]

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!

[EugenioJunior]

Hi, @tgjones43 

What if you slice when there are two records for the same column?

[tgjones43]

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]

[EugenioJunior]

So it worked?

[Zubair_Muhammad]

@tgjones43 @EugenioJunior 

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 · Accepted solution]

@tgjones43 

In that case

=List.Count(
                 List.Select(
  Text.Split(
Text.Combine(
List.Transform(Record.ToList(_),each Text.From(_))
        ,",")
,
","
                ),each _ ="CL")
)

[tgjones43]

Thanks @Zubair_Muhammad, it is giving the following error though:

Expression.Error: The name '_' wasn't recognized. Make sure it's spelled correctly.

[Zubair_Muhammad]

Hi @tgjones43 

Please see the attached file's Query Editor for the custom column.

Let me know if it helps

[tgjones43]

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?

[Zubair_Muhammad]

Could you show me the screen shot of your custom column

[tgjones43]

Sure, here it is

[Zubair_Muhammad]

@tgjones43 

You need to place the formula, I provided above in a new custom column as shown in pic below

[tgjones43]

@Zubair_Muhammadthat has done it, thank you so much.

Why does the formula need to be presented like that, rather than on one line like I had it?

[Zubair_Muhammad]

Hi @tgjones43 

The formula bar shows the Complete M Code for the step.
The Custom Column just shows the formula part

You can also view the entire code for the Query from View Tab>>Advanced Editor.