Data Refesh does not support script only queries RRS feed

  • 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:

    Correlation ID:


    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

    Saturday, May 3, 2014 6:04 AM


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 3, 2014 12:26 PM
  • 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?



    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


      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

      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 })

    Marco Russo

    Saturday, May 3, 2014 6:40 PM
  • What if you replace the #table with Table.FromRows({{1}, {2}, {3}, {4}, {5}}, {"Number"})?
    Monday, May 5, 2014 6:46 PM
  • 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 5, 2014 6:50 PM
  • 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.


    Marco Russo

    Monday, May 5, 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 5, 2014 10:12 PM
  • Ok thanks!

    Marco Russo

    Monday, May 5, 2014 10:44 PM