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.