Calling stored procedure using parameters from Excel table

[Tom88]

When I try to call a stored procedure using parameters from an Excel table I get a cross query error. The only way around this is to go into query settings and set privacy to ignore. I've done this before and I would be prompted by a dialog to change the privacy setting which was fine. Now it doesn't prompt. Which means if I share this solution with anyone in my group then I have to have them all go into power query to change the setting. This is not workable. Anyone else run into this and find a workaround? Am I just not doing this correctly?

Here is the Let statement bringing in the parameters from excel
let
Source = Excel.CurrentWorkbook(){[Name="ParameterTable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}})
in
#"Changed Type"

and the one calling the stored proc
let
// Reference the Parameters query
ParameterQuery = Parameters,

// Extract the first value from each column (assuming one row in the table)
StartDt = Text.From(ParameterQuery[StartDate]{0}), // Convert date to text
EndDt = Text.From(ParameterQuery[EndDate]{0}), // Convert date to text

// Build the SQL query dynamically
SQLQuery = "
EXEC [BudgetDashboard].[OverTheCap]
@StartDt = '" & StartDt & "',
@EndDt = '" & EndDt & "'",

// Execute the query on SQL Server
Source = Sql.Database("IST-MSSQL", "SPH_Budgets", [Query=SQLQuery])
in
Source

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

As stated change the privacy to ignore and it works fine. If it prompted that would be fine too.

 

[Matt Allington]

It’s very annoying and I have never understood it fully despite working exclusively in this space for 11 years. There are some blogs out there on how to do it and from memory, there are a few different causes that can trigger the error. I tried to understand but frankly it’s just easier to turn off the settings you mentioned. These settings are in the client, not the file. This is why they need to be set on every PC.

In your case, it sounds like you need a fix. My mate Ken has blogged about it a bit. I think this is what you need Power Query Errors: Please Rebuild This Data Combination

He has a few articles on the topic that you can find here references other queries or steps site:excelguru.ca - Google Search