CODEWITHSUNDEEP
powerbietlpowerquerybusiness-intelligencerest
rchacko
rchacko

Reputation: 2129

Any better way in Power BI to make REST API calls like HTTP GET to read data using a dynamic Access Token (Key)

Trying to generate few Power BI reports using REST API calls. This will require to generate an "Access Token" initially and then pass that Key to various API calls for authentication purposes, to read data.

I saw many threads that is using "Access Token" (Key) as a URL query-string or as HTTP header in power BI Web data source. But trying to achieve the Access Token created dynamically and then pass as headers in GET calls.

At the moment I am trying to read data from Adobe sign and they exposing data through REST API calls and authorized users can use HTTP GET to read data from their portal. There is a facility to create organisation-specific application and Access Tokens using their API's.

So please share if if you know a way to generate an Access Token dynamically during each dateset-refresh using POST and then call GET to fetch data needed for reports.

The below images are proposed API calls and tested through Postman

enter image description here

enter image description here

Upvotes: 1

Views: 6349

Answers (1)

Krzysztof Sztompka
Krzysztof Sztompka

Reputation: 7204

I have achieved something like this by creating powerbi custom connector. Which allows you to easily authenticate to any standard REST API (or any other sources). Of course, you can also do some work to authenticate to some more custom apis (I had this problem). But adobe sign api has quite good oauth2 implementation. Custom connector is probably the best recommended solution. You can do very powerful things (I did). This is the way to do production-ready solutions.

How to create a custom connector: https://github.com/Microsoft/DataConnectors/blob/master/docs/m-extensions.md#overview

  1. Install the Power Query SDK from the Visual Studio Marketplace
  2. Create a new Data Connector project
  3. Define your connector logic
  4. Build the project to produce an extension file
  5. Create a [My Documents]\Power BI Desktop\Custom Connectors directory
  6. Copy the extension file into this directory
  7. Enable the Custom data connectors preview feature in Power BI Desktop (under File | Options and settings | Custom data connectors)
  8. Restart Power BI Desktop

When you have some basic connector you can implement OAuth authentication https://learn.microsoft.com/en-us/power-query/handlingauthentication and here you have almost copy&paste solution https://learn.microsoft.com/en-us/power-query/samples/github/readme (From above resource)

GithubSample = [
    Authentication = [
        OAuth = [
            StartLogin = StartLogin,
            FinishLogin = FinishLogin
        ]
    ],
    Label = Extension.LoadString("DataSourceLabel")
];
StartLogin = (resourceUrl, state, display) =>
        let
            AuthorizeUrl = "https://Github.com/login/oauth/authorize?" & Uri.BuildQueryString([
                client_id = client_id,
                scope = "user, repo",
                state = state,
                redirect_uri = redirect_uri])
        in
            [
                LoginUri = AuthorizeUrl,
                CallbackUri = redirect_uri,
                WindowHeight = windowHeight,
                WindowWidth = windowWidth,
                Context = null
            ];
FinishLogin = (context, callbackUri, state) =>
    let
        Parts = Uri.Parts(callbackUri)[Query]
    in
        TokenMethod(Parts[code]);
TokenMethod = (code) =>
    let
        Response = Web.Contents("https://Github.com/login/oauth/access_token", [
            Content = Text.ToBinary(Uri.BuildQueryString([
                client_id = client_id,
                client_secret = client_secret,
                code = code,
                redirect_uri = redirect_uri])),
            Headers=[#"Content-type" = "application/x-www-form-urlencoded",#"Accept" = "application/json"]]),
        Parts = Json.Document(Response)
    in
        Parts;

Then when you first time run your connector it will ask user to authenticate and after it will store token.

Later on when you run your connector all requests to your API done by Web.Contents method https://learn.microsoft.com/en-us/powerquery-m/web-contents will be authenticated.

Upvotes: 2

Related Questions