cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
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
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 Super User!




Highlighted
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
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors