Importing Data from power Query larger then 1million Rows

[earthworm]

I have Data in 2 sheets . Row count 1 million + . First I need to append the data through power Query and remove duplicates. Then I want to export the data back into excel table. Since when data is merged in appended table in power Query and size has increased , I can't export back to excel table. Please help because there is a row limitation on single sheet. Is there any option that can automatic export data into respective sheet when data row size exceeds single sheet ?

 

[johnny51981]

Make sure whatever queries you have created that aren't the final result that you are wanting to use are set up as Connection Only. They will still exist in your Power Query editor, they just won't load to any location in your workbook of sheets.

Then, I would recommend splitting up by something like a unique ID or transaction date, something that is static once applied to that line of data. Then create the same query multiple times (currently twice) with each being filtered to represent all segments of the split. Example, query 1 would be filtered for transaction dates on or before 12/31/2022, query 2 would be filtered for transaction dates on or after 01/01/2023.

They will then load to your workbook of sheets as individual tabs with their own data row count limit.