what's the best way to load data using power query

The short answer is Parquet. The long answer is rather surprising. 

• https://www.sqlgene.com/2024/11/28/benchmarking-power-bi-import-speed-for-local-data-sources/

Hi Jeanxyz,

Thank you for reaching out in Microsoft Community Forum.

Please follow below steps to Optimize Power BI Refresh:

1.Break the 700k-row table into monthly or yearly partitions and load only what’s needed using filters or relationships.

2.Set up RangeStart and RangeEnd parameters in Power Query and define date-based filtering to only refresh new data.

3.Remove unused columns, reduce decimal precision, and simplify datetime fields to basic formats to shrink load time.

4.Load preprocessed data into a lightweight database like SQL Server Express or SQLite, then import or DirectQuery from Power BI for faster reads.

Please continue using Microsoft Community Forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

Reading a 700,000-row local .csv file takes 15 minutes, which means you should check your queries (if you just upload to the model).

Based on past experience, the following operations will seriously slow down the operation of M query:

1. Sorting (it is not very time-consuming unless there are too many sorts).

2. Merging queries and aggregating (in some specific cases, it will read the file as many times as the number of groups. For example, if there are 100 groups, the file will be read 100 times).

3. Frequent indexing (or looking up) of values ​​in the table (for example, in the newly added column, sum all rows in the original table that are greater than the current row. In this case, if there are 100 rows, the file will be read 100 times).

I conducted a series of tests and found that the problem was that the calculation of the calculated column was too complicated. You can try to use the following formula to replace the calculated column in the fact_stocks table. It will take about 30 seconds.

2-day range = 
VAR tFilterStock = FILTER('fact_stocks', fact_stocks[Stock]= earlier(fact_stocks[Stock]))
var c_1d=maxx(filter(tFilterStock,fact_stocks[Date]<earlier(fact_stocks[Date]) ),fact_stocks[Date])
var max_=maxx(filter(tFilterStock,fact_stocks[Date]<=earlier(fact_stocks[Date]) && fact_stocks[Date]>=c_1d ),fact_stocks[High])
var low_=minx(filter(tFilterStock,fact_stocks[Date]<=earlier(fact_stocks[Date])  && fact_stocks[Date]>=c_1d ),fact_stocks[Low])
var close_=maxx(filter(tFilterStock,fact_stocks[Date]=earlier(fact_stocks[Date]) ),fact_stocks[Close])
return
divide((max_ - low_),close_)

I just filter the corresponding [Stock] first and filter it afterwards. This will greatly reduce the number of iterations.

I did not check the results, you should double check if the results are correct.