Compare two cells using DAX or Power Query

[PSB]

I want to check if all values in Souce column exist in Target Column.

If I can get True or False, it's good. 

If I can get Exact value that's missing, it's better.

Cell_Id	Source	Target	Check Source in Target?	Check Source in Target?
KNJ01002A11	389090,507870,520110,126490	389090,126490,505950,520110	FALSE	Missing 507870
KNJ01002A21	389090,507870,126490,520110	389090,126490,505950,520110	FALSE	Missing 507870
KNJ01002A31	389090,507870,126490,520110	389090,126490,505950,520110	FALSE	Missing 507870
ANJ01002A11	389090,507870,505950,520110,126490	389090,126490,505950,520110	FALSE	Missing 507870
ANJ01002A21	389090,507870,505950,520110,126490	389090,126490,505950,520110	FALSE	Missing 507870
ANJ01002A31	389090,507870,505950,520110,126490	389090,126490,505950,520110	FALSE	Missing 507870
ANJ01002A12	505950,520110	389090,126490,505950,520110	TRUE	All Exist
ANJ01002A22	505950,520110	389090,126490,505950,520110	TRUE	All Exist
ANJ01002A32	505950,520110	389090,126490,505950,520110	TRUE	All Exist

[PSB · Accepted solution]

your solution worked perfectly. Can't thank you enough for your help.

Only thing I need is "," between the result like shown below. 

Actual Result:  Missing 126490389090

Result I need:  Missing 126490,389090

[grantsamborn]

Hi @PSB 

I made a couple of transformations in Power Query:

- separate Source from Target

- unpivot both tables

- create DimCell

After that, there is a single measure to concatenate any values that weren't found.

Let me know if this works.

(edited to change link - I forgot the delimiter in CONCATENATEX.)

https://1drv.ms/u/s!AnF6rI36HAVkhPIcSilVrS2tpNBEQg?e=ApDvy6

[PSB · Accepted solution]

your solution worked perfectly. Can't thank you enough for your help.

Only thing I need is "," between the result like shown below. 

Actual Result:  Missing 126490389090

Result I need:  Missing 126490,389090

[grantsamborn · Accepted solution]

Hi @PSB 

I updated the link yesterday to reflect the changes to CONCATENATEX's delimiter.

I hope it works for you.

[PSB]

I want to flag as true even if source is "123-234" and Target is "012-123-234-013"

[Idrissshatila]

Hello @PSB ,

You could add a calculated column with the following measure 

Check Source in Target = IF('Table'[Source] = 'Table'[Target], "True","false")

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin