Refreshing API from Visual Crossing in Power BI Service automatically

[TJohnsonOK]

Hi all

I am somewhat a novice with programming, so programming is not my strong suit.  I just wanted to say that in advance. 

I have uploaded through Power Query forecast data for several cities through Visual Crossing using the API with CSV--I followed the instructions and used the web with an anonymous login and no credentials checked.  This worked fine.  Once I combined the queries into one so that they would all be in the same table and published the file back to Power Bi--this works as well.  However, when I try to manually refresh ( my goal is to have this refresh one time a day in Power Bi) it fails--this is the error.

Data source error: {"error": {"code": "SSAS_ShortMessage_ProcessingError","pbi.error": {"code": "SSAS_ShortMessage_ProcessingError","parameters": {},"details": [],"exceptionCulprit": 1}}} Table: Gas Forecast Cities.
Cluster URI: WABI-US-CENTRAL-B-PRIMARY-redirect.analysis.windows.net
Activity ID: 577bc6cd-e26b-45f5-b6c6-b3683c62f7e6
Request ID: 3f39c562-2c04-eaa4-3faa-e5ef74338dc4
Time: 2023-09-16 19:01:58Z

In Power BI I created a gateway connection for each city that the forecast was for.

The table Gas Forecast Cities is the query that I used to combine all the cities in the same table.  I have been working for several days on this--and tried to find a forum that could help--

Does anyone have any ideas on how I can get this to work?  

Thank you

[TJohnsonOK · Accepted solution]

For those who are interested in something like this, I was FINALLY able to get it to work.  I was able to bulk download all the cities in one file, so I didn't have to do an append query.  Then in my power bi gateway--I didn't connect to the web url, but in the cloud connections, I created a connection where I connected as follows.

Hopefully this helps someone in the future.