Power Query Performance Issue

[Junaid11]

Hello,

I am facing a performance issue in Power Query. I have a function that calls a web API. The result of this call feeds the column of an Excel file. Here is my final query M: let Source = Excel.Workbook(File.Contents("C:Routes.xlsx"), null, true), BIC_Context_Sheet = Source{[Item="BIC_Context",Kind="Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes(BIC_Context_Sheet,{{"Column1", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"BIC_Context", type text}}), #"Removed Top Rows" = Table.Skip(#"Changed Type1",123), #"Invoked Custom Function" = Table.AddColumn(#"Removed Top Rows", "BIC_Context_q", each BIC_Context_q([BIC_Context])) in #"Invoked Custom Function"

The query is working fine But I have great difficulties of slowness on the volumetry. I have about 200 API calls to make. And it takes me several hours Is there a way to improve processing times? Any alternative to this call? This is the one that takes a lot of time #"Invoked Custom Function" = Table.AddColumn(#"Removed Top Rows", "BIC_Context_q", each BIC_Context_q([BIC_Context])) Because I'm calling this API about 200 times

Kindly Help.

Thank you

[Daryl-Lynch-Bzy · Accepted solution]

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