cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HASSANNAZEER Frequent Visitor
Frequent Visitor

Api token

Can anyone help me with connecting this data source using API KEY
http://developer.fulcrumapp.com
10 REPLIES 10
Phoenixke Frequent Visitor
Frequent Visitor

Re: Api token

let
request = Json.Document(Web.Contents("insert_url_here",
[Headers=[#"Accept"="application/json", #"Content-Type"="application/json", #"X-ApiToken"="insert_apikey_here"],
Content=Text.ToBinary("{""insert_body_here""}"),
RelativePath = "insert_any_relative_path_here_or_ommit"]))[insert_any_json_response_you_want_to_capture_or_ommit]
in
request

 

Adding the Content into a blank query forces PBI to do a POST instead of the GET you can do.

Phoenixke Frequent Visitor
Frequent Visitor

Re: Api token

As an example the users.json endpoint with GET:

let
request = Json.Document(Web.Contents("insert_url_here",
[Headers=[#"Accept"="application/json", #"Content-Type"="application/json", #"X-ApiToken"="insert_apikey_here"],
RelativePath = "/api/v2/users.json"]))[user]
in
request

 

This should return you a list of all 'user' records. I'm not sure if you need the ApiToken in this header or if you need the Authorization first and put that in.

HASSANNAZEER Frequent Visitor
Frequent Visitor

Re: Api token

This works fine. But all are labeled as Request

possible to use jquery ?

cURL

curl --request GET 'https://api.fulcrumapp.com/api/v2/records.json?form_id=my-form-id' \
--header 'Accept: application/json' \
--header 'X-ApiToken: my-api-key'

jQuery

$.ajax({
  type: "GET",
  url: "https://api.fulcrumapp.com/api/v2/records.json",
  data: {
    "form_id": "my-form-id"
  },
  contentType: "application/json",
  dataType: "json",
  headers: {
    "X-ApiToken": "my-api-key"
  },
  success: function (data) {
    // do something!
    console.log(data);
  }
});
HASSANNAZEER Frequent Visitor
Frequent Visitor

Re: Api token

When I Try Query API, It does not work. 

Formatting Calls to the Query API

All Query API requests should follow this general pattern:

https://api.fulcrumapp.com/api/v2/query?token={API Token}&q={SQL Statement}

If you encounter errors, double-check that you are using the correct API token, and that your SQL statement is valid. A common use case example is to get a count of the records in a form, grouped by status:

Example

https://api.fulcrumapp.com/api/v2/query/?token=abc-123&q=SELECT _status, COUNT(*) FROM "Park Inventory" GROUP BY _status ORDER BY COUNT(*) DESC

Result

_status,count
City,19
Private,5
Local,3
County,3
State,1
Moderator v-yuezhe-msft
Moderator

Re: Api token

@HASSANNAZEER,

What error message do you get in Power BI Desktop?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HASSANNAZEER Frequent Visitor
Frequent Visitor

Re: Api token

let
photos = let
request = Json.Document(Web.Contents("https://api.fulcrumapp.com",
[Headers=[#"Accept"="application/json", #"Content-Type"="application/json", #"X-ApiToken"="5db3ef309dbec743c34e71b1119fedff394a54473f0203a7894e203914e53f427c33991045777b77"],
RelativePath = "/api/v2/photos.json"])),
photos = request[photos],
#"Converted to Table" = Table.FromList(photos, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
photos,
#"Converted to Table" = Table.FromList(photos, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"access_key", "created_at", "updated_at", "uploaded", "stored", "processed", "deleted_at", "record_id", "form_id", "file_size", "content_type", "latitude", "longitude", "url"}, {"access_key", "created_at", "updated_at", "uploaded", "stored", "processed", "deleted_at", "record_id", "form_id", "file_size", "content_type", "latitude", "longitude", "url"})
in
#"Expanded Column1"

 

 

 

 

the Above Code is working But Its Difficult to link Tables. 
How Can I Expand All Columns ?

How Can I Rename Column Names based on another Table ?

 

Can I Use the Query API Method ? How to do that ?

 

http://developer.fulcrumapp.com/query-api/reference/

Phoenixke Frequent Visitor
Frequent Visitor

Re: Api token

let
    photos = Table.ExpandRecordColumn(
                Table.FromList(
                    Json.Document(Web.Contents("https://api.fulcrumapp.com",
                    [Headers=[#"Accept"="application/json", 
                              #"Content-Type"="application/json", 
                              #"X-ApiToken"="5db3ef309dbec743c34e71b1119fedff394a54473f0203a7894e203914e53f427c33991045777b77"],
                    RelativePath = "/api/v2/photos.json"]))[photos], 
                Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
            "Column1", {"access_key", "created_at", "updated_at", "uploaded", "stored", "processed", "deleted_at", "record_id", "form_id", "file_size", "content_type", "latitude", "longitude", "url"}, {"access_key", "created_at", "updated_at", "uploaded", "stored", "processed", "deleted_at", "record_id", "form_id", "file_size", "content_type", "latitude", "longitude", "url"})
in
    photos

I've slightly 'refactored' your query, the result should be the same.

The linking should be handled via 'Manage Relationships'. There was an update to the renaming (https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-july-feature-summary-2/#rename) but I don't think you'll be able to use something like a switch statement for renaming the colums.

HASSANNAZEER Frequent Visitor
Frequent Visitor

Re: Api token

Can I Use the Query API Method ? How to do that ?

 

http://developer.fulcrumapp.com/query-api/reference/

Phoenixke Frequent Visitor
Frequent Visitor

Re: Api token

If the fulcrumapp's API supports it, you should be able to use it.

In PBI you would simply paste the entire url into a Web request and if the "?q=" part is correct, it should simply pull in the data.

 

I'd start with something simple like ?q=SELECT * FROM "table_name"

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors