references other queries or steps, so it may not directly access a data source.

[itsmollertime]

I have a function that has been working correctly for multiple years. Without any changes, I am now getting the dreaded, "references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.". I have checked privacy settings and everything is aligned. 

I have a function called Function_GetValue that looks like this:

(rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

 It is used in the "Renamed Columns" step below. This is the end of about 30 steps, which don't error until the renamed columns step.

#"Replaced Errors1" = Table.ReplaceErrorValues(#"Added JIRA plus Roadmap Dec", {{"JIRA Plus Roadmap Desc", ""}}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Errors1",{{"1", Function_GetValue("Pos_1")},{"2", Function_GetValue("Pos_2")},{"3", Function_GetValue("Pos_3")},{"4", Function_GetValue("Pos_4")},{"5", Function_GetValue("Pos_5")},{"6", Function_GetValue("Pos_6")},{"7", Function_GetValue("Pos_7")},{"8", Function_GetValue("Pos_8")},{"9", Function_GetValue("Pos_9")},{"10", Function_GetValue("Pos_10")},{"11", Function_GetValue("Pos_11")},{"12", Function_GetValue("Pos_12")},{"13", Function_GetValue("Pos_13")},{"14", Function_GetValue("Pos_14")},{"15", Function_GetValue("Pos_15")},{"16", Function_GetValue("Pos_16")},{"17", Function_GetValue("Pos_17")},{"18", Function_GetValue("Pos_18")},{"19", Function_GetValue("Pos_19")},{"20", Function_GetValue("Pos_20")},{"21", Function_GetValue("Pos_21")},{"22", Function_GetValue("Pos_22")},{"23", Function_GetValue("Pos_23")},{"24", Function_GetValue("Pos_24")},{"25", Function_GetValue("Team1")},{"26", Function_GetValue("Team2")},{"27", Function_GetValue("Team3")},{"28", Function_GetValue("Team4")},{"29", Function_GetValue("Team5")},{"30", Function_GetValue("Team6")},{"31", Function_GetValue("Team7")},{"32", Function_GetValue("Team8")},{"33", Function_GetValue("Team9")},{"34", Function_GetValue("Team10")},{"35", Function_GetValue("Team11")},{"36", Function_GetValue("Team12")},{"37", Function_GetValue("Team13")},{"38", Function_GetValue("Team14")},{"39", Function_GetValue("Team15")},{"40", Function_GetValue("Team16")},{"41", Function_GetValue("Team17")},{"42", Function_GetValue("Team18")},{"43", Function_GetValue("Team19")},{"44", Function_GetValue("Team20")},{"45", Function_GetValue("Team21")},{"46", Function_GetValue("Team22")},{"47", Function_GetValue("Team23")},{"48", Function_GetValue("Team24")},{"49", Function_GetValue("Team25")},{"50", Function_GetValue("Team26")},{"51", Function_GetValue("Team27")},{"52", Function_GetValue("Team28")},{"53", Function_GetValue("Team29")},{"54", Function_GetValue("Team30")},{"55", Function_GetValue("Team31")},{"56", Function_GetValue("Team32")},{"57", Function_GetValue("Team33")},{"58", Function_GetValue("Team34")},{"59", Function_GetValue("Team35")},{"60", Function_GetValue("Team36")},{"61", Function_GetValue("Team37")},{"62", Function_GetValue("Team38")},{"63", Function_GetValue("Team39")},{"64", Function_GetValue("Team40")},{"65", Function_GetValue("Team41")},{"66", Function_GetValue("Team42")},{"67", Function_GetValue("Team43")},{"68", Function_GetValue("Team44")},{"69", Function_GetValue("Team45")},{"70", Function_GetValue("Team46")},{"71", Function_GetValue("Team47")},{"72", Function_GetValue("Team48")},{"73", Function_GetValue("Team49")},{"74", Function_GetValue("Team50")}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each true)
in
    #"Filtered Rows"

When it runs, I get the following error: Formula.Firewall: Query 'Portfolio_All_Dept_Primary' (step 'Filtered Rows') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

I watched Chris Webb's video on this topic, but can't seem to figure out where I am going wrong. Any help is very appreciated!

[itsmollertime · Accepted solution]

Okay, weird scenario, but I figured out the issue. I had a PC refresh at work and when installing Office I inadvertently used by person O365 account versus my corporate account. I would have never gotten this from the error, but when changing to my corporate account, the query started functioning correctly without the firewall error. Man, it's always the little things that get you!

[rubayatyasmin]

@itsmollertime 

are you trying to combine protected and unprotected data at the same time? If so, then split the queries. 

https://community.fabric.microsoft.com/t5/Power-Query/Formula-Firewall-Error-when-Iterating-through-...

refer; How to fix the Formula.Firewall error in Power Query (2 ways) (exceloffthegrid.com)

*allow native DB query

*set the data privacy level of your current workbook. 

https://community.fabric.microsoft.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-so...

[itsmollertime]

As I stated initially, all privacy settings check out (all are set to public, no mismatches). 

The getvalue function that I created is grabbing values from named cells (Defined Names) in my workbook.

In the source spreadsheet, I have column headers where the values can shift often. If I use hardcoded values for those column names, such as "Team Bronx" in PowerQuery, I end up having to modify my query every time a header changes in Excel. Therefore, I have the team names stored in separate worksheet and replace the values "Pos_1" with the friendly name after I have completed all of my steps in power query.

For example, A1 in my reference worksheet is assigned the defined name Pos_1. I am using the function to pull back the value in that cell to be used as the column name in Power Query. This is the code to perform that function in the editor: {{"1", Function_GetValue("Pos_1")}. So instead of the output of my query giving me

-----------

| 1 | 2 | 3 |

-----------

It returns

---------------------------------------------

| Team Bronx | Team Cypher | Team Rose|

---------------------------------------------

As stated, this query has been untouched for at least 6 months or more and has been working without error. The excel spreadsheet is stored on a Sharepoint server (not sure if there could have been a change on the server to impact privacy?). 

Here is the Query Dependencies output for this function:

In Query Options --> Global --> Privacy is set to "Combine data according to your Privacly Level settings for each source

In Query Options --> Current Workbook --> Privacy is set to "Combine data sccording to your Privacy Level setting for each source"

In Data Source Settings I have two Data Sources in the workbook:

1. Current Workbook

2. SQL Server Database hosted at company

Based on the references you provided and what I have read/viewd in Chris Webb's videos, I am not sure where else to check. Of course, this problem goes away if I set Privacy Levels to "Always Ignore Privacy Level settings", which I don't want to do.