Formula.Firewall Error in Power Query & Power BI: Rebuild This Data Combination Solved (MSPTDA 9.5)

Formula.Firewall Error in Power Query & Power BI: Rebuild This Data Combination Solved (MSPTDA 9.5)


Welcome to a follow-up
to MSPTDA video number 9. And yes, this is video 9.5. We got to talk
about Power Query’s terrible formula.firewall error. Now if you want to follow along
to see how to solve this error, you just download the same
Excel file for video 9 and the PDF notes
have a new note about the
formula.firewall error. But if you noticed, if
you tried to refresh, then the front folder many
bad tables query got an error. And here’s the problem. If I double click
and open this up, there’s the
formula.firewall error. And if we go up to
Advanced Editor, we want to remember that
that’s the folder path that we imported from an
Excel parameter query, but that’s a query
result. We also have a function query down here. And the problem
is the two queries might have a different
privacy settings. Now I’m going to show you
two ways to solve this. One is probably not very good. We can just change the privacy
so that none of that matters. Or we can simply
take this, which if we were to look
at what this returns, it simply returns a table
filled with all of the files from that folder path. So if we were to take that step
and use it in a separate query, we would not get this error. Now, before we do that,
I’m going to click Cancel. Here it is, up to File, down
to Options and Setting, Query Options. We want to go down to Privacy. If we change it to Always
Ignore Privacy Setting levels. And if you want to look at
the different privacy levels, there’s Private,
Organizational, and Public. Actually, this is a good link. If you click it, Private,
Organizational, and Public– data is visible to everyone,
just to some people, and the data is private. But if we say Always Ignore
Privacy Settings, then when we click OK and refresh,
that error will go away. There’s no more conflict between
two queries in their privacy levels. Now I’m going to go back
up, Options, Query Options, Privacy, and Combine Data. We’ll do the default
step, Click OK. Now if we refresh, we
get the error again. Now here’s how we’re
going to fix this. Go up to Advanced Editor. That’s the step we’re
going to have to simulate in a different query– folder.files. I’m going to close and load. Now remember, from
FolderManyBadTables was referring to a
folder path input. So when I double-click
and open this, this was the parameter query
where the first step was getting the table, and
then we navigated down to get this folder path. But guess what? That folder path was calling
to an external data source over in the other query. That’s what was
causing the conflict. So if we actually deliver
the full table in this query, we’re totally allowed
to use this query over in from FolderManyBadTables. Because there’s not
an external data call, it won’t flag it as
a formula.firewall with that privacy issue. So guess what? I’m going to add a step here. F of x and the function
we used was folder.files. By the way that’s the
name of the previous step, even though it says
Navigation there. Close parentheses. And now when I hit Enter, I
have loaded the table here. I’ve actually landed the table
from the external source. If you look at Ken
Pohl’s article, he has a great diagram that
calls this a staging query. I’m going to name the step over
here Blended Data and Enter. So we’ve landed the data. That’s the name of the query. I’m going to click
Close and Load. And that was connected
as connection only. Now I come back to
this, double-click, up to Advanced Editor. And guess what? In step one, now that
I’ve landed it over as a separate query and used
the folder path over there, now if I say source
equal to whatever the output from
that other query is, it will have no problem, done. And there it is. Because we took the query with
the external folder path out of this query and used
it in folder path input to deliver the table,
there’s no privacy conflict. Now I’m going to close and load. So formula.firewall, error
privacy levels for two queries are in conflict. Solution one, change Privacy
Level to Always Ignore Privacy Settings. That’s the setting
up in Power Query. Or solution two,
land the first query with an external connection
has Connection Only, then use that name query
in the second query. All right. If you feel like that video, be
sure to click that Thumbs Up, leave a comment, and
subscribe, because it’s always lots more videos to
come from Excel Is Fun. All right, we’ll
see you next video.

Danny Hutson

31 thoughts on “Formula.Firewall Error in Power Query & Power BI: Rebuild This Data Combination Solved (MSPTDA 9.5)

  1. I like your channal so much and i love your way when you explain but if you please can you explain how can i make the cruve with normal distribution .Thank you so much for your help.

  2. Hey mike… lately my organisation has updated microsoft tools… I can see little changes in the ribbon tab… "Get and Transform" is now on the left most side in the ribbon tab….have u seen it ???

  3. This is a really useful trick, learned from Ken Pulse, really an issue when even if you share your solution with dynamic file path! Thanks for sharing in video form Mike!

  4. cant wait to watch this video 🤪 but I have to take a test tonight. 30 questions in 8 minutes. I guess they want to know what is my IQ :/

  5. OMG, this would certainly have been one of those errors driving one crazy. Thank you so much for saving us all. Appreciate knowing that there is a privacy setting at all.

  6. BOOM! Another day, another video, and what do you know: another 2K subs!
    You’re getting closer, Mike! 🙂
    This Master Series MSPTDA is getting you to 500K subs and beyond.
    BTW: as long as I can get away with it: I go for option 1. But it is always good to know there is a more robust way too!

  7. Thank you!!!!! Thank you!!!! Thank you!!!!!! That firewall drove me crazy and I never could figure out the way around. I was trying to disable settings in Excel, not in the query itself. I will try your second solution as well

  8. Hi Mike; I have will living with the solving with the Privacy setting.

    2nd Solution is really Brilliant. Three Cheers!!

  9. very interesting! , and I post a comment about this problem in my LinkedIn referring to this video
    this is one of the best series you ever made *** five stars 🙂

  10. 2nd option is really great, but if we have more than 1 FolderPathInput( it has both folders and files ) linking to different query (i.e. source = FolderPathInput{0} in one query and in other it is source = FolderPathInput{1} and so on) then in that case how we could achieve using 2nd option?

  11. Both options do not work for me. I guess the C drive path is incorrect. Please advise me how to change it to our own drive. When I tried to use new source option, it wiped out all the applied step from the query called FolderPathInput.

    DataSource.NotFound: File or Folder: We couldn't find the folder 'C:UsersmgirvinDesktop09-ParameterQueryTables*'.

    Details:

    C:UsersmgirvinDesktop09-ParameterQueryTables

  12. I must admit, this hasn't all soaked into my head yet. I read the Puls and Webb blogs but it is going right over my head. At least your previous video is made whole now that this solution fixes the dreaded Formula.Firewall issue. That previous video is packed with a lot of stuff I didn't know so still trying to absorb it all. Thanks for such a through exploration into the M language.

  13. I use Power Query at work. I have a firewall query error in a report that I am trying to build in Power BI using 3 different data sources. One of the data sources located on a server outside of US. I can't wait to fix my errors using two solutions you presented. If I fix my errors I let you know which solution has worked!

Leave a Reply

Your email address will not be published. Required fields are marked *