REST API with MS Power BI

Tried pasting file info but had to attach . . . Mike(C)

Hi Mike,

I can successfully pull the data from Power BI desktop > get data from web > invoke a GET call with Basic authentication, see the attached screenshots.

If you are trying to invoke a POST call, as suggested in below two posts:

https://community.fabric.microsoft.com/t5/Desktop/Pull-data-from-API-using-POST-method-in-Power-BI/m-p/574727

https://community.fabric.microsoft.com/t5/Desktop/Connect-to-a-Web-Service-sending-parameters/td-p/225510

You can try to set the request body as "Content" in request header:
= let
   body = "The POST method body here",
   Data= Web.Contents("https://yourusrlhere",[Content=Text.ToBinary(body),Headers=[#"Content- 
   Type"="application/json"]]),
   DataRecord = Json.Document(Data),
   Source=DataRecord 
in
   Source

Let me know how this works for you.

Duo

Hi Duo, I'm trying to replicate the above examples using FedRAMP Production and cannot get past the first two steps. The third step, expanding the results, is grayed out. Since you got it to work, can you please share your query?

Hi David,

I did a quick test by setting the web source URL as https://signer-gov.esignlive.com/api/packages?from=1&to=100 (note that there's a "signer-" upfront the browser URL) and the base64 decoded API Key as the username and password, see attachment. And this returns the data source.

Duo

I was able to get the Source step to work. It produces 2 fields: results and count. results=List and count=381. This is as expected. I then convert to table, but after that, the Expand button (on the Transform tab) is greyed out. I found a workaround, Convert the first value to Table, which produces a list with 100 items. I then convert the list to table, which creates a table with 100 rows. At this point the Expand button becomes available. This gives me the columns. However, I have 381 packages. I tried changing the original query argument to "from=1&to=110" and I still only get 100 rows. I need all 381 packages.

Update: I was using Power Query in Excel. When I tried it in Power Query for Power BI, it worked much better but it is still only giving me 100 rows.

I see. Yes, this is an OSS API limitation that the maximum returned item is 100. So 381 packages require 4 API calls:

/api/packages?from=1&to=100

/api/packages?from=101&to=200

/api/packages?from=201&to=300

/api/packages?from=301&to=400

And this technical blog seems to provide an example where it looped through different data sources:
https://pbi-guy.com/2022/07/05/how-to-loop-through-an-api-with-power-bi-without-knowing-last-page/

Duo

OK, thanks. So, another issue: 

With this query: = Json.Document(Web.Contents("https://signer-gov.esignlive.com/api/packages?predefined=sent&from=1&to=100"))

I'm not getting just the sent ones.

Hi David,

Try with the parameter "?query=SENT" instead of predefined.

Duo

Thank you! That worked!