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

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
Greg_Deckler
Super User
Super User

Perhaps @ImkeF has run into this.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors