Issue while reusing existing table/query

[danishwahab]

Hi Everyone,

I'm facing an issue while refreshing a report which is published on the Power BI Report Server. Everything works fine while refreshing on the Desktop however fails when refreshing same report deployed on Report Server. Here is the error:

Unable to combine data] Section1/LocationTypeMaster/Changed column type 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.

I had ruled out other suggestion/solutions given online and already done below changes:

- Options and Settings -> Data Source Settings -> Privacy Level of all connections are set to "None"

- Options and Settings -> Options -> Load Data -> "Always ignore Privacy Level settings"

I had remove all tables/queries from the Transformation section and found that this issue is coming when use another query/table as a source as shown below

This means instead of resuing an existing query/table we have to rewrite entire transformation logic in the new table wherever we wanted to refer existing query/table.

Is there any other way to resolve this issue other than rewriting same logic again? This clearly is not an ideal approach.

Thanks!

[Anonymous]

Hi @danishwahab ,

During my investigation I have found users in the past who have had similar experiences to you .
The solution given by other forum users was: You need to combine all the queries into one query.

Hope it helps!

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[danishwahab]

Hi @Anonymous ,

This will too much rework. I would also like to inform that this refresh was working few months back and now they had stopped working. If this is working fine on local desktop why it is failing on the server during schedule refresh?

Thanks!

Any idea? @Anonymous 

[collinq]

Hey @danishwahab ,

I have seen this error sometimes when I open a file in Desktop and the file has not yet "loaded" all of the queries.  What I have to do is let all the queries load (and ensure that they have all loaded in the Power Query editor side) and then hit refresh and it works fine.  This does not always happen and it doesn't always kick back on the same query, but the "steps not complete" and the "rebuild" are consistent.  

let me know if waiting it out actually helps!

[danishwahab]

Thanks @collinq for your response. 

In my case, there is absolutely no issues in the Desktop. I'm able to refresh individual tables/queries and final report however this error comes only when refreshing a published report on the Report Server.

Thanks!

[collinq]

Hey @danishwahab ,

Thanks for the clarification, I misunderstood and thought the issue was in Desktop.  I did read the answer from @Anonymous .  I agree with you though that this can get cumbersome.

I am wondering if you have confirmed that every single data field has a data type that is NOT "any"?

What about the Section1/LocationTypeMaster/ field specifically?

Also, do you have a merge or two where the issue might be coming from?

I am wondering if instead of redoing the whole thing if you can move the step up or down and get around the issue???

[danishwahab]

The issue isn’t with datatypes. The problem arises when referring to another table as a source, which causes the scheduled refresh to fail on the server after deployment. However, the refresh works fine on the desktop.

Thanks!