Pass Parameters from Excel sheet to Power Query from MS SQL

Question

I am using Excel 365. I have a Power Query (PQ) that reads off a MS SQL database and I need to set parameters for that query from a table in the same Excel workbook.

I created the parameters in PQ, when I feed them manually the query works But when I link those parameters to my Excel table get the following error message

Formula.Firewall: Query 'SQL_Query' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

I changed the security settings but it did not work.

I've seen several posts that work but in instances where both data sources come directly only from Excel.

let
    Source = Sql.Database("www.com", "db", [Query="
    DECLARE @START_DATE AS DATE
    DECLARE @END_DATE AS DATE
    DECLARE @HYP AS VARCHAR(4)
    SET @START_DATE     = convert(datetime, " & Start_Dte  & ",101) 
  
    SET @END_DATE   = convert(datetime, " & End_Dte  & ",101) 
    
     SET @HYP  = " & Hyp & "  --  = 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.STORE_STORE_ID 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 
      

      ", MultiSubnetFailover=true])
in
    Source

Answer 1

The easy way: open Power Query, then File > Options > CURRENT FILE > Privacy > Ignore