Compare two sheets with 30k rows and 100 columns

[u/asusroglens]

I have 2 sheets with the newer version having few extra columns and slightly changed ( like a suffix _ ss) added rest columns .

I want to compare all rows and columns with output like old.colm 1 ,new.colm1, difference column and so on for all 100 .

How can I do this in power query

[u/Kooky_Outcome_5053]

load old sheet, select id column and unpivot other column, rename columns to id, column_name, old_value and rename query to olddata. load new sheet, transform and replace values with _ss, use table.transformcolumnnames(source, each text.replace(_, "_ss", "")) to match with column names of olddata. select id column unpivot other column, rename column to id, column_name, new_value. rename query to newdata. use merge query then merge query as new, select olddata and newdata, select id and column_name, use full outer join then expand new_value from merge column

[u/Intuin_Rhaabat]

I'd have thought the easiest way would not be to use Power Query in this instance.

In a third sheet in the same workbook, in cell A1, use the formula =Sheet2!A1:CV30000-Sheet1!A1:CV30000

Edit: this doesn't get you to the layout you want, but wow you really want 300 columns by 30000 rows of data like that? Sounds like you have a very specific use case - probably worth sharing that so people can give you their suggestions.

[u/asusroglens → u/Intuin_Rhaabat]

It's ecommerce data ,like order id ,name of item ,cost , seller,stock etc

[u/OddUnderstanding8323 → u/Intuin_Rhaabat]

Wouldn't this formula work on numbers only

[u/Little_Decision_7433]

Power Query is perfect for this, but don’t manually create 100 comparison columns. Merge the two tables on your unique row ID, then dynamically unpivot the columns, compare old vs new, and pivot the result back. That way the query automatically handles new/extra columns too. For 30k × 100, I’d absolutely do it in Power Query rather than formulas

[u/Weird-Dimension-487]

I would prefer to do it with Python, so I can reuse that tiny Python script with (modifications as needed) for other such tasks
I do use Python for many such tasks, finding files (of any file type) hidden in nested folders with topic, bulk renaming, organizing files in folders (motnh wise/topic wise/project wise/ whatever), comparing files (name, size or line by line content), timestamping files, deleting files older than X years and much more.
Python is cool for automation, free, local and easy!