cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Counting multiple characters in a single column

Hi, so I have a column which is ordered by ID numbers and in each row there is often multiple "codes" (each code is always 2 letters long seperated by a space . I have included a table of what these look like. I would like to be able to count the frequency in which each code turns up and also create a calculated column which tells me the last code in each string. Any ideas on how I could go about this.

 

Example:

IDCodes
123456~AA BB CC DD
123457~AB CC DD ZA
123458~YX BZ RN AA BB CC DD
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Counting multiple characters in a single column

First the easy question: How to get a column with the last code:

Click the column, then in the Add column tab, choose extract -> Text after delimiter. Enter space as your delimiter, and in advanced options, choose from end of input.

 

Now for the frequency of the  codes:

In a new query, create a new blank query with:

= MyTable[[Codes]] 

Where MyTable is the name of your query with the codes

 Now go to Transform -> Extract -> Text After delimiter, and enter in ~ to remove it from the column

Next add a new custom column, call it CodeList with the formula:

= Text.Split([Codes], " ")

Now add a new custom step (right click the query step list and choose insert step) with:

= List.Combine(#"Added Custom"[CodeList])

Assuming that Added Custom was the name of your previous step.

Next, click ToTable button with default options to turn it back into a table.

Finally, choose Group By, and leave the default options. This should give you the table of 2 letter code to count.

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: Counting multiple characters in a single column

First the easy question: How to get a column with the last code:

Click the column, then in the Add column tab, choose extract -> Text after delimiter. Enter space as your delimiter, and in advanced options, choose from end of input.

 

Now for the frequency of the  codes:

In a new query, create a new blank query with:

= MyTable[[Codes]] 

Where MyTable is the name of your query with the codes

 Now go to Transform -> Extract -> Text After delimiter, and enter in ~ to remove it from the column

Next add a new custom column, call it CodeList with the formula:

= Text.Split([Codes], " ")

Now add a new custom step (right click the query step list and choose insert step) with:

= List.Combine(#"Added Custom"[CodeList])

Assuming that Added Custom was the name of your previous step.

Next, click ToTable button with default options to turn it back into a table.

Finally, choose Group By, and leave the default options. This should give you the table of 2 letter code to count.

View solution in original post

Highlighted
Frequent Visitor

Re: Counting multiple characters in a single column

Thank you for this. I have had another problem arise. Was wondering if it is possible to keep these counts linked to an identity for filtering purposes?

Microsoft
Microsoft

Re: Counting multiple characters in a single column

Yea, it is possible.

 

In the first step, just reference the table instead of only taking the Codes column. I.e. instead of MyTable[[Codes]] use MyTable

 

Then after you do the split, your next step would be to expand list column. The remaining step in group by would be the same, except you would group by 2 columns (id and code).

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors