No Built-In Power Query Connector: Am I Stuck?

, ,

Uh oh! You’re using Microsoft Power BI or Excel and you’ve discovered that Power Query does not have a built-in connector for the data source you’re interested in consuming.

Question: What does this mean for you?

Answer:
(pick which of the below you think is most likely correct)

  • No go. You cannot access this data source from Power Query.
  • Custom connector. “Built-in connector” and “custom connector” sound like opposites. Either you may need to fork out a bit of $$ to get a custom connector created, or give up.
  • Something else. Hmm…what are the other options?

I’d suggest starting with “something else.” When Power Query doesn’t ship with a connector for your source of interest, there are a number of other options to consider.

Let’s take a journey through the main other options for directly connecting to a source from Power Query. We’ll order these into “stages” based on an approximation of the level of effort involved. Then, we’ll expand our scope and touch on several possibilities that involve using external tools or languages to provide the needed data to Power Query.

But first, the problem scenario….

Uh oh! Not Directly Supported

Say you’re trying to pull data from “Ben’s Amazing Accounting Software” (a fictitious name, but one with a nice ring to it—but maybe I’m biased).

You check under Get Data in Power BI Desktop/Excel (or under New Source, if you’re in Query Editor) and don’t find “Ben’s Amazing Accounting” listed. Alternately, perhaps you check the master list on the web and it doesn’t show “Ben’s Amazing Accounting” as supported by the Power Query edition you are using. In either case, the net effect is the same: You don’t have a fully supported Power Query connector for the source you want to use.

What do you do now?

Stage 1

Underlying Data Storage Technology

What is the underlying data storage technology used by the data source of interest?

Often, a proprietary application uses a widespread technology for its data storage. Power Query may not have a connector branded for the particular application but may have one for its underlying storage technology.

Take our “Ben’s Amazing Accounting Software” example. Say it stores its data in a Microsoft SQL Server database. While Power Query does not have a connector specifically for this particular bookkeeping program, it does have one for Microsoft SQL Server. So long as “Ben’s Amazing Accounting Software” is amazing enough to allow you to directly connect to its database, then you should be able to use the built-in SQL database connector to access its data.

So, if a built-in connector doesn’t exist for your source of interest, try to identify the source’s underlying data storage technology and (assuming you are allowed to directly connect to the data store) check whether Power Query has a connector for it. If so, give it a try!

OData Endpoint

Another option when no built-in connector exists: Check whether the source of interest (or its storage technology) exposes an OData API.

OData is an Application Programming Interface (API) standard that defines a common way to perform actions, such as fetching data, as well as to retrieve information about the schema of available data. Power Query ships with an OData connector.

If an OData API is offered by the application of interest, then connecting to the application may be as easy as pasting the API endpoint’s URL into Power Query’s OData connector’s dialog, configuring the connection (like the username and password), then using the connector’s interface to select the data entities of interest.

Stage 2

ODBC/OLE DB Driver

If accessing the underlying data storage technology isn’t an option and no OData endpoint is available, then try this question: Does the application of interest, or its underlying data storage technology, provide a Windows ODBC or OLE DB driver?

ODBC and OLE DB are data interface standards. Many data sources support external access to their data using one or both of these technologies. Power Query supports reading data via both (excepting OLE DB from dataflows, but that may change soon). If an ODBC or OLE DB driver for Windows is available for the source of interest, try having Power Query access data through it.

The initial level of effort involved is likely more than the stage 1 options: you’ll need to find, download and locally install the appropriate ODBC or OLE DB driver, then figure out the correct connection configuration to use (e.g. the right connection string; or, alternately, just for ODBC: how to set up an appropriate DSN).

The install process will need to be repeated on each system that will use Power Query to access the source. If you send your report to a coworker and they want to refresh it (or edit its M code), they’ll first need to do the same driver install and configuration that you did. Also, if you want to refresh in the Power BI Service, you’ll need to use an on-premise data gateway and repeat the driver setup there.

ODBC and OLE DB open up the opportunity to connect Power Query to a plethora of sources. The cost is some additional setup effort and possibly an on-premise data gateway, but the benefit gained is a world of opportunity.

Stage 3

Hand-Crafted API Access

Sometimes, none of the above options are viable. If so, our quest for a connection solution leads us to another question: Does the application of interest (or its underlying data store) expose an HTTP/HTTPS API?

This is a broader than the OData API question from earlier. Here, what we’re looking for is any kind of API that is accessible via HTTP or HTTPS. (This API could be available on the public Internet or just inside your local network.)

If it does, you could try writing M code that calls the API and fetches data from it.

For a simple scenario, you may be able to craft a simple API call that fetches the appropriate data (maybe something like https://example.com/getUsers?fields=id,firstname,lastname), then use trivial M code based around Web.Contents to call the API, then convert the response received (perhaps returned as JSON) into the appropriate Power Query data type (like a table).

let
  Url = "https://example.com/getUsers?fields=id,firstname,lastname", 
  Source = Web.Contents(Url ),
  AsJson = Json.Document(Source),
  AsTable = Table.FromRecords(AsJson)
in
  AsTable

The above makes consuming a web API from Power Query look simple—and sometimes it is. But not always.

Sometimes the API returns paged responses, so you need to wire up logic enumerating through the pages, stitching them together into a single table. Sometime the response format is complex, necessitating significant M code to parse and transform it. Sometimes the authentication method used by the API isn’t supported by Power Query; or, maybe it’s supported but only if you package your M code into a custom connector. Sometimes, the fact that Power Query may make multiple calls to a data source bites, necessitating complex M code. And on the list goes.

Directly interacting with an API from Power Query can range from simple to complex. You might want to start by searching online to see what you can learn from others who’ve tried connecting to the API from Power Query. Unless that research reveals blockers, it may then be worth giving a go at it for a few hours to see how far you can get. Maybe in that span of time you’ll come up with a satisfactory working solution or decide that, while more effort is required, consuming the API is doable and a worthwhile investment. On the other hand, you may discover that pulling this off is way too complex given the API’s specifics and your M coding skill level.

In summary, if the source of interest (or its underlying data storage technology) has a web API, then—technically speaking—most likely you can connect to it from Power Query, so long as Power Query supports the appropriate authentication method. However, you’ll need to evaluate how easy or hard this is to do, and whether that quantity of effort is justified.

Other Options

In addition to the above, if you’re open to bringing other tools or languages into the mix, you may have some other options, including:

External Data Staging

Using some external-to-Power Query tool or feature, is there a way you can get data out of the source into something Power Query can easily read?

Maybe you have an ETL tool that supports reading from the source, which can dump what it fetches into a SQL database that Power Query can then pull from. Maybe the source application itself allows you to schedule CSV exports, which you can have it dump to a location where Power Query knows to look for them.

Somehow or another, with this approach, you’re using a tool that can pull data from the source to stage that data to a location and in a format that Power Query can easily read.

Adapter Bridge

Using an external programming/scripting language + framework (for example, an Azure Function written in C#/.Net), you could try exposing an API that’s easy for Power Query to consume. Internally, when used, this API would call to the underlying desired data source, retrieve the appropriate data, reformat it (if needed) then return it. This approach encapsulates the intricacies of interacting and authenticating with the source inside the bridge, keeping those complexities out of Power Query.

In a sense, External Data Staging and Adapter Bridge are opposites. The former stages a static copy of data, which Power Query then later reads, while the adapter bridge translates in real-time between Power Query and the source. Each have their place, with pros and cons.

External Language Integrations

Power BI’s edition of Power Query has a level of support for running R and Python scripts. If it’s easier to interact with the source (or its data store) from R or Python, in theory, you could use a script written in one of these languages to call the source, fetch the appropriate data, then hand that data off to Power Query.

While technically this can work, there are some significant limitations: R and Python are only supported for Power BI datasets (so not from Excel or from Power BI dataflows). Also, when used from the Power BI Service, a personal data gateway (not an enterprise data gateway) is required. In many situations, it is preferable to minimize the use of personal data gateways, which may realistically constrain using Python and R from Power Query to Power BI Desktop only.

Conclusion

There you have it: Quite a few options to evaluate when Power Query doesn’t have a built-in connector for a source you’d like to connect to.

Hopefully, when you find yourself in this situation, one of the earlier, easier levels of our exploration applies. Regardless, there’s probably a way to get data from your source to Power Query. Hopefully, doing so will be easy (though unfortunately, that is not guaranteed); at minimum, in most cases, it should be possible.

Leave a Reply

Your email address will not be published.