Getting occurence of multiple values in a single column

[Anonymous]

ok so the idea of what I'm trying to do is:

-----------------------------------------

Name             animals

Dan                 dog,cat

doug              cat

jim                  cat,sheep,dog

-----------------------------------

then use that table to create this one

-----------------------------------

animals          amount

dog                2

cat                  3

sheep             1

------------------------------

I know best practice would be to put them in seperate columns but this was the data I was given

[dm-p · Accepted solution]

Hi @Anonymous,

Your ideal solution is getting close, although you should have one row per animal per name for greater flexibility. You can make some changes by editing the query for your data.

I've mocked up your data and will walk-through for you.

• From the query editor, select the animals column.
• In the ribbon, select Transform > Split Column > By Delimiter
• Ensure that comma is specified, and Each occurrence of the delimeter, e.g.:
  
  
• Click OK. You'll now get separate columns for each occurrence, e.g.:
  
  
• Now, we need to ensure we get the right number of records and consolidated values. Right-click on the Name column and choose Unpivot Other Columns, e.g.:
  
  
  The reason we do other columns rather than selected columns is that later on you might have more than three animals per row, and doing it on the Name column just works on whatever's left after splitting by delimiter.
• Now, you'll have a row per name and value, e.g.:
  
  
• Now, we just clean-up the artifacts. Delete the Attribute column and rename the Value column to Animal, e.g.:
  
  
• Now, you can Close & Apply your query and the data will be loaded.

At this point, you shoud be able to just add the Animal and a count to the report canvas, e.g.:

And you should be good to go. Good luck!

Daniel

---

If my post helps, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂