Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

Need Help
  • lbendlin's avatar
    lbendlin
    6 years ago

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

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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 !