Power BI is making mutliple calls to the API after converting to a table and expanding records

[Tman915]

I have an API that I am querying using the web source. In my API code, I put some debugging information to see how many times it was being called. If I only get the source data (Get Data -> Web) and run that, the API is only being called once and the results show. If I convert the results into a table (by pressing "To Table") and I refresh the data I can see the API is being called twice. I can take it a step further and expand the records in the table and the API gets called three times. At this point, this causes errors and the query fails to load. Is there any way to stop Power BI from querying it multiple times when I do a transform?

let
    Source = Json.Document(Web.Contents("MyURL")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"NodeName", "PropertyName", "Identifier", "MetaData", "data"}, {"Column1.NodeName", "Column1.PropertyName", "Column1.Identifier", "Column1.MetaData", "Column1.data"})
in
    #"Expanded Column1"

[v-yangliu-msft]

Hi @Tman915  ,

This is the principle of power bi, because if you expand the records in the table, the operations inside will be called once automatically

You can check the related content of the document, I hope it will be helpful to you:

https://stackoverflow.com/questions/61238537/odd-web-contents-behavior-making-multiple-calls-to-api-...

https://blog.crossjoin.co.uk/2019/10/13/why-does-power-bi-query-my-data-source-more-than-once/

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tman915]

Thank you for your help.

[Mornagli]

Hi @v-yangliu-msft , I have the sme problem, My DB server not handle in all the API calls, is there a solution or somthing that i can do? I work with ODATA feed, and after i expand it does not load....

[Tman915]

The solution that worked for me was to have one query that called the API and then create a reference of that query by right clicking it and clicking create reference and performing the transformation on the referenced query. Also, in Power BI, go to Options -> Data Load and uncheck Enable parallel loading of tables.