Power Query Formula.Firewall error

[cr731]

I'm having issues with the "Formula.Firewall" error in Power Query ("Formula.Firewall: Query 'Source_Query' (step 'Promoted Headers') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.") and simply cannot find the answer.

I have the following,

- A named range called Parameter_Source
- A query with the following code called SourceFile

let
    Source = Excel.CurrentWorkbook(){[Name="Parameters!Parameter_Source"]}[Content]{0}[Column1]
in
    Source

I then am creating a second query with the following:

let
    Source = Excel.Workbook(File.Contents(SourceFile)),
    page_Sheet = Source{[Item="page",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(page_Sheet)
in
    #"Promoted Headers"

This results in the firewall error.

What is the cause of this?

 

[anvg]

Hi
I have a similar problem in Excel 2016. I do not khow why it is happend. But if merge those queries in one it will work

let
    SourceFile =Excel.CurrentWorkbook(){[Name="Parameters!Parameter_Source"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(SourceFile)),
    page_Sheet = Source{[Item="page",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(page_Sheet)
in
    #"Promoted Headers"

Regards,

 

[ImkeF]

I also don't know why that is, but you can also solve it by enabling fast combine. Then the error-message will disappear.

 

[cr731]

Hi
I have a similar problem in Excel 2016. I do not khow why it is happend. But if merge those queries in one it will work

let
    SourceFile =Excel.CurrentWorkbook(){[Name="Parameters!Parameter_Source"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(SourceFile)),
    page_Sheet = Source{[Item="page",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(page_Sheet)
in
    #"Promoted Headers"

Regards,

Thanks - this worked. But it still makes no sense to me why it works this way.

 

[SimonNU]

This error is a pain in the butt and will regularly rear its ugly head. More info: Power Query Errors: Please Rebuild This Data CombinationThe Ken Puls (Excelguru) Blog

 

[cr731]

I was wondering if anyone can offer an explanation as far as what the various privacy levels actually do? What is the technical difference between them?

And, if everyone using my query has access to all of the data sources, is there any risk by just using fast combine and disabling the privacy settings altogether?