.Unable to combine data

[smpa01]

@ImkeF @AlexisOlson @miguel507 

TLDR: I keep on getting the following error when I am trying to refresh a dataset (semantic model) in service. How do I debug this?

Data source error: [Unable to combine data] [Unable to combine data] Section1/childrenDim/src44 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 IDbCommand interface. Table: childrenDim.

childrenDim table is created as following like this. I am querying server1 tbl to get the EmpID and reducing the distinct IDs with Accumulate to be passesd on dynamically to sql statement to query server2 table to get additional details.

//children table
let
    Source = Sql.Database("Azure_SQL_SERVER1", "production_db", [Query="// some query that returns a two coulmn table as following"])
in
    Source

| EmployeeID 	| OwnerID 	|
|------------	|---------	|
| 123        	| 789     	|
| 456        	| 987     	|

//childrenDim table

let
    src0 = ()=> Children,
    src00 = src0(),
    src1 = ()=> src00[EmployeeID],
    src11 = src1(),
    src2 = ()=> List.Distinct(src11),
    src22 = src2(),
    // array {123,456} reduced to generate a string (123,456) to be passed on 
   // to sql later
    src3 = ()=> Text.Replace("("&List.Accumulate(src22,"",(state,current)=>Text.From(state)&","&Text.From(current))&")","(,","("),
    src33 = src3(),
    src4 = let a = Sql.Database("Azure_SQL_SERVER2", "production_db", [CommandTimeout = #duration(0, 1, 0, 0)])
in  Value.NativeQuery(a,"
  select TRY_CONVERT(bigint,Emp_ID) as EmpID,First_Name+' '+Last_name as Name
FROM schema.[Dim_Emp]
where TRY_CONVERT(INT, Emp_ID) IN"&src33&"",null,[PreserveTypes = true, EnableFolding = true])
 //src44 = src4()
in
    src4

// the above returns this
| EmpID 	| Name  	|
|-------	|-------	|
| 123   	| lorem 	|
| 456   	| ipsum 	|

This fails in service but locally it has no issue. I need to work in service , how do I get past this error.

 Thank you in advance.

[miguel]

This article might be able to help you better understand why the error was raised:

Behind the scenes of the Data Privacy Firewall - Power Query | Microsoft Learn

To summarize, create reference queries before you combine data or try to combine everything into a single "section" (Which would be inside of a single query)

[miguel]

forgot to say this, but also make sure that all your data sources have the same privacy level (probably either Public or Organizational)

[ManuelBolz]

Hello @smpa01  ,

i once had a similar error. For me it was the privacy level of the data sources. To test it, you can set it to “ignore”.

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github

[smpa01]

@ManuelBolz  it was already ignored and the above setting no effect. This can be ruled out as a potential solution to my use case.

[ManuelBolz]

Hello @smpa01,

Can you recreate the query and try to load?

The error message says "[...] Section1/childrenDim/src44 [...]" even though you commented out src44.

[smpa01]

I can confirm this is a typo on my side and it should not have been commented out. Overall, it does not resolve the issue.

[AlexisOlson]

@ManuelBolz may be on to something. Try it with the comments removed.

@smpa01, what is the reasoning behind the way you're defining src0, src00 in two steps instead of just src00 = Children?

[smpa01]

@AlexisOlson  I was hoping for a QuickFix  but I got lucked out.

[AlexisOlson]

Ah, got it. Let's try using that trick but cleaning up the query a bit.

let
    IDs = Children[EmployeeID], 
    Buffer = List.Buffer(List.Distinct(IDs)), 
    TextList = (L as list) as text => Text.Combine(List.Transform(L, Text.From), ","),
    TxtIDs = TextList(Buffer),
    DB = Sql.Database("Azure_SQL_SERVER2", "production_db", [CommandTimeout = #duration(0, 1, 0, 0)]), 
    Query =
        "SELECT
           TRY_CONVERT(bigint, Emp_ID) as EmpID,
           First_Name + ' ' + Last_name as Name
       FROM schema.[Dim_Emp]
       WHERE TRY_CONVERT(INT, Emp_ID) IN (" & TxtIDs & ")",
    Result = Value.NativeQuery(DB, Query, null, [PreserveTypes = true, EnableFolding = true])
in
    Result

[smpa01 · Accepted solution]

@AlexisOlson  I did not have a chance to try out your query but thanks for this. But thisis not a query level issue at all. My data-model has a marriage of 34 tables altogether coming from Azure SQL, SSAS, SP, PL SQL, DREMIO SQL, Dataflow and I want to be in a situation where I am able to take values from one db to seamlessly pass on to another before (SSAS to SQL and vice versa, SQL to SQL) the modelling can even happen. 

I was constantly getting errored out when I published this in service due to Unable to combine data.... error, I did not experience the same error locally.

What actually helped me out is this ChrisWebb's video .

There are a lot of key moments from the video, but for me is 16:58, 18:19.

16:58 tells you that why you should never turn on the 3rd option EVER. Cause that gives you a false hope that everything is well but ultimately fails in service. Thi works under a complete false pretense of that turning 3rd option is a QUICK FIX and there is nothing else required. With 2nd option turned you will get the same errror locally as in service. with the 3rd otion turned on you will be fine locally but will be screwed in service.

18:19 tells you in order for data to flow from one source to another what the data source Privacy needs to be. In my case, I want the data to travel free from one source to another source. So I changed my privacy level to be PUBLIC that allows seamless flow not only locally but in service.

Also, I changed my queries to function as Chris mentionedwith two steps and not a single step in 46:47.