xplode data in google sheet

Question

I got a table like this

Category is a multiple selection, the count simply is the number of occurrences. I want to tranform to this table

.

In python, i can use xplode function. But u want using formular or appscript in googlesheets to solve this problem. I try arrayformular, and split. But it's not worked

Answer 1

Here's a possible solution:

=ARRAYFORMULA(QUERY(SPLIT(TOCOL(SPLIT(A2:A4,",")&"|"&B2:B4),"|"),"where Col2 is not null"))