Large data in Power Query

[tiredofit]

Excel has a 1 million row limit.

Power Query can handle a lot more rows.

My understanding is that to manipulate data, one can use Power Query, then close and load back into Excel.

However if after manipulating the raw data, the size of the cleaned data exceeds 1 million rows, how can it be loaded back into Excel?

 

[RoryA]

It can't, unless you can break the query up so that you use multiple sheets. Why do you want to put it all on a worksheet?

 

[tiredofit]

It can't, unless you can break the query up so that you use multiple sheets. Why do you want to put it all on a worksheet?

I don't but my non-technical stakeholders want to see the cleaned data that makes up the final output report!

 

[RoryA]

Are they really going to go through several million rows of data to verify it? You're probably better off picking sections of the data to restrict the number of output rows and have them look through that.

 

[tiredofit]

Are they really going to go through several million rows of data to verify it? You're probably better off picking sections of the data to restrict the number of output rows and have them look through that.

They think they can spot some trends.

Besides, you know the customer is always right,

Fortunately I have some code to split the data onto several worksheets, so that'll satisfy them for the time being.