Creating field in table 1 showing Yes or No depending on whether it exists in table 2

[jonclay]

Hi everyone

I have two tables in my CRM, one called Contact and the other called CP.  These are related on the contactid field using a Many to Many relationship with a Single Cross-filter direction.

The CP table contains a field called preferencesname which can contain one or many values (e.g. A or B or C etc).

I want to create fields in the Contact table which basically say "... if the CP table contains value A for a specific contactid then put Yes otherwise No". I would then do the same for value B, value C and so on, all in separate fields.

So, I want to end up with something like this for each contactid:

contactid	Name	Value A	Value B	Value C	Value D
12345	Jon	Yes	No	No	Yes

What would be the best way to acheive this please?

Many thanks
Jon

[Anonymous]

Hi @jonclay ,

Since you did mention many-to-many relationships, can you provide sample data as well as expected results?

Best regards,
Community Support Team_ Scott Chang

[aduguid · Accepted solution]

I'd do this in power query and pivot the column, preferencesname.