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
Anonymous
Not applicable

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
artemus
Employee
Employee

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
artemus
Employee
Employee

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.

Anonymous
Not applicable

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?

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

Top Solution Authors
Top Kudoed Authors