How To Use REST API's

[dmutka]

I have a CRM that uses REST APi's that requires OAuth Access Token to authorize the request. When one builds a report inside the CRM it automatically creates an API for that report such as the following:  https://api.somewebsite.com/v1/custom-report/29b55c5c84f64fe1b913dfaf22107715f63dd8e4237a4543b61535d....

The are depricating this API for custom reports requiring me to access each dataset using a REST API. They provide me with a Client ID and client secret and post an example of an API call using C#, but I do not know how to integrate it into Power BI. Below is the sample code they provide for acquiring an Access Token.

    private void InitializeToken()
    {
        //ClientID and ClientSecrect must be obtained from API Settings page in 
        //These keys are used to request a accesstoken which will be used to make authorized api calls. Storing these information safely is highly recommended 
        string ClientID = "YOUR_CLIENT_ID";
        string ClientSecret = "YOUR_CLIENT_SECRET";
        HttpClient client = new HttpClient();
        //add an accept header for JSON format
        client.DefaultRequestHeaders.Accept.Add(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));
        //Create authorization header for client authentication
        string authorizationHeader = Convert.ToBase64String(Encoding.UTF8.GetBytes(ClientID + ":" + ClientSecret));
        client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", authorizationHeader);
        //Use client_credentials granttype with ClientID to get the accesstoken
        Dictionary<string, string> grantClientCredential = new Dictionary<string, string>();
        grantClientCredential.Add("grant_type", "client_credentials");
        grantClientCredential.Add("ClientId", ClientID);
        // Requesting token
        HttpResponseMessage tokenResponse = client.PostAsync("https://api.somewebsite.com/accesstoken", new FormUrlEncodedContent(grantClientCredential)).Result;
        if (tokenResponse.IsSuccessStatusCode)
        {
            //The response contains following information
            //access_token
            //refresh_token
            //expires_in
            //IMPORTANT!!! access_token is valid for the duration indicated by expires_in(in seconds).
            //refresh_token can be used to refresh access token before it expires
            dynamic tokenResposeDe = JsonConvert.DeserializeObject(tokenResponse.Content.ReadAsStringAsync().Result);
            //this accesstoken should be sotred somewhere global so that it can be used to make api calls until it expires.
            accessToken = tokenResposeDe.access_token;
            refreshToken = tokenResposeDe.refresh_token;
        }
        else
        {
            // error will be returned by tokenResponse.Content.ReadAsStringAsync().Result
        }
    }
       

I have also tried using the following Query to retrieve the customers dataset from the CRm with no avail.

let
    authKey ="Basic" & Binary.ToText(Text.ToBinary("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"),0),
    url = "https://api.somewebsite.com/v1/customers",

    GetJson = Web.Contents(url, [Headers=[Authorization="authKey", #"Content-Type"="application/json"]]),
    FormatAsJson = Json.Document(GetJson),

    AccessToken = FormatAsJson[access_token],
    AccessTokenHeader = "Bearer" & AccessToken,
    
    GetJsonQuery = Web.Contents("https://api.somewebsite.com/v1/customers/id/25259",
        [
            Headers = [#"Authorization"=AccessTokenHeader]
        ]
    ),
    FormatAsJsonQuery = Json.Document(GetJsonQuery),
    NavigateToStatuses = FormatAsJsonQuery[statuses],
    TableFromList = Table.FromList(NavigateToStatuses,Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    TableFromList

The first API posted returns a list of records as expected, but the other API's return error messages, such as :
"Expression.Error: The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Referer".

Any help in regards to this matter would be appreciated. I have read a bunch of articles and other questions on Power Bi community but i cannot find a solid answer to this problem. It may be a lack of experience/knowledge. Regardless, if there is a step-by-step walkthrough for how to use REST API's with Power Bi that would be appreciated, but if there is a simple fix or something that I am dong wrong, please advise.

Thanks in advance!

[Greg_Deckler]

Perhaps @ImkeF has run into this.

[ImkeF · Accepted solution]

You have to create a custom connector for using OAUTH2. There are some very good tutorials here: https://github.com/Microsoft/DataConnectors

https://github.com/Microsoft/DataConnectors/blob/master/docs/m-extensions.md#implementing-an-oauth-f...