Compare two table with million of rows - Power Query

[Anonymous]

Hello,

I want to compare two tables and find the rows which are not in the first table. I was already able to do this with smaller tables (hundreds of thousands)

The problem i have now is that i have to split columns into rows by delimiter " / " which leads to an exploding variance. The target table is often only a few thousand rows long but the actual tables most often explodes into millions of rows.

Example 1:

Example 2:

Is it possible to compare example 1 with example 2 without splitting the rows of example 1 inorder to keep the variance small?

[TeigeGao]

Hi @Anonymous ,

Could you please tell us why will you compare it in Power Query? Actually, it is not convenient to handle these data in PowerBI, for me, I will handle them in the database like SQL Server.

It will take much resource to handle it in PowerBI, besides, it costs less resource when splitting the rows of example 1

Best Regards,

Teige

[Anonymous]

Hi @TeigeGao ,

I get the data as excel files. I use power query in excel because i have not used any other program for such a problem and I'm stuck on what i have on the company laptop. 

But I'm open to try other solutions like SQL.