Single Csv.Document with online data resulted in 4 requests from Power BI desktop

[kangkc]

I am accessing a Web API data which returns CSV data stream using Csv.Document(Web.Contents(...))

The M Scripts looks similar to the following and it works fine and I am able to get the data into Power BI perfectly. 

However, I realize from the Web API logging that Power BI actually issues 4 requests for every data refresh (no report nor filter created yet).  I simulated the same script under Power Query and it issues 2 requests for the same script. 

It seems to be a Csv.Document issue.

This is causing some problem as there is a limited request quota for the Web API. Any suggestion ?    

let
Source = Csv.Document(Web.Contents("...URL...", [Timeout=#duration(0, 1, 0, 0)]),[Delimiter="|", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{.......})
in
#"Changed Type"

[cleon169]

Did you happen to find a solution to this issue? I'm experiencing the same thing.

Initally I thought it was because the API required the parameters to be sent in a POST request, but functionality was added to the API server to enable the same action in a GET request, and the issue is there. Only happens when the response content-type is text/csv.

[Anonymous]

HI @kangkc,

Your formula seems well, I haven't found any query may caused the issue.

Current web connector not support any redirect feature, maybe you can try to use fiddler to trace the operation and share the detail informations to us.

Regards,

Xiaoxin Sheng

[kangkc]

I did a Fiddler trace, and can confirm that Power BI indeed fire off 4 requests to the Web API address even though it's only for 1 query for single data refresh.

As the same Web API support JSON format, we created another query using JSON.Document(Web.Contents(..)) and based on Fiddler, JSON.Document will only fire 1 request per refresh. 

We can't use JSON format due to some requirement hence switching to use JSON is not possible.

There is definitely something unique (or issue?) about Csv.Document that resulted in 4 requests for 1 query refresh. 

Unfortunately I can't share the Fiddler trace in public due to data but all the 4 requests are exactly the same. 

[Anonymous]

Hi @kangkc,

I'd like to suggest you split them to multiple steps, I guess nested functions may caused with duplicate requests.

Sample:

let
File=Web.Contents("...URL...", [Timeout=#duration(0, 1, 0, 0)]),
Source = Csv.Document(File,[Delimiter="|", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{.......})
in
#"Changed Type"

Regards,

Xiaoxin Sheng

[kangkc]

I have tried that before posting. Doesn't help.

Interesting thing is under Power Query with the exact same query, it will result in 2 requests. Whereas in Power BI, it is 4 requests. Seems like Power BI request is always 4 times.

[Anonymous]

Hi @kangkc,

I guess it may related to the power bi data level, each level will send / receive requests to their parents (so it may be double)

Datasource(database) -> Query Table(power query) -> Data Model

Regards,

Xiaoxin Sheng