Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
@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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.