Power Query Performance Issue

Hi Junaid11, I am not sure I understand your explanation of the problem.  Which of the following is correct:

1. Power Query calls an API to get and process an Excel file, one by one.  (get, open & process x 200)
2. Power Query calls an API to get all Excel files, then opens the file and process one by one (get x 200, open & process X 200)
3. Power Query calls an API to get all Excel files, then opens all the files, and process all of them (get x 200, open x 200, process X 1)

The challenge with Power Query is the order of execution when performing the first can be slow.  Especially the first because it could run the API 400 time not 200 times.  Consider this  Why does Power BI query my data source more than once .

I need to consider strategies to call the API and then process the data once.  Try considering the following:

1. Export the API data to SharePoint Online Library then read from the Library - this separates the external API call delay from Power Query process.  Use Power Automate to call the external API.
2. Use a Dataflow to open the Excel files into single table before processing the transformations
3. Use Incremental Refresh to avoid reloading the same API more that once