Computation time to compare 2 tables _ 100k rows each _ 150 columns each _ 6.5 mln rows as a result

[MasterSonic]

Hi Guys,

I found solution for my problem on this forum before.

Having 2 tables to compare ( 2 tables _ 100k rows each _ 150 columns each )
- I just unpivoted them on key column, inner joined on key and columns name  + added  cond. column to compare columns.

Key

Column names

Value table 1

Value table 2

value table1= value table2 yes/no

Problem is 6.5 mln of rows, and merge query loading 2hrs on deskopt ( sharpoint will be worse ?!!!)

Is there any other way to compare them ? Any idea

[Anonymous · Accepted solution]

HI @MasterSonic,

I'd like to suggest you only do unpivot columns on the table value fields and return to the data view side.
Then you can create a calculated table to extract the unique id and attribute fields value as relationship keys to link two tables.

Relationship in Power BI with Multiple Columns - RADACAD

BTW, does any advanced operation apply to these tables? (merge, combine, invoke custom function, recursion calculations) They will spend memory resources looping calculate through each row and cause the performance issue. You can try to add buffer functions to reduce the spending.

Chris Webb's BI Blog: New Options For The Table.Buffer Function In Power Query Chris Webb's BI Blog ...

Regards.

Xiaoxin Sheng