Forum Discussion
.Unable to combine data
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.
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?
AlexisOlson I was hoping for a QuickFix but I got lucked out.
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 ResultAlexisOlson 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.