check if a value in table 1 exist in table2

[Anonymous]

Hello,

I have two tables.  Table1 contains let's say 15,500,000 rows and table2 contains 15,263,123 rows.

In table 1, I have the field UniqueKey1 and in Table 2 I have the field UniqueKey2.

In principle, all the UniqueKey2 values should be found in UniqueKey1.  I want to set a flag1 to check if the UniqueKey1 exist in UniqueKey2. 

As my two tables are huges, I limited by the memory issue of power bi. 

What's the best way to do that?

Thanks in advance for your help

[Pablinho]

Hello. Is this being done in power Query? I am using it but I got a missing coma error.

Thanks!

[AlB · Accepted solution]

Hi @Anonymous 

I would created a calculated column:

NewColumn =
IF ( Table1[UniqueKey1] IN DISTINCT ( Table2[UniqueKey2] ), 1, 0 )

Since the large majority of NewColumn values will be 1, the engine will be able to compress that NewColumn a lot and thus it won't take up too much additional space.

[Anonymous]

Hello,

I have test your solution it works perfectly.  However, as I am new Power BI user, could you please explain me how this code works because I have never use distinct in?

Thanks for your help.

[AlB]

Sure.

DISTINCT gives you all the distinct  values in the column, in this case all UniqueKey 2.

Then, for each row in the first table, we check whether that UniqueKey1 is in the list of Unique Keys 2,. If so, we return a 1; otherwise a 0. If you find this helpful, please consider kudoing the posts. Check these out:

https://dax.guide/op/in/

https://dax.guide/distinct/ 

Cheers