none
Custom Data Adapters for PowerQuery

    Question

  • Hi,

    The list of data adapters / sources for Power Query is quite extensive, but there is talk of more becoming available in the future.

    I was wondering if there had been any thought about providing access to the API to write our own custom adapters (preferably in .NET)? For example, following the Reporting Services (on premise) model, where we can build both a results rendering engine and a custom query creation engine - which could be packaged and installed by a user and once done would appear in the list of data sources.

    Then once the user selects the source, it opens our custom query designer - which returns a connection string and query property which are persisted, and the PowerQuery engine can then pass those properties to an execute method which returns a data set.

    Of course we could always expose our data via an OData endpoint - but for those of us who are operating in different structured data sources as opposed to flat tables, it would allow us to hide the complexity of the data and enhance the offering.

    Cheers

    Tuesday, August 13, 2013 7:01 AM

Answers

All replies

  • Hi Brendan,

    Thanks for your feedback. We are considering the possibility of adding support for ODBC sources in the future, however we can't share any specific timelines at this point.

    Out of curiosity, could you also let us know what are the data sources you'd be interested in?

    Regards,
    M.

    Tuesday, August 20, 2013 2:12 AM
    Owner
  • Hi Miguel,

    In our case we would not be able to use ODBC. 

    We actually have a .NET custom data store - a database application (our own OLAP engine) which we would like to extract data from.

    To write an ODBC or OLE DB provider is a long winded and clunky process as it is all old technology. If Power Query could either support .NET data adapters, or as I said have support for custom data processing libraries the way Reporting Services has, that would be perfect. 

    On your end it means that you can allow developers of custom data sources to provide their own data adapters and increase the amount of usage of your tool. On our end it means we can team up with PowerQuery to get access to the awesome breadth of your BI visualisation without having to mess about with old technology.

    Cheers,

    Brendan


    Brendan Hayes

    Tuesday, August 20, 2013 2:20 AM
  • I'd love to see  DB connections to OLEDB drivers in the Power Query, like  in Power Pivot with the Other(OLEDB/ODBC) option.

    I use a IBM Series i (aka iSeries, AS/400) as my primary DB. It runs DB2, but not the LUW (Linux, Unix, Windows) variety, so the DB2 option doesn't work.

    Currently I use the IBM DB2 for i IBMDASQL OLE DB Provider successfully in Power Pivot imports.

    Connection string: Provider=IBMDASQL.DataSource.1;Persist Security Info=False;Data Source=S1045643

    I can obviously still important directly in Power Pivot, but could use the Power Query unpivot option to greatly simplify my SQL and reduce the query run time.

    Friday, August 23, 2013 3:40 PM
  • Thanks guys for your suggestions. We definitely hear your feedback about generic OLEDB/ODBC support and will keep it in mind for future product improvements.

    Regards,
    M.

    Wednesday, September 04, 2013 1:45 AM
    Owner
  • I'll second Brandan's request for an API to allow custom data adapters to be added. Not all datasources fit nicely into the Web/OData/ODBC scheme. And ease-of-use often requires a UI.
    Sunday, November 10, 2013 7:51 PM
  • Another way that such a custom integration could be achieved would be to do something similar to how Excel Web Query works. When you click "from the web" in Power Query, all you get to do is enter a URL, whereas the "from the web" button in the "Data" tab opens a web browser. This gives a 3rd party the option of creating a browser-based custom query designer. I think that Microsoft should add this feature back to Power Query.

    For now, Brendan, your best bet is to add a feature to your own query designer to generate either an OData URL or a full "M" expression (the direction I'm headed) which your user would copy and paste into Power Query.

    Sunday, November 10, 2013 8:21 PM
  • Here is (an albeit clunky) workaround.

    If you can access the data via Reporting services, you can then use the Reporting Services report as a data source for Power Query (via oData). It's not ideal, but it should get the job done. You can use SSRS to reshape the data for end users. SSIS can also be used in a similar manner.

    Cheers


    John

    Tuesday, November 12, 2013 12:44 PM