Excel 365中向连接MS SQL的Power Query传递Excel表格参数时遭遇Formula.Firewall错误的技术求助

Let's break down why you're hitting this Formula.Firewall error and how to fix it quickly. The issue is that Power Query's security firewall blocks cross-query references between different data sources (your SQL Server and Excel workbook table). When your SQL query references a separate parameter query pulled from Excel, PQ sees this as mixing two isolated data contexts and throws the error.

Step-by-Step Solution #

The fix is to pull your Excel parameters directly into the same query as your SQL call, eliminating the cross-query reference. Here's how to adjust your code:

1. Replace your existing SQL_Query code with this revised version:

let
    // 1. Pull parameter values directly from your Excel table
    Param_Source = Excel.CurrentWorkbook(){[Name="ParameterTable"]}[Content],
    // Get the first row of your parameter table (adjust if you need to filter rows)
    Param_Row = Table.First(Param_Source),
    // 2. Format parameters to match SQL syntax (add quotes, fix date formatting)
    Start_Dte_Formatted = "'" & Text.From(Param_Row[START_DATE], "yyyy-MM-dd") & "'",
    End_Dte_Formatted = "'" & Text.From(Param_Row[END_DATE], "yyyy-MM-dd") & "'",
    Hyp_Formatted = "'" & Param_Row[HYP] & "'",
    // 3. Build your full SQL query with formatted parameters
    SQL_Query = "DECLARE @START_DATE AS DATE 
DECLARE @END_DATE AS DATE 
DECLARE @HYP AS VARCHAR(4) 
SET @START_DATE = convert(datetime, " & Start_Dte_Formatted & ",101) 
SET @END_DATE = convert(datetime, " & End_Dte_Formatted & ",101) 
SET @HYP = " & Hyp_Formatted & " 
-- = Query_Parameters('ParameterTable', 'Store') 
select case WHEN view1.STORE_NAME <> 'NULL' then view1.STORE_NAME ELSE view2.STORE end as [Store Name], 
case WHEN view1.Store_Street <> 'NULL' then view1.Store_Street ELSE view2.Store_Street end as [Store Street], 
case WHEN view1.Store_City <> 'NULL' then view1.Store_City ELSE view2.Store_City end as [Store City], 
case WHEN view1.Store_State <> 'NULL' then view1.Store_State ELSE view2.[Store State] end as [Store State], 
case WHEN view1.Store_zip <> 'NULL' then view1.Store_zip ELSE view2.Store_ZIP end as [Store ZIP], 
case WHEN view1.Store <> 'NULL' then view1.Store ELSE view2.Store end as [Store], 
case WHEN view1.ZIP_CODE <> 'NULL' then view1.ZIP_CODE ELSE view2.ZIP_CODE end as [ZIP Code], 
case WHEN view2.Distance ='[1-5 MILE]' then '[0-5 miles]' ELSE case WHEN view2.Distance ='[6-10 MILE]' then '[06-10 miles]' ELSE case WHEN view2.Distance ='[+50 MILE]' then '[50+ miles]' ELSE case WHEN view1.Distance <> 'NULL' then replace(view1.Distance,'MILES','miles') ELSE replace(view2.Distance,'MILES','miles') end end end end as Distance, 
case WHEN view1.Provider <> 'NULL' then view1.Provider ELSE view2.Provider end as Provider, 
isnull(view1.Location,'New / Old') as Location, 
case WHEN view1.[Lead Source] <> 'NULL' then view1.[Lead Source] ELSE '' end as [Lead Source], 
isnull(view1.[YEAR],'') as [Year], 
isnull(view1.[month],'') as [Month], 
isnull(view1.[Opps],'') as [Opps], 
isnull(view1.[Compass_Sales],'') as [Compass Sales], 
case WHEN view2.TC_Terrain='Yes' THEN 'Yes' ELSE 'No' END as [TC Terrain] 
from ( ----Opps & Sales by Provider and by ZIP --------- 
SELECT b.STORE_NAME, f.STREET1 as Store_Street, g.CITY as Store_City, g.STATE as Store_State, g.ZIP_CODE as Store_ZIP, b.STORE_STORE_ID as Store, a.CONTACT_ZIP as ZIP_CODE, a.DISTANCE as [Distance], a.media_name as [Provider], d.Location_name as [Location], ccategory_name as [Lead Source], YEAR(TRANSACTION_DATE) as [YEAR], MONTH(TRANSACTION_DATE) as [MONTH], SUM(Opportunities) as Opps, SUM(Compass_Sales) as Compass_Sales 
FROM [DB].[fact].[FACT_SALES_TRAFFIC_DETAIL] a 
left join db.dim.dim_store b with(nolock) on b.store_id = a.store_id 
left join db.dim.dim_lead_category c with(nolock)on a.SOURCE = ccategory 
left join db.dim.dim_Location d with(nolock)on a.Location_Key = d.Location_key 
left join db.dim.dim_market e with(nolock)on b.market_id = e.market_id 
left join DB.DIM.DIM_STORE f with(nolock) on b.STORE_STORE_ID = f.STORE_STORE_ID 
left join db.dim.dim_zipcode g with(nolock) on f.ZIP = g.ZIP_CODE 
WHERE b.store_Store_id in (@HYP) and a.Source = 'e' and TRANSACTION_DATE between @START_DATE and @END_DATE and a.MEDIA_NAME='TC' 
GROUP BY b.STORE_NAME, b.STORE_STORE_ID, d.Location_name, a.media_Name, ccategory_name, YEAR(TRANSACTION_DATE), MONTH(TRANSACTION_DATE), CONTACT_ZIP, a.DISTANCE, f.STREET1, g.CITY, g.ZIP_CODE, g.STATE) as view1 
full outer join ( ---True Car ZIP code Terrain------ 
SELECT b.STORE_name as STORE, b.STREET1 as [Store_Street], c.CITY as [Store_City], c.STATE as [Store State], b.ZIP as [Store_ZIP], INCLUDED_ZIP AS ZIP_CODE, DISTANCE AS Distance, 'TC' AS Provider, 'Yes' as TC_Terrain 
FROM BITESTDB.TIMLINM.TC_ZIPCODE a 
left join DB.DIM.DIM_STORE b with(nolock) on b.STORE_id = a.STORE_id 
left join db.dim.dim_zipcode c with(nolock) on c.ZIP_CODE= b.ZIP 
WHERE STORE_STORE_ID = @HYP AND INCLUDED_ZIP <> '') as view2 
on view1.ZIP_CODE = view2.zip_code 
ORDER BY Distance ",
    // 4. Connect to SQL Server and run the query
    Source = Sql.Database("www.com", "db", [Query=SQL_Query, MultiSubnetFailover=true])
in
    Source

Key Changes Explained #

• No more cross-query references: We're pulling the Excel parameter table directly into the SQL query's let block, so all steps live in the same data context.
• Parameter formatting: We wrap dates and strings in single quotes and use yyyy-MM-dd for dates (SQL Server recognizes this format reliably, avoiding conversion errors).
• Simplified workflow: You don't need a separate parameter query anymore—everything is self-contained in one query.

Quick Checks #

• Make sure your Excel table is named exactly ParameterTable (match the name in Excel.CurrentWorkbook(){[Name="ParameterTable"]}[Content]).
• Verify your table has columns named START_DATE, END_DATE, and HYP (adjust the column names in Param_Row[START_DATE] if yours are different).
• If your parameter table has multiple rows, replace Table.First(Param_Source) with Table.SelectRows(Param_Source, each [YourIdentifierColumn] = "TargetValue") to pick the correct row.

内容的提问来源于stack exchange，提问作者Gonzalez