Desktop Refresh Error: Query "references other queries or steps ... "

[tylerabbott]

On nearly every report I have in Power BI Desktop, I refresh the data and get this error message on random tables/queries in my dataset. To 'fix' it, I have to open the Power Query Editor, refresh all previews in there, then close the editor and refresh the data in the report view again - and everything refreshes no problem. So, it's not actually an issue with my queries at all - it just seems to be a bug in PBI Desktop.

[paulh-sefa]

Well, the ticket with Microsoft wasn't especially helpful. Essentially the answer I got was that this issue was due to an architectural constraint in Power BI Desktop and that it wasn't worth the re-architechting effort to fix it. They pointed me at this blog post (https://excelguru.ca/power-query-errors-please-rebuild-this-data-combination/), which at least somewhat explains the literal meaning of the problem and outlines some steps to reorganize queries to avoid the problem.

TLDR: you will only get this problem if a single query references a data source directly (e.g., the original source in the query is the database, spreadsheet, etc.) and then you join in another query defined elsewhere in the dataset. If instead, you load both data sources into intermediary queries and then join those queries in a third query, you won't get the error.

There are some downsides to this, notably that you will lose any query folding after the join, even if the two original queries are pulling from the same database.

Frankly, I'm probably just going to go with Sophie's solution and ignore the problem haha.

[C4YNelis]

Hi @paulh-sefa ,

that was the link I actually looking for earlier to post here, however, I couldn't find it anymore. I can unfortunately say for sure that this is not always a solution. I do this with all my queries and I still get the error. It is definitely a bug.

Essentially, always load your sources in a "loading stage". I usually group those source queries. I trim them, select columns and sometimes filter them (as long as those filters don't depend on external sources, otherwise you get this formula.firewall error). There queries are not loaded to the report.

In a second stage you can do all transformations, merging, etc. I usually group those too. These are still not loaded to the report though.

And finally in a third stage I just do appending (when applicable) and finally some reordering if desired and renaming. These queries are the ones I load as tables to the report (I group those too).

This way, you shouldn't encounter the formula.firewall issue.

However, that is unrelated to the primary issue raised here. It does solve Sophie her refreshing problem most likely. Also, my advice would be to leave the privacy levels intact. They are there for a reason. If you set your privacy levels correctly (usually organizational), than you shouldn't have any problem refreshing via a gateway, as long as you have a "common" datasource (there are exceptions, where you might need to build a custom connector).

[057Sophie]

I will try to have a look at the query causing the error.

But I am not sure that this link will help preventing me having this error while refreshing from desktop as I proceed exactly like you.

I split all my queries into small ones.

The first always beeing the "import" query from the source. (not loaded in the data model)

Then I do all the other queries starting from the import query.

Like on this model:

The error by refreshing always comes from "Check Notif" and this is my editor:

let
Source = Table.NestedJoin(#"01 Import Mail", {"ECO"}, TRACKING_ECO_STATUS, {"ECO"}, "TRACKING_ECO_STATUS", JoinKind.LeftOuter),
#"TRACKING_ECO_STATUS développé" = Table.ExpandTableColumn(Source, "TRACKING_ECO_STATUS", {"ECO", "Notification_Date"}, {"ECO.1", "Notification_Date"}),
#"Colonne conditionnelle ajoutée" = Table.AddColumn(#"TRACKING_ECO_STATUS développé", "Check Notif", each if [Date Received] = [Notification_Date] then true else false)
in
#"Colonne conditionnelle ajoutée"

So I don't see any "external data source" on my query, while it is always bringing me the error we are talking about in this post.

[C4YNelis]

Hi @057Sophie ,

I don't see any external sources in your query either. However, I find that the table and column which Power BI Desktop reports the error on, are not always the same query that contains the actual error. And that being said, this is indeed exactly what I have too, getting these errors where there is no obvious cause for one.

However, for this specific query, your external source seems to be loaded in #"01 Import Mail". Make sure you don't have any references to other queries there (such as merges, of filters).

[TorsteinZahl]

Did some of you solve this problem? I have had this in some years now, and its really boring... 
Any news from Mircosoft or any ez workarounds?

Good thread!

[C4YNelis]

Hi @TorsteinZahl ,

unfortunately, no. I've just finished a support ticket for another issue and I was actually about to create a supportticket for this issue, so I'm also very interested to hear if anyone else has found anything so far?

This issue has cost me a lot of time already over the years... Especially with large reports this is a real drag.

[paulh-sefa]

@TorsteinZahl The only "solution" that has ever worked for me is to just ignore the error (as posted earlier in this thread by 057Sophie).

From Settings, you can either disable this error globally (GLOBAL > Privacy) or for a specific report (CURRENT FILE > Privacy). Just change the Privacy Levels setting to "Always ignore Privacy Level settings" for the global setting or "Ignore the Privacy Levels and potentially improve performance" for the current file setting.

Just remember that this setting can't be disabled in the Power BI service, so your privacy levels do actually need to match before you publish.

[frithjof_v]

I'm having this same issue now. I hope MS will fix it.