locked
Azure SQL database -> Power Query -> Power Pivot + scheduled data refresh RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi all,

    We are working on a POC where we want to create a reusable Power Query that pulls data from a Azure SQL database. We want that data to be inserted in a PowerPivot model which should be refreshed automatically from a Power BI site.

    As per the following documentation we understand it should be possible:

    https://support.office.com/en-gb/article/Supported-Data-Sources-and-Data-Types-cb69a30a-2225-451f-a9d0-59d24419782e?ui=en-US&rs=en-GB&ad=GB

    However we are struggling to do so.

    We have tried to create a data source on the Power BI admin center using the connection string coming from the Power Query however we got the following error:

    Unsupported data source types detected
    The Power Query connection string contains one or more unsupported data source types: {DataSourceKind}. Please refer to the data source support matrix to review the data source types supported from a Power Query connection string.

    On the one hand we understand that Power Query is a valid data source for Power BI sites and Azure SQL is also a valid data source for a Power Query query. What are we missing?

    Could you please confirm that it is possible to refresh a Power Pivot model that reads the data from a Power Query which in turns pulls from an Azure SQL database?

    Could anybody provide some gidelines about how to do it?

    Thanks for your help


    Tuesday, February 10, 2015 6:45 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi Ben_m1,

    Have a look at this link:

    http://blogs.msdn.com/b/powerbi/archive/2014/08/19/scheduled-data-refresh-update-new-data-sources.aspx

    This article contains some details for a Power Query connection to a non-on premise data source.

    Regarding the error you get when creating the datasource, it is probably caused by the query string of your Power Query connections:

    "Please note that #literals, Web, SAP BusinessObjects, Active Directory, HDFS, Facebook, Exchange and Current Excel workbook are not yet supported sources"

    When we perform any transformation in Power Query, the query string usually contains #literals. Depending on the kind of transformation you are doing, the operation could not be supported.

    Hope this can help you :)

    Wednesday, March 4, 2015 11:50 AM