Dataflow to get data from REST API

[webportal]

Hello,

I'm trying to get the data from an API with:

Json.Document(Web.Contents("https://dominio.com/api/index.php/v1/GetData", [Headers=[Authorization="Bearer xxxx", Accept="*/*"]]))

The data format is JSON, and the authentication is via token.

However, this is retrieving an empty table.

Testing the API with Postman, I'm able to obtain the data I need.

Thanks!

[v-yanjiang-msft]

Hi @webportal ,

One possible cause is that the API requires a specific content type header to return the JSON data. You can check the documentation of the API or use Postman to see what content type header is expected by the API. For example, some APIs may require Content-Type: application/json or Content-Type: text/plain. You can then add this header to your Web.Contents function, like this:

Json.Document(Web.Contents(“https://dominio.com/api/index.php/v1/GetData”, [Headers=[Authorization=“Bearer xxxx”, Accept=“/”, Content-Type=“application/json”]]))

Another possible cause is that the API returns a nested JSON object that Power BI cannot automatically flatten into a table. You can check the structure of the JSON data by using Postman or by loading the data as a list in Power BI. For example, if the JSON data looks like this:

{ “data”: [ { “id”: 1, “name”: “Alice” }, { “id”: 2, “name”: “Bob” } ] }

You can then use the Record.Field function to access the data field, like this:

Json.Document(Web.Contents(“https://dominio.com/api/index.php/v1/GetData”, [Headers=[Authorization=“Bearer xxxx”, Accept=“/”]]))[data]

This will return a list of records that you can then convert into a table.

A third possible cause is that the API has some limitations or restrictions on the number of requests, the size of the response, or the time interval between requests. You can check the documentation of the API or use Postman to see if there are any errors or warnings returned by the API. For example, some APIs may have a rate limit that prevents you from making too many requests in a short period of time. You can then adjust your query parameters or use a pagination function to avoid exceeding the limit.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[webportal]

@v-yanjiang-msft 

Thanks, I've tried:

= Json.Document(Web.Contents("https://www.dominio.com/api/GetData", [Headers=[Authorization="Bearer xxx", Accept="*/*", Content-Type="application/json"]]))

Which returns the error:

Expression.SyntaxErrror

[v-yanjiang-msft]

Hi @webportal ,

Could you please provide the complete error message?

Best regards,

Community Support Team_yanjiang

[Anonymous]

@v-yanjiang-msft 

 Thanks, I tried this and it worked:

= Json.Document(Web.Contents("https://www.dominio.com/api/GetData", [Headers=[Authorization="Bearer xxx", Accept="*/*", Content="application/json"]]))

But now I have another situation.

Of the 3 tables I have in Joomla, which I'm fetching via API, one of them worked (according to the image above) and another 2 with different errors:

1) One of them says that the format is invalid

2) And the other one says it gives me error 404:

Any tips on how to resolve this?

[webportal]

@Mahesh0016 

The data source is a REST API that provides the data in JSON format.

The authentication is trhough a Bearer token.

If more info is needed, pls let me know.

Thanks

[Mahesh0016]

@webportal Please Can you elaborate on which data source are using for data load in Power bi. Thank You!!

[Anonymous]

Thanks, I tried this and it worked:

= Json.Document(Web.Contents("https://www.dominio.com/api/GetData", [Headers=[Authorization="Bearer xxx", Accept="*/*", Content="application/json"]]))

But now I have another situation.

Of the 3 tables I have in Joomla, which I'm fetching via API, one of them worked (according to the image above) and another 2 with different errors:

1) One of them says that the format is invalid

2) And the other one says it gives me error 404:

Any tips on how to resolve this?