PowerBI refresh data from AWS API Gateway Rest API

[Anonymous]

On my local machine, I have connected a PowerBI report to a set of REST APIs that are from AWS API Gateway that require an api-key in the header. The report loads and can refresh the data successfully when locally. The code for connecting looks like:

Source = Json.Document(Web.Contents("https://" & #"API URL",
[RelativePath = "prod/productionData", Headers=[#"x-api-key"=#"apiKey"],
Query = [#"site" = #"SiteID", #"timerange" = #"LongTimeRange"]]))

However, when I upload the file onto my company's PowerBI workspace, it is unable to refresh because "Failed to update data source credentials: The credentials provided for the Web source are invalid. "

The API can only be accessed when the apiKey is passed through the header. How can I get PowerBI to play nicely with this setup from the AWS side?

[TeigeGao]

Hi @Anonymous ,

Based on my searching, some API cannot work well with web.content in PowerBI Service, for more information, please refer to this idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/10927416-web-contents-should-support-scheduled-refresh-ev.

As a workaround, we can use the Python script to get the data from the API, then convert it to dataframe data type, PowerBI can recognize the data type.

Best Regards,

Teige