[Unable to combine data] Section1/Query1/AutoRemovedColumns1 references other queries or steps

[Anonymous]

I have googled and read several blog posts and threads related to this issue, and nothing works.

What I have is a file based on Chris Webb's example 

I have 3 queries. They load fine in Desktop, but they fail in Services with the error "[Unable to combine data] Section1/Query1/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.. The exception was raised by the IDataReader interface. Table: Query1."

If I hard-code Query2 with #table() instead of using a shared file, the reload works. Privacy settings are the same for both sources, and the option to "ignore privacy" is ticked.

Query1:

let
    Terms = Query2,
    Output = 
     Table.AddColumn(
       Terms, 
       "Search Successful", 
       each SearchSuccessful([Term])
      )
in
    Output

Query2:

let
    Source = SharePoint.Files("SHAREPOINT", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "SHAREPOINT/TEST/")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),

        Source2 = Excel.Workbook(#"Filtered Hidden Files1"{0}[Content], null, true),
        Sheet1_Sheet = Source2{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Term", type text}})
in
    #"Changed Type"

SearchSuccessful:

let    SearchSuccessful = (Term) => 
    let
        Source = 
         Json.Document(
           Web.Contents(
             "https://data.gov.uk/api/3/action/package_search", 
             [Query=[q=Term]]
            )
           ),
        Success = Source[success]
    in
        Success

in SearchSuccessful

Excel file is just this, same data as Chris Webb's example:

 

[Jmenas]

Hi @Anonymous,
Would you able to fix this? 
@v-yulgu-msft  I have the same issue and I am using 1 separate query and 1 Function query (with its own separate query). Why doesn't PBI service doesn't allow to combine 2 external data sources? Is there any workaround? 

best,

J.

[Anonymous]

@Jmenas No, I can't get the example to work still.

However, I built another app with the same method as the example but different website and excel and it worked for some reason on Services and Desktop. Same method, same permissions.

[v-yulgu-msft]

Hi @Anonymous ,

The error prompted that you cannot combine an external data source within another query. To deal with the problem, strip the “external” data source and put it in it’s own query.

For details, Power Query Errors: Please Rebuild This Data Combination

Best regards,

Yuliana Gu