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

How To Use REST API's

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: How To Use REST API's

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...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: How To Use REST API's

Perhaps @ImkeF has run into this.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User III
Super User III

Re: How To Use REST API's

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...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors