cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tgjones43 Regular Visitor
Regular Visitor

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.

 

SiteCategorySpot 1Spot 2Spot 3Spot 4Spot 5Spot 6Spot 7Spot 8Spot 9Spot 10NOEACLRSSC
AANONONONONONONONONONO10nullnullnullnull
ABEAEAEACLEAEAEAEACLEAnull82nullnull
BASCNONONONONONONONONO9nullnullnull1
BBRSRSRSRSCLCLRSRSRSRS,CLnullnull38null

 

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!

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Count text values across muliple columns for each row

@tgjones43 

 

In that case

 

=List.Count(
                 List.Select(
  Text.Split(
Text.Combine(
List.Transform(Record.ToList(_),each Text.From(_))
        ,",")
,
","
                ),each _ ="CL")
)
14 REPLIES 14
EugenioJunior Frequent Visitor
Frequent Visitor

Re: Count text values across muliple columns for each row

Hi, @tgjones43 

 

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

tgjones43 Regular Visitor
Regular Visitor

Re: Count text values across muliple columns for each row

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 Frequent Visitor
Frequent Visitor

Re: Count text values across muliple columns for each row

So it worked?

Super User
Super User

Re: Count text values across muliple columns for each row

@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 Regular Visitor
Regular Visitor

Re: Count text values across muliple columns for each row

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.

Super User
Super User

Re: Count text values across muliple columns for each row

@tgjones43 

 

In that case

 

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

Re: Count text values across muliple columns for each row

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

 

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

Super User
Super User

Re: Count text values across muliple columns for each row

Hi @tgjones43 

 

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

 

Let me know if it helps

 

 

tgjones43 Regular Visitor
Regular Visitor

Re: Count text values across muliple columns for each row

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?