cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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

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"

6 REPLIES 6
Frequent Visitor

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.

Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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. 

 

 

 

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

 

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors