cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Relating Data using a list of comma separated values

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

1 ACCEPTED SOLUTION
Super User III
Super User III

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

View solution in original post

2 REPLIES 2
Super User III
Super User III

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

View solution in original post

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 !

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors