Exchanging an OAuth Token in Power BI Custom Data Connectors

Custom Data Connectors can support multiple kinds of authentication. For most modern web apps, OAuth is the obvious choice and the Power Query SDK, coupled with the authentication examples (here and here), make it easy to setup.

Unfortunately, two apps that I have recently created Custom Data Connectors for were partway through an OAuth implementation. While the circumstances were unique to each application, I had to authenticate the user with the OAuth Identity Provider (IdP) and then exchange the access_token for one that the application could use. This isn’t an unusual OAuth “flow”, particularly when the application uses a custom payload in the access_token JWT for application functions.

Whether you agree with an exchange flow or not, I had to figure out how to make the Custom Data Connector work within these constraints.

To accomplish this, I had to interrupt the normal OAuth response flow and exchange that IdP provided access_token for one that the application would accept as through Bearer authorization. I also needed to ensure that the refresh_token flow would continue to work when Power BI encountered a HTTP 401 (unauthorized) response from and API, causing it to perform a refresh grant to retrieve a new access_token and keep data flowing without the user being interrupted.

It just wasn’t clear to me exactly where to do all of this work.

In other OAuth frameworks or SDK’s, it’s usually pretty easy to see where you’d drop into the flow and make the exchange before moving ahead. Given the Github authentication example, my mind wasn’t thinking functionally at the time (it’s still a challenge for me!). It was unclear that the response from the FinishLogin function was being consumed and stored by Power BI directly. Once I made that connection it was easy to accomplish what I needed.

The Basic OAuth Setup

I started with the examples from the articles I’ve linked to above, enabling OAuth and setting up each of the functions.

...    
Authentication = [
        OAuth = [
            StartLogin=StartLogin,
            FinishLogin=FinishLogin,
            Refresh=Refresh,
            Logout=Logout
        ]
...

Inside of the FinishLogin function there is a call to the TokenMethod which takes the code grant and exchanges it with the IdP for an access_token.

TokenMethod = (code) =>
    let
        Response = Web.Contents("https://auth.example.com/oauth2/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"]]),
        LoginResult = Json.Document(Response)
    in
        LoginResult ;

It took me a while to realize that the Json.Document(Response) was simply parsing the OAuth payload (id_token, access_token, and refresh_token) into a Power Query record that the connector/runtime was storing and using later. Therefore, I had to do my exchange somewhere in this function before returning the Record that Power BI needed.

Exchanging the Access Token

In order to exchange the trusted access_token, another function was needed to call the actual Exchange endpoint of each application. YMMV, but here’s what a basic function might look like.

TokenExchange = (id_token as text) =>
    let
         NewToken = Web.Contents("https://auth.example.com/api/exchangeToken",[
            Content = Text.ToBinary("{id_token: '" & id_token & "'}"),
            Headers = [#"Content-Type"="application/json"]]),
         LoginResult = Json.Document(NewToken),
         AccessToken = LoginResult[token]
    in
        AccessToken

With that in place, I could modify the TokenMethod to do the exchange mid-stream for me like so.

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"]]),
        body = Json.Document(Response),
        result = if (Record.HasFields(body, {"error", "error_description"})) then 
                    error Error.Record(body[error], body[error_description], body)
                 else
                    // This is a code exchange, we expect a refresh_token
                    if (Record.HasFields(body, {"refresh_token"})) then 
                    [
                        refresh_token=body[refresh_token],
                        access_token = ExchangeToken(body[id_token])
                    ]
                    // This token was obtained by a Refresh request. No refresh_token             
                  else
                    [
                        access_token = ExchangeToken(body[id_token])
                    ]
    in
        result

The key was understanding that the result of the TokenMethod, which is what the FinishLogin method will ultimately respond with, needs to provide a Record with all of the fields necessary to continue the OAuth flow. In this case, simply replacing the access_token and refresh_token allowed Power BI to store this for later use.

Whenever the token expires, producing a HTTP 401 (Unauthorized) response, the RefreshToken logic kicks in, goes back through this same logic, and data keeps flowing.

Conclusion

Learning how to use Power Query to get data from REST APIs has been a fun (if not frustrating at times) learning experience. The documentation continues to improve and the Power Query team and community are very helpful. Each new challenge helps me better understand the building blocks they have provided and how to get to the next milestone.

Now, if I could just have something like a “finally” query that gets executed after an import to create the relationships I need and additional linking tables if needed. Wow, I’d be in heaven! 😉

Organizing Functions in a Power BI Custom Data Connector

The more complex a Power BI Custom Data Connector gets, the more cumbersome the main Power Query file becomes. Without the ability to make a true “project” structure and include or directly reference functions from other files at this point, I wanted to find a way to keep things a little more organized.

In the series of tutorials that the Custom Data Connector team put together, there’s an example of how to include external functions at the end of Tutorial 7. Although it’s not mentioned in this short explanation, I quickly realized that a Power Query project references all files by inclusion in the final build, not the physical path structure of the project or through namespaces. As long as the file is set to be compiled into the final solution, you can reference it by name regardless of where it is actually placed in the file structure of the project itself.

Therefore, to keep my Custom Data Connector projects a little more visually organized (and to help my future self remember how functions relate), I’ve gotten into the habit of organizing my projects using folders that group common functions together.

I’m still tweaking what I call my “standard” set of folders, but between projects the concepts are the same.

The Root

I try to only keep my Resource and Power Query files in the root of the project, making it easy to find as the number of functions grows.

Icons

Next, I always move the icons into a separate folder. In the generic Power Query project template, you end up with 8 icon files taking up space and named after your main PQ file. Clutter.

Old Snippets

This folder isn’t always named consistently, but I keep a folder for functions and snippets of code I don’t need anymore, usually because I found a better way to do something. I’ve found that it can be helpful for me to refer to old examples of how the project evolved in a way that looking at the commit history can’t solve. When snippets are in a file, it’s easy to include it as a function and see it work again.

Helper/Core

I’m still trying to figure out what I really want this folder to be called, but it contains the meat of my application function logic. Inside of this folder I make sub-folders that group common functions together.

  • Table – These are usually the base helper functions provided by the team at Microsoft with some custom modifications.
  • Data Transformations – The heart of a data connector is about transforming data into something usable for… the user. These are separate functions that generally translate to each Query in Power BI.
  • Authentication – Most data sources require some sort of Authentication. Most of these functions are based on authentication examples from the Microsoft team. I’ll tweak them as needed for the specific project.

Making it work

Whatever your specific folder structure becomes, how does this work out in practice?

Simple! Any file that you want to reference in connector code has to be set as Compile in the Build Actions. Once that is set, the file can be referenced by name only in a PQ function. If you ever forget to set a file to be included in the compiled Data Connector, you’ll quickly get a warning.

For files like the Icons, you don’t need to do anything special after setting the build action. As long as the name stays the same, the references in you Connector.Icon method will just keep working.

However, if you want to load one of the other functions, you’ll need to use the temporary workaround as described in Tutorial 7.

// 
// Load common library functions
// 
// TEMPORARY WORKAROUND until we're able to reference other M modules
Extension.LoadFunction = (name as text) =>
    let
        binary = Extension.Contents(name),
        asText = Text.FromBinary(binary)
    in
        Expression.Evaluate(asText, #shared);

With that function available, you can now load other functions with one or two lines of code. If the method requires no inputs, just initiate it to a function variable.

Table.ChangeType = Extension.LoadFunction("Table.ChangeType.pqm");

In some cases, however, your function might need access to another external function or parameter which has to be passed by reference because the scope has changed. In that case, you need to load the function first and then make it available through a second function variable that accepts the external references and any additional parameters.

TokenMethodFunction = Extension.LoadFunction("Auth.TokenMethod.pqm");
TokenMethod = (grantType, tokenField, code) => TokenMethodFunction(grantType,tokenField,code,OAuthConfig,ExchangeToken);

With this workflow, my overall Power Query file is much less cluttered and it helps bring more meaning to my source control commits when functions are broken down into smaller chunks.

The One Downside

I’d be remiss if didn’t mention the one, hopefully obvious, issue with moving files outside of the main Power Query file into .PQM files.

The lack of syntax highlighting and intellisense.

It is annoying. I’ve learned to deal with it for small changes. If I need to work through a problem, I’ll copy the function over until it’s been modified and is working, and then wire it back up through the “LoadFunction”. In reality, once a function is working, it’s rare that I’ll actively modify it.