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

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

2 REPLIES 2
Super User
Super User

Re: How To Use REST API's

Perhaps @ImkeF has run into this.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 330 members 3,421 guests
Please welcome our newest community members: