Problem Merging Queries: Formula.Firewall: Query references other queries, so it may not....

[KCinMelbourne]

MS - please - do something to make this go away.

It is infuriating in every possible way and results in the creation of a large number of unnecessary queries.  Rather than making everyone create staging queries, can't this just be addressed?

[v-qiuyu-msft]

Hi @KCinMelbourne,

Would you please clarify the issue with more information so we can understand it better? 

Best Regards,
Qiuyun Yu 

[KCinMelbourne]

Hi Qiuyun, This is a known issue where you receive the above error if you have an external data source in a query then separately merge or append data from another query into it. According to the forums, there are two known workarounds for this. 1. To split all the source data into their own query. Reference that query in a second query to perform any merge or appending of data. 2. Change your privacy settings to "Always ignore privacy settings". I've applied both of these methods and option 2 definitively works. Option 1 however I've found to be inconsistent where sometimes I have a query without any merged data and it still fails. The most frustrating thing about this particular issue is that the error doesn't occur when you build the query. In fact, it will work when developed, work when refreshed and only break when you edit (For example) a parameter which feeds into that query. It would be good for MS at the very least to release documentation explaining the issue so people can make an educated decision between option 1 and 2 or any option MS may be aware of. It would be even better if PBI was amended so the issue didn't occur at all. Cheers Kirsty

[v-qiuyu-msft]

Hi @KCinMelbourne,

Thank you for your detail information but I still have some unclear points: 

>> This is a known issue where you receive the above error if you have an external data source in a query then separately merge or append data from another query into it. 

Would you please clarify which "external data source" you mean? 

>>According to the forums, there are two known workarounds for this. 1. To split all the source data into their own query. Reference that query in a second query to perform any merge or appending of data. 2. Change your privacy settings to "Always ignore privacy settings". I've applied both of these methods and option 2 definitively works. Option 1 however I've found to be inconsistent where sometimes I have a query without any merged data and it still fails. 

Would you please share threads URL you found in forums? 

Best Regards,
Qiuyun Yu 

[KCinMelbourne]

Hi @v-qiuyu-msft, In this instance, I'm pulling spreadsheets/CSV files from either desktop or SharePoint as well as data directly from Project Online via an OData feed. Most commonly though I've seen the error with spreadsheets/CSV. The best article I've read on it is here: https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/ Mostly, if I follow these rules, the error goes away. That said, the other day I was at the point where I only had the source extract in the query and I was still receiving the error which was frustrating. I also find applying this method results in a very large number of queries in larger models which is more difficult to manage. It was actually a friend of mine though who advised if I switch my security levels to ignore security, it resolves the issue. But I don't have an adequate understanding of the security risks of doing so. Cheers Kirsty

[v-qiuyu-msft]

Hi @KCinMelbourne,

Based on my test in Power BI desktop version 2.65.5313.841 64-bit (December 2018), create two tables, one get data from local Excel workbook, the other get data from Project online use OData feed, when we merge queries with these two tables, it will prompt us to set Privacy Level. After setting privacy level properly, merge queries is successful. See: https://docs.microsoft.com/en-us/power-bi/desktop-privacy-levels

In your scenario, would you please update your Power BI desktop to the latest version as ours then test it again. 

Best Regards,
Qiuyun Yu 

[KCinMelbourne]

Hi @v-qiuyu-msft,

Thank you for your advice.  I've been battling with this over the past week looking at your advice, as well as various blogs by Chris Webb and Ken Puls. 

I have performed the following:

• Split all source imports into different queries before a merge takes place
• Checked my permissions were at organisational level
• Occassionally even split a query in 2 that had 2 merges because it was erroring

After doing this, I also realised that my device is not by default under the same organisational account as everything else (ie. I log in on my personal email not my work email). I changed the default authorisation on the Local Files to use my work log in and the file refreshed on desktop and service (hoorah!). 

However, that said, a couple of days of it working and the error has started again for select queries, some of which I don't believe should error at all. I am still able to refresh on PBI Service, but the error occurs on desktop. I've found that:

• The error is usually kicked off when I have been editing code in the queries, even if it is something simple like changing the format of a column
• Sometimes, I can refresh a query individually and it will work but when I refresh the whole file, that query will fail
• Sometime, the refresh will fail on a particular query.  If I go into Query Editor, I can see a warning against that query or one of it's sub-queries. If I reload those one-by-one in Query Editor, when I load and refresh the final query that had the error, it will now refresh. 

When the new issues started, I also tried making the folders 'public' instead of 'organisational', I think that worked (still testing) but I'm not entirely clear on the implications of doing that.

I am currently using Version: 2.65.5313.1381 64-bit (December 2018) which appears to be the latest version.  As soon as I get a chance I will try to upload a copy of the calendar in my model which was creating the Firewall error but I simply don't understand why. I'll just need some time to strip out everything else. 

In the meantime, if you have any other advice, I'd really appreciate it.

Cheers

Kirsty

If you have any other ideas of 

[v-qiuyu-msft]

Hi @KCinMelbourne,

I would suggest you create a support ticket to let engineers look into the issue on your side. 

Best Regards,
Qiuyun Yu 

[KCinMelbourne]

Thanks @v-qiuyu-msft, I will do.

[raynok]

Hi @KCinMelbourne & @v-qiuyu-msft 

I had Similiar issue but was extremely relieved to this article https://powerbi.microsoft.com/en-us/blog/privacy-levels-for-cloud-data-sources/. I

[KCinMelbourne]

Thanks @raynok !

I actually forgot I raised this as I've been working directly with Microsoft.  Actually, they originally advised it would take 6 months but I got a really happy call earlier this week to let me know it was released early. I haven't got it working yet but I'm still hopeful!

KC