Unable to combine data-Source references other queries or steps Error

[Fraz707]

Hi 

I've been facing a problem while refreshing the dataset after publishing it to the app, it's giving the error which I've depicted in the scrennshot. To set the context , there was a requirement to create a dashbord in which we had to pass the query directly from the database while the sql server and database name has to be passed by parameter(for more clarity you can check this post:https://community.powerbi.com/t5/Desktop/Can-we-pass-a-SQL-query-as-parameter-from-the-database-itse...), so while doing that I faced the formula firewall error which I was able to resolve by changing the privacy settings and the dataset was getting refreshed on the desktop but after publishing it when i triggered a manual refresh it is showing error.( I've also tried Ken Puls method but that didn't seem to work, you can check it here:https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/)

Can anyone help me resolve it? Any help on this would be very helpful.

(Note: the _orders table is being reffred by the  final_orders  table here.)

To Give more clarity, I've explained what I did below.

I've created a query table in the Database an have imported it to powerbi 

Then I've imported my desired table from my database as I normaly would using the sql server database connection.

Then I changed the [Query=""] statement to pull data from the table instead.

= Sql.Database("ServerName", "DatabaseName", [Query=List.First(Table.Column(Table.SelectRows(QueryTable, each [Query_ID] = 1), "Query"))])

It works for Power BI desktop after ignoring privacy setting but gives the above error after publishing.

@v-shex-msft 

Thanks.

[v-shex-msft]

HI @Fraz707,

It seems like you are trying to reference query table records across different connection modes, I'd like to suggest you switch to import mode or test with same connection mode to instead.

In addition, have you enabled the 'parallel loading' on your report and make sure the second query table (the table who invoke previous records as connection string) loading after the raw table loaded?

Regards,

Xiaoxin Sheng

[Fraz707]

Hi @v-shex-msft ,

Thanks for you reply. All my queries have connection mode of import only and I've tried both method publishing the report after enabling the parallel loading as well as after disabling the parallel loading but still I'm getting the same error, can you think of something else that could be done to troubleshoot this issue?

Thanks.

[v-shex-msft]

HI @Fraz707,

Have you tried to merge these query steps into one query table? (e.g. move connect to second database steps to the first query table) It may fix the 'privacy level' issue between invoking data across different queries tables.

In addition, can you please share more informants to help us to clarify this issue? (e.g. refresh history, gateway log, request-id...)

Troubleshoot gateways - Power BI#refresh-history 

Regards,

XIaoxin Sheng

[Fraz707]

Hey @v-shex-msft  please take a look I've updated the question.

Thanks.

[v-shex-msft]

HI @Fraz707,

Maybe you can try to navigate to power bi service dataset and gateway data source settings to modify your data source and data credentials privacy level to 'organization' and try again.

Privacy Levels for Cloud Data Sources 

If you are work with the personal gateway, please refer to the following link to change the settings:

Use personal gateways in Power BI#use-fast-combine-with-the-personal-gateway 

BTW, what version of the report you used on power bi service hide? if you mean the raw version, I'd like to suggest you publish the report that modified 'privacy settings' to replace the old one.
Regards,

Xiaoxin Sheng

[Fraz707]

HI, Thanks for your respose @v-shex-msft 

Tried the 'Organization' level setting still not working in my case. Since its an organisational data therefore can't use the Personal Gateway for it. Tried replacing the Privacy setting and publishing it again but still its failing to refresh with the same error.

Any other suggestion particularly on power bi web , like the settings we have on desktop to ignore the privacy level?

Thanks.