Relating Data using a list of comma separated values

[Anonymous]

Hi,

I'm relatively new to Power Query and I'm just trying to get my head wrapped around a problem, so I'm looking for some general advice.

I have a table of 5k People. One of the columns is "Events", which is a string of comma-separated ID numbers, each number corresponding to a entry on the Event table.

The Event table has 40 Events with 3 columns: ID, Name, and Type. There are 4 event types.

I want to find out how many events of each type each person has been to.

So in general I need to break up a person's string of comma-separated event IDs, match each event ID to the Event table and find out what kind of event it was, and the keep a running tally of events of each type each person has been to.

Any thoughts on the best way to get this? I could resort to VB for this, but I'm trying to learn Power Query so I'm going to see if I can solve it this way if possible.

Thanks,
Dan

[lbendlin · Accepted solution]

Select the events column of your people table, select "Split Column by Delimiter", specify comma as the delimiter, and then under Advanced options select "Split into Rows" .

[Anonymous]

Ah, OK, so from that I can make a join table, People-Events. From there I played around with merging tables and pivot columns and now have it giving me the desired results. I'm sure I can work on optimization because it takes quite some time to process, but this definitley was a nudge in the right direction. Thanks, @lbendlin !