Power query freeze when data size changes

[KasperSommer]

Hi all, 

I have a Power Query connected to an Oracle ERP system of many small companies. 
In total there is around 100.000 - 600.000 transactions in the general ledger during a financial year. 

When I roll over my model to fit a new financial year, by just changing a filter, then the amount of transactions natually goes from 100.000 - 600.000 down to 1/12 amount af lines. 

When ever I do this, everything just freezes and no matter how long I wait, it will not complete the update. 

Why is this and is there a workaround ? 

[v-yingjl · Accepted solution]

Hi @KasperSommer ,

It may caused by the data size beacause in Power Query each step represents a single query from the data source so that when the data size is too large and you have many steps to query it, it would be freezed.

In this case, since you have connected to Oracle database, you can try that:

1. If you have used Import mode to connect, you can try to change it to Direct Query mode
2. You can filter data using SQL statement before transforming data in Power Query
3. If you have many steps which need to query the data source, you can try to use the buffer function like Table.Buffer() to avoid it.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vijay_A_Verma]

A better approach would be apply the filter through the SQL statement when you fetch data from Oracle DB. SQL statement can be written under Advanced Options. 

See whether this is feasible for you or not.