API Post / Get & make into a http link

[troygraham]

Our company is currently using Goformz and would like to start tapping into the backend using API Post and GET commands in Power BI so we can generate some report data for use in Excel. 

I am very new to Power BI and still getting my head around the coding, so i have no idea how to achieve the following goal.

This is what I need to be able to do

1. First I need to run a POST command

https://api.goformz.com:443/v2/formz/89c9b747-9bd7-4c3d-b5bc-2f3b76758898/exports

2. From the post command results i need to pull part of the location url and add it to a new query

{
"location": "https://api.goformz.com/v2/queue/f4c3349c-35da-4117-a335-a7ac001504bd",
"access-control-expose-headers": "Link, X-Total-Count, Location",
"date": "Mon, 10 Jul 2017 01:16:30 GMT",
"content-length": "0",
"content-type": null
}

3. The new query would then look like this:

https://api.goformz.com:443/v2/formz/89c9b747-9bd7-4c3d-b5bc-2f3b76758898/exports/f4c3349c-35da-4117...

4. The results of the new query would look like this

{ "id": "f4c3349c-35da-4117-a335-a7ac001504bd", "createdDate": "2017-07-10T01:16:31", "contentType": "application/pdf", "link": "https://servicereachstore.blob.core.windows.net/40669a94-c2f5-43f2-bc0b-a278002e1864/filestorage/f4c...", "linkExpiration": "2017-07-10T01:29:46.9105457+00:00" }

5. How can i then place the new link in a cell in excel

https://servicereachstore.blob.core.windows.net/40669a94-c2f5-43f2-bc0b-a278002e1864/filestorage/f4c...

[Phil_Seamark]

Hi @troygraham

I know your datasource is different but if you walk through this blog you might find some handy tips on

• Stripping out the data you need to POST
• converting it to a usful JSON format
• merging the results returned with your dataset.

I think there are enough similarities that it might go some way to help you.

https://community.powerbi.com/t5/Community-Blog/Sentiment-Analysis-in-Power-BI/ba-p/55898

[troygraham]

Hi Phil,

Thanks for your fast reply, but because I am still learning I found this very hard to follow and/or use same methods for my project.

If you could lead me to any simpler examples that may help, would be great.

Is it possible to setup an M Query ? and how would i apply it to my project needs. 

Cheers

Troy

[Phil_Seamark]

Hi @troygraham

While it's probably not Advanced M, it's definitely not simple M either.  You are going to have to mix REST API knowledge with the M texted editor to achieve this one I'm afraid.

[troygraham]

ok i will have to keep digging for the answer.

Another question and I am sure this is an easy one.

Is there a way that i can add a textbox and button in excel to have the ability to change the highlighted text in the JSON Web Query and use the button to apply and refresh the sheet that the query is attached to ?

[troygraham]

Our company is currently using Goformz and would like to start tapping into the backend using API Post and GET commands in Power BI so we can generate some report data for use in Excel. 

I am very new to Power BI and still getting my head around the coding, so i have no idea how to achieve the following goal.

This is what I need to be able to do

1. First I need to run a POST command

https://api.goformz.com:443/v2/formz/89c9b747-9bd7-4c3d-b5bc-2f3b76758898/exports

2. From the post command results i need to pull part of the location url and add it to a new query

{
"location": "https://api.goformz.com/v2/queue/f4c3349c-35da-4117-a335-a7ac001504bd",
"access-control-expose-headers": "Link, X-Total-Count, Location",
"date": "Mon, 10 Jul 2017 01:16:30 GMT",
"content-length": "0",
"content-type": null
}

3. The new query would then look like this:

https://api.goformz.com:443/v2/formz/89c9b747-9bd7-4c3d-b5bc-2f3b76758898/exports/f4c3349c-35da-4117...

4. The results of the new query would look like this

{ "id": "f4c3349c-35da-4117-a335-a7ac001504bd", "createdDate": "2017-07-10T01:16:31", "contentType": "application/pdf", "link": "https://servicereachstore.blob.core.windows.net/40669a94-c2f5-43f2-bc0b-a278002e1864/filestorage/f4c...", "linkExpiration": "2017-07-10T01:29:46.9105457+00:00" }

5. How can i then place the new link in a cell in excel

https://servicereachstore.blob.core.windows.net/40669a94-c2f5-43f2-bc0b-a278002e1864/filestorage/f4c...