Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EricRF
New Member

Count occurence of individual characters in a column with multiple characters per record

Hello,

I need to count the number of times each number 1-8 occurs in a column. The rows can contain multiple numbers and I need to count each number individually. For example:

RecordType ID
A-0011
A-0023
A-003451
A-004384621
A-0052
A-006654

 

This is what I am hoping to achieve

Type IDCount of Type ID
13
22
32
43
52
62
70
81

 

There are thousands of rows. Each Type ID corresponds to a label from a second table. I have been trying for days to figure this out but haven't found any posts or information to solve. Appreciate any guidance you all can provide.

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Using the Query Editor, split the digits into rows.  See image below

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

Using the Query Editor, split the digits into rows.  See image below

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I have tried separating but have been unsuccessful in coming up with a way to count the number of each value across 20 columns. Do you know of a way to count the instances across the newly created columns?

You have changed the question now.  Share some representative data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Question hasn't changed, the solution you provided was to split the column, which does not solve for the original. If I separate the column, then I would have this:

EricRF_0-1663679772781.png

If you read the original question, I am looking for a count of the Type ID, like this:

EricRF_1-1663680010760.png

I need to display the number of times the Type ID, 1 - 8, appears in the column(s). Hope this clarifies it for you.

Hi,

I have definitely read and understood your question clearly but you have not seen my screenshot clearly.  You have to split by rows (not by columns).


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

My apologies. I did indeed not look closely enough. I duplicated my table, as splitting the columns into rows caused relationship issues with the original. That worked perfectly with the new table, thank you so much.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
TomMartens
Super User
Super User

Hey @EricRF 

this DAX statement creates a table

 

Table 2 = 
ADDCOLUMNS(
    SELECTCOLUMNS(
        GENERATESERIES( 1 , 8 , 1 )
        , "Type ID" , [Value]
    )
    , "Count of Type ID"
        , var currentTypeID = [Type ID]
        return
        
            COUNTROWS( 
                FILTER(
                    'Table'
                    , CONTAINSSTRING( FORMAT( 'Table'[Type ID] , "" ) , FORMAT( currentTypeID , "" ) )
                )
            )
)

 

 

This is the result:
image.png

This solution assumes that each number appears just once in the concatenated "Type ID" otherwise the solution will become more complex.


Hopefully, this provides what you are looking for.

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

I appreciate the solution, but am not successful in implementing. First build with Power BI, so please excuse my ignorance. Also, there are not multiple instances of the same number in a row. Here's what I'm actually working with for greater context.

 

Table - Coaching

Field - AssignedTipsID.1

Values in the field are stored as text if that matters.

 

This is what I used based on your response:

EricRF_0-1663364693345.png

 

This did created a new table, 'TipsCount'. This is the output I get:

EricRF_2-1663365334991.png

 

EricRF_1-1663365219519.png

 

Am I doing something wrong here?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.