Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am getting the following error:
Formula.Firewall: Query 'Query1' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
In my first query I am accessing an API to get a list of URLs. In the second query it uses this list of URLs to then go back to the API and request all of the data.
I understand what the complaint is, but how do I get around it? I am actually always hitting the same domain for both queries, but maybe it doesn't know that. I suppose it wouldn't know that until runtime.
I have read that in Excel PowerQuery you can go to:
File=>Options and Settings=>Options and set the Ignore Privacy Levels option.
But no such option seems to exist in Powe BI Desktop. Does anyone know what I'm doing wrong or how I can get around this restriction, it seems like a common usage scenario?
Solved! Go to Solution.
Ken Puls blogged about this here
http://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
Please try the following in Power BI Desktop:
File -> Options -> Privacy -> Select “Ignore Privacy Levels…”
It helped me.
Fantastic! This saved my day (and most of yesterday 🙂 )
Hello,
I did this privacy level change, it solved for Power BI Desktop refreshes, but web refresh is still failing due to the same error.
This is the article previously referenced in this thread:
https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
I confess I had to read it a few times to get it.
Basically, each query needs to be loaded into a staging query first. Then, you can combine these staging queries.
Fred
You wrong it not working like you think, separated queries still show error firewall
All of my data retrievals are now done in external functions
However, yet again, I had a problem with this frustrating error. I called a standard function to return some parameters from a parameters sheet. Suddenly I got the Firewall error.
Given that I have written some VERY complicated scripts which retrieved data from various sources and worked no problem but written other scripts which are not (seemingly) as complicated but have failed with this error, I am still of the opinion that this is Microsoft's catch-all, can't cope error message!
Also, I sometimes get this error when stepping through the code (in the "advanced editor") but not when running the script, explain that!
I've just had it happen again ... twice!
I edited an existing script.
I didn't reference any additional (external) data.
I took a column creation out of a group statement and created it in the next step, the idea being to check a result from the group creation, something you can't do inside the group creation.
I also added a step inside an existing IF statement to check if the new column (which can be null or a record) was null before checking the contents of the column.
That's it, that's all I changed.
It failed with the usual error.
I changed four other scripts (which reference the same data but output different results) in the same way and they worked fine.
Even more bizarrely, one other script which I changed in the same way worked fine in the "Advanced Editor" and then failed when I quit back to Excel and ran the script!
This all adds weight to my belief that this really is a default "I can't cope" error message.
What's even more frustrating is if you read the actual error message it states "references other queries or steps" ... every step references other steps, so this is a meaningless error message!
It also means that I have literally no idea what the problem is or how to resolve it.
So frustrating.
You're right, this is a misleading "can't cope" messagem for a Power BI bug.
If this issue was only due to referencing other queries, nobody would be able to solve it, as many have, by using staging queries, custom functions, or changing the steps order.
This is madness.
I have tried using staging queries, and also making data retrieval in custom functions. None worked.
I'll probably have to start using a data lake to make simple merges between data sources, only because of this issue.
Hello all,
I came across this post because I experienced similar error message and followed all the proposed solutions here, but I keep getting the error above. What I try to acomplish is the following: I have a list of F1 drivers with their social media screennames in a SQL table (DimDriver), first I would like to add number of followers, tweets etc from the Twitter API. What I did was follow the oAuth method form this blog https://chris.koester.io/index.php/2015/07/16/get-data-from-twitter-api-with-power-query/ and put it all in a custom function.
Next I made a query referencing my DimDriver( renamed it directly to "twitterdata"), filtered on all driver with a screenname in the table and as a last step I invoked a custom column using the above function. As far as I can see that makes only one external data source, but still no luck. Could it be the custom function?
Any help would be appreciated, the F1 reports are openly available on our companies website for training purposes, if anyone is interested!
The code I used:
let Source = Twitterdata, #"Removed Columns" = Table.RemoveColumns(Source,{"Driver", "Reference", "Number", "Code", "Birthdate", "Nationality", "WikipediaURL", "Hashtag last name", "Hashtag code", "Facebook", "Instagram"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Twitter] <> null)), #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Twitter", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Twitter.1", "Twitter.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Twitter.1", type text}, {"Twitter.2", type text}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Twitter.1"}), #"Invoked Custom Function" = Table.AddColumn(#"Removed Columns1", "TwitterFunction", each TwitterFunction([Twitter.2])) in #"Invoked Custom Function"
Many thanks in advance!
Rens
It's hard (for me) to tell without seeing it "properly" but there doesn't seem to be anything wrong.
The only thing I can say is that I now have ALL data loads in separate functions ... I added a function which gets passed a sheet/range name and it loads the data.
I see that you have Source=Twitterdata ... what is Twitterdata, it doesn't seem to be a function as it doesn't have () closing brackets.
Apart from that this is a simple script which I would have hand-coded as a single multi-line statement so I'm rather puzzled.
Hope you sort it out. Good luck.
Solved just using "Merge" as soon as possible on the query.
Seems that editor doesn't like to do merges by the end of the query as it was clearly explained on excelguru blog.
Josep.
Hey the2nicks,
Actually TwitterData is referring to another Query, the function is invoked on the last line
#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns1", "TwitterFunction", each TwitterFunction([Twitter.2]))
The code for the function is:
let FxGetTwitterUserData = (ScreenName as text) => let authKey = "Basic " & Binary.ToText(Text.ToBinary("<<here are my twitter API keys ;-)>>"),0), url = "https://api.twitter.com/oauth2/token", GetJson = Web.Contents(url, [ Headers = [#"Authorization"=authKey, #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"], Content = Text.ToBinary("grant_type=client_credentials") ] ), FormatAsJson = Json.Document(GetJson), AccessToken = FormatAsJson[access_token], AccessTokenHeader = "bearer " & AccessToken, GetJsonQuery = Web.Contents("https://api.twitter.com/1.1/users/lookup.json?cursor=-1&screen_name="&ScreenName, [ Headers = [#"Authorization"=AccessTokenHeader] ] ), FormatAsJsonQuery = Json.Document(GetJsonQuery), FormatAsJsonQuery1 = FormatAsJsonQuery{0}, #"Converted to Table" = Record.ToTable(FormatAsJsonQuery1), #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "description" or [Name] = "favourites_count" or [Name] = "followers_count" or [Name] = "following" or [Name] = "follow_request_sent" or [Name] = "friends_count" or [Name] = "screen_name" or [Name] = "status" or [Name] = "statuses_count" or [Name] = "url")), #"Transposed Table" = Table.Transpose(#"Filtered Rows"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"screen_name", type text}, {"description", type text}, {"url", type text}, {"followers_count", Int64.Type}, {"friends_count", Int64.Type}, {"favourites_count", Int64.Type}, {"statuses_count", Int64.Type}, {"status", type any}, {"following", type any}, {"follow_request_sent", type any}}) in #"Changed Type" in FxGetTwitterUserData
Of course it's nicer to hand code, but this was (I think) the 4th try to get it working, so I'll clean up later
Thanks for your reply
As far as I'm concerned this is their catch-all, can't cope error message!
I have a spreadsheet with a lot of queries (which create data for sheets in the spreadsheet) and function queries which reference that data and return to other queries, I did it in this way as I had repeatedly run into this error and it was suggested to me that placing the data retrieval in a function would resolve the issue, but it doesn't.
I have several very compliccated scripts which work fine. I just copied a few lines from one of those scripts into a far simpler script
- call a common function to retreive data from a sheet into a table
- a function to reference the above table to get a piece of information
- a call to the above function to get the required piece of information
and, BANG, formula firewall error.
Looking at the message it tells me that the error is in the last step in the script BUT the retrieval of the data is in the previous step!
I stepped through the script and it then failed on the previous step.
The reason it was failing on the final step when I ran the whole script is because M uses a lazy interpretation model which only tries to determine the value of something when it's needed, so in the final step it referened data from the previous step and that's where the problem occurred.
And this is why I think these errors occur all too frequently ... the lazy interpretastion ... I'm sure that if every step were separately processed (and the result stored until the end of the script) that a lot of these errors would not occur ... if memory is an issue then add a command to discard something when it's no longer required.
I've looked at my script and tried to work out what the hell that error message means and how to fix it and I simply don;'t understand ... why does the same kind of processing work fine in another script?
I've had similar problems in other scripts ... I added a parameter sheet to the spreadsheet so that I could change the query results without having to edit the script every time ... retrieving the parameters works fine in most scripts BUT in a couple scripts when I add the step to retrieve the parameters I get the Firewall error ... surely this is simply a matter of M not being able to cope and throwing out this generic and unhelpful message?
I wish they would fix this.
After a couple hours I found a workaround for the problem I was having but it really just confirms what I already think about M.
I found that the problem I was having was NOT down to accesing an external data source (all of my data loads are done in functions) but because I load a table, create two filtered tables from that table and then join them together. Why it would object to that I don't know, but it does ... I suspect (as per my previous post) that it's something to do with the lazy interpretation and it getting confused!
I then moved the splitting and re-joining of the table into a function script and it worked!
I think M / Power Query is a fantastic tool but it needs so many improvements ...
1. Ability to save files from within the Power Query environment ... qiute a few times Excel has hung/crashed when exiting the environment and I have lost hours of work!
2. Ability to export all scripts to text files in a specifed folder.
Would be nice ...
1. The Advanced Editor name is a bit of a joke as it anything but!. Colour-coding and all of that would be nice BUT for starters how about some basic Notepad-style editing features such as Search and Replace?
2. I really wish I could change the editor font size as it is too small, I don't want to have to change all of Window settings just because of this.
3. Environment state. It would be nice if the opened/closed groups stayed opened/closed in Power Query and the Excel sidebar, I constantly have to close the groups I'm not interested in.
100% agree with both these statements:
1. Ability to save files from within the Power Query environment ... qiute a few times Excel has hung/crashed when exiting the environment and I have lost hours of work!
2. Ability to export all scripts to text files in a specifed folder.
In essence, the option to do source control in some practical way from Excel PowerQuery and PowerBI PowerQuery.
"I think M / Power Query is a fantastic tool but it needs so many improvements ..."
As far as I m concerned, I would say only :
"it needs so many improvements ..."
So, regarding editing of Power Query, I just found out that there's an addon for Power Query in Visual Studio (via Adam Saxton), and just started using it. He also mentioned that there's one for Visual Studio Code as well.
Ken Puls blogged about this here
http://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
Updated URL for Ken Pul's article: https://excelguru.ca/power-query-errors-please-rebuild-this-data-combination/
First of all, this link mentioned no longer exist, so this is definitely not a solution.
Then. In my scenario, I've defined a transformation for a JSON file from a known format as a formula. This transformation DOES use data from other sources, because it's exactly what makes it useful as a formula, and the very reason for it to be converted into a formula.
Problem is, this formula becomes useless due to this error - unless I go and apply the security settings fix.
I understand this won't work in the cloud. But then, how to fix it AND keep the formula useful?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
104 | |
97 | |
39 | |
30 |