none
Data Refesh does not support script only queries

    Question

  • I created a Power Query transformation that does not contain any data source because it generates a fixed table (imagine a Date Table, with certain columns marked based on current day value). I receive this alert trying to create the data source:

    Discover data sources - You can only use a connection string that has SQL Server or Oracle data sources. Also, the data sources cannot include native queries. Tracing ID: 040eda49-bcf9-4c82-af1c-69f6efb26cd0 - ‎07‎.‎55‎.‎44

    The refresh fails with this error:

    Failure
    Correlation ID:

    10225488-17be-4256-b03e-be975b87a281

    OnPremise error: Sorry, we can't refresh connections to this kind of data source in Power Query yet.

    I am wondering whether I can workaround the check by creating a SQL Query that returns an empty table that will be ignored in a following join, but is that really necessary? Why not refreshing a transformation that doesn't access to any data source?


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Saturday, May 03, 2014 6:04 AM

Answers

All replies

  • It's possible that the static analysis that determines which data sources are in use has failed. It's also possible that this scenario just wasn't anticipated. I'd be curious to know whether your empty SQL query workaround works; it would tell us which of two teams to forward the problem to :).
    Saturday, May 03, 2014 12:26 PM
    Moderator
  • I tried to workaround the issue using the script below. However, even if creating the Data Source in Data Management Gateway worked well, without raising any error, I had this error (script follows).

    Is the Table.Combine limited? Or what is the issue in this case?

    Thanks!

    Marco

    Scheduled start   time: 5/3/2014 1:58 PM

    Actual start time:   5/3/2014 2:31 PM

    Failure time:   5/3/2014 2:31 PM

    Status:

    Failure
      Correlation ID: 3abb39be-05df-40e5-8366-45bdf58ccdc3

                                  
       

    Data connections status:

       
       

    Power Query - DimProductCategory

       
       

    OnPremise error: Sorry, we can't refresh connections to this     kind of data source in Power Query yet.

       

    Report owner: Marco   Russo

    let
      Tab1 = #table( {"Number"}, { {1}, {2}, {3}, {4}, {5} } ),
      Source = Sql.Database("servername", "AdventureWorksDW2012"),
      dbo_DimProductCategory = Source{[Schema="dbo",Item="DimProductCategory"]}[Data],
      FilteredRows = Table.SelectRows(dbo_DimProductCategory, each [ProductCategoryKey] = 0),
      InsertedCustom = Table.AddColumn(FilteredRows, "Number", each 0),
      RemovedColumns = Table.RemoveColumns(InsertedCustom,{"ProductCategoryKey", "ProductCategoryAlternateKey", "EnglishProductCategoryName", "SpanishProductCategoryName", "FrenchProductCategoryName", "DimProductSubcategory"}),
      Append = Table.Combine({RemovedColumns,Tab1 })
    in
      Append


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo


    Saturday, May 03, 2014 6:40 PM
  • What if you replace the #table with Table.FromRows({{1}, {2}, {3}, {4}, {5}}, {"Number"})?
    Monday, May 05, 2014 6:46 PM
    Moderator
  • The problem appears to be that all literals of the form #literal are being mis-analyzed. But for most of these, there should be an alternative function-based way to construct the same value, and this should work.

    Monday, May 05, 2014 6:50 PM
    Moderator
  • I confirm that the workaround using Table.FromRows({{1}, {2}, {3}, {4}, {5}}, {"Number"}) worked well for scheduled refresh.

    The workaround is not enough for a pure script query, you always need a SQL or Oracle Query in order to run the scheduled refresh.

    Thanks!


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Monday, May 05, 2014 9:29 PM
  • So a query that doesn't reference any data sources and also doesn't use any of the literal forms that look like #this will still not work? Got it.

    EDIT: I'm told that this is a known limitation of this initial version of Power Query refresh. Your workaround seems quite reasonable to me. You shouldn't even need to merge the SQL part of the query with the remaining part; I believe just having it there unused will satisfy the requirement of referencing at least one source.

    Monday, May 05, 2014 10:12 PM
    Moderator
  • Ok thanks!

    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Monday, May 05, 2014 10:44 PM