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

 

[Tom88]

I appreciate the response but from what I can gather the solution is to not to use a stored procedure with parameters and filter the data by joining the queries after they are loaded into power query. That works but not efficient because it brings in a lot of extra data. I ended up doing that but I got rid of the parameters coming from Excel and just put a slicer on a pivot table so they could filter the dates. It's so inefficient though because I'm loading 15 years of data when 90+% of the time they only want to look at the most recent year.

I was thinking of another use case. Right now each grant administrator downloads reports for all their faculty from SAP and sends them out one by one. I was also thinking of using that same model so that a central administrator could download all the data and send out reports filtered for each faculty grant portfolio. I was playing around with loading a parameter based on the file or sheet name and then passing that to SQL Server. Each faculty could just hit refresh when we load the central table. Now we are talking over 100 users. It would save so much time. If I download it all I could filter but I don't think we want to send out a file with all the data. What if a faculty member went into power query now they can see other faculty grants.

I think I'll try changing the setting in VBA. Sure it will set off alarm bells but easier to tell 100+ people to just click okay/ignore rather than walking them through settings.

 

[Tom88]

Okay nice try VBA won't work
"

Why VBA Can't Modify Privacy Settings Directly​

Power Query enforces privacy settings to protect sensitive data and prevent unauthorized cross-data source information leaks. VBA doesn't expose APIs for adjusting privacy levels programmatically because:

• It would bypass Excel's security model.
• It could unintentionally compromise sensitive data."

If it just prompted like it used to then that would be fine. I could tell people to switch the prompt to "Ignore". Thank God I didn't do this before and then MS changes the behavior and now I'd be stuck every time there is a new person. I guess I could use an Access database to let the administrator manage parameters.

I think I've officially given up on this!