Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
ID | Codes |
123456 | ~AA BB CC DD |
123457 | ~AB CC DD ZA |
123458 | ~YX BZ RN AA BB CC DD |
Solved! Go to Solution.
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.
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.
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).