making relation between tables when reference column values in other table are appended with comma

[Bokbob]

Hi All, I have 2 tables one is with list of all contacts and other is the events table where it will reference one or more contacts as attendees.

And in the events table multiple contacts are stored in single column with comma appended to the names. (i wont have any contact ID stored in events table except their names unfortunatly)

For my report i need have a contact filter where they should be able to select a contact and look at any events they participated in.

How would i link/have a relation between these 2 tables when the values are multiple/dynamic in events table.

Please help me.

[v-yuta-msft]

Hi Bokbob,

I have made a sample data with table ‘Contacts’ and table ‘Events’ based on your description like below:

To make relationship between them based on column [Contacts], click Edit Queries-> table ‘Events’ -> Transform -> Split Column by Delimiter like below:

The result is like below and then you can create a relationship between the two tables based on ‘Contacts’[Contacts] and ‘Events’[Contacts.1].

Regards,

Jimmy Tao

[Bokbob]

hi Jimmy, I appriciate your reply.

Please see my sample data. 

So how to have a relation between these 2 tables based on the names in the first image as names could be more than 1 and can be any in number appended with ','

Please help.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.