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

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 III
Super User III

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")
)
Try my new Power BI game Cross the River

View solution in original post

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 Member
Member

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 III
Super User III

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"))

 

Try my new Power BI game Cross the River
tgjones43 Member
Member

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 III
Super User III

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")
)
Try my new Power BI game Cross the River

View solution in original post

tgjones43 Member
Member

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.

Highlighted
Super User III
Super User III

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

 

 

Try my new Power BI game Cross the River
tgjones43 Member
Member

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?

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors