Over the past few weeks I’ve spent some time playing around with Power BI custom data connectors and while I don’t have anything to share publicly yet (other people are way ahead of me in this respect – see the work of Igor Cotruta, Miguel Escobar and Kasper de Jonge among others) I have learned some interesting things that are worth blogging about.
First of all, the data privacy rules around combining data from different data sources do not apply in custom data connector code. As the docs say here:
Data combination checks do not occur when accessing multiple data sources from within an extension. Since all data source calls made from within the extension inherit the same authorization context, it is assumed they are “safe” to combine. Your extension will always be treated as a single data source when it comes to data combination rules. Users would still receive the regular privacy prompts when combining your source with other M sources.
Those of you who have followed my recent series on this topic, or who have struggled with the Formula.Firewall error, will appreciate how much easier this makes combining data from different sources.
Secondly, you have a lot more flexibility when it comes to different types of authentication for web services. As I showed in my session on web services and M at the Data Insights Summit, there are a lot of limitations when it comes to working with web services in Power BI or Excel. Within a custom data connector, however, you can connect to web services that use OAuth for authentication, you can make POST requests to web services that require authentication and you can pass a web API key from the credentials store through an HTTP custom header and not just through a query parameter – none of which are possible in Power BI or Excel.
I’m sure there are a lot of other useful bits of functionality or behaviour that are only available in custom data connectors – I know I’ve only just begun to learn what’s possible. Even with what I’ve listed here, though, I get the feeling that there will be a lot of cases where you will have no choice but to build a custom data connector just to be able to access certain data sources, even if you only need to create a single report. There may also be cases where it’s preferable to build a custom data connector rather than embed lots of complex M code in a Power BI report or Excel workbook, perhaps to make code portability easier. It’s a bit of a pain to have to have Visual Studio and the SDK installed in order to do this, but building a custom data connector is fairly easy if you already know M and the development experience in Visual Studio (with intellisense!) is much better than in the Advanced Query Editor window.
Reblogged this on Paul Bunting • Software Architect.
Waiting for someone to create a Data connector to One Drive personal so that URL hacks are not required.
I think it would be possible…
And I’m waiting for someone to create a Custom Data Connector (*.mez) for Google Translate, the Google Distance Matrix and google longitude and latitude finder
Hey Chris I wasn’t sure where to ask this. I’m coding a custom connector and I have a problem with errors. It’s like the connector can’t handle errors. If the data source is with errors, even if I add a remove error step in ConnectorName.Contents( parameters ) => I get the error instead of the result. It won’t return the table with column with error in rows. Error examples: can’t convert between different types, can’t read #N/A, etc. I can’t change the source, I need to keep errors for this.
The code is working perfectly as function in power bi edit queries, but i can’t use that code as custom connector.
Do you know if there is something to avoid the break and continue with the column error?
Sorry for the late reply. I would need to test this to find out more, but is there a project-level property you can set that changes this behaviour?