Api token

[HASSANNAZEER]

Can anyone help me with connecting this data source using API KEY
http://developer.fulcrumapp.com

[Candynator]

Hi,

I just wonder how did you go with this last year?  as I am trying to get data from fulcrum into a power BI to create live reports using API key.

Thanks

[Phoenixke]

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]

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]

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

[v-yuezhe-msft]

@HASSANNAZEER,

What error message do you get in Power BI Desktop?

Regards,
Lydia

[HASSANNAZEER]

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]

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]

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

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

[Phoenixke]

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"

[HASSANNAZEER]

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);
  }
});