Web connector in powerapp dataflow returns error in refresh, but works well in design

That's tricky. I have not tested this outside of pbi service but may be worth a shot. It may not be possible with your data source, but if you specify the same query parameters in the first argument of Web.Contents as the query parameters you specify in the Query record of the optional record second argument, then you can trick the test connection that the service runs.

Also described in a Chris Webb post: https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

Relevant excerpt:

This technique will only work if the url passed to the first parameter of Web.Contents() is valid in itself, is accessible and does not return an error. But what if it isn’t? Luckily there’s another trick you can play: when you specify the Query option it can override parts of the url supplied in the first parameter. For example, take the following expression:

 

Web.Contents(
 "https://data.gov.uk/api/3/action/package_search?q=apples",
 [Query=[q="oranges"]]
)

 

When static analysis is carried out before dataset refresh, the url

 

https://data.gov.uk/api/3/action/package_search?q=apples

 

..is evaluated. However when the dataset is actually refreshed, the search term in the Query option overrides the search term in the base url, so that the call to the web service that is actually made and whose data is used by the query is:

 

https://data.gov.uk/api/3/action/package_search?q=oranges

 

This means you can specify a base url that isn’t really just a base url just so that static analysis succeeds, and then use the Query option to construct the url you really want to use.