Refresh failing in service, works in desktop

[jkuntz]

I have a new report that I cannot get the semantic model to refresh on the Power BI Service, but it refreshes just fine on Power BI Desktop. I have an existing report that uses 1 of the same 3 data sources, and refreshes fine on the PBI Service. The error(s) make no sense, it says "Please make sure your data gateway (personal mode) is installed and configured properly". My sources are all external websites, nothing "internal" that uses a gateway.

1. ClickUp API 

2. Harvest API 

3. Bank of Canada website 

The existing scheduled refresh semantic model calls the Harvest API and works just fine, no errors. If I go to Manage Connections and Gateways, I have them all set to Anonymous, Privacy set to Public (it was Organizational, and received the same errors). I have it set to skip test connection since ClickUp and Harvest both need my provided authentication keys.

Data Source Credentials under Settings for the semantic model is greyed out, I cannot get in to set/check credentials.

In Power BI Desktop, I can refresh it just fine, no errors. I can't quite figure out how to get past this or find what the real error is (since it can't actually be a gateway error). Any ideas would be appreciated.

[jkuntz]

Thank you! That is totally what the issue is, I'd completely forgotten about running into the same on the first report several years ago. The 2nd API I might have built the M query differently as it paginates differently, so I will start there. This time I will document it better and write it up on my blog since I expect I will continue the forget > relearn cycle a few more times!

[Jonvoge · Accepted solution]

Hi jkuntz

In my experience, APIs in Power Query can be super weird. I've had many cases of queries which work fine in Power Query or Dataflows, but once published fails during the refresh.

What I've found is that the most common culprit are POST requests and requests with dynamic urls in general (as in, if you use a function to handle pagination for example). Is that the case with any of your APIs that fail?

Can you perhaps upload the M-code here for dislay here?

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data