Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
You have to create a custom connector for using OAUTH2. There are some very good tutorials here: https://github.com/Microsoft/DataConnectors
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
Perhaps @ImkeF has run into this.
You have to create a custom connector for using OAUTH2. There are some very good tutorials here: https://github.com/Microsoft/DataConnectors
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |