Excel Parameterized PowerQuery With Null Handling

Question

I'm encountering an error when attempting to execute a parameterized SQL query from Excel using parameter values (StartDate and EndDate) stored in a worksheet. When the EndDate parameter is left blank in the worksheet, I would like to utilize the current date and time as a default value. Instead, I am getting a strange error message, despite the query being generated by Excel appearing valid. I created a basic example to demonstrate this issue, and found it to be a repeatable problem.

Steps to Reproduce:

Create a new Excel worksheet
Create a new table named Params with the following values:

Create a new SQL Query (Data -> Get Data -> From Database -> From SQL Server Database)
Enter a basic query like the following (note: for the server and database I used a known-good SQL Server 2014 database that I have used for many other similar queries - I am confident that this is not the source of the problems) at press OK:

Press the Edit button on the next screen. Go to the Advanced Editor and enter the following query, which should replace an empty EndDate parameter with the current local time:

let
    Params = Excel.CurrentWorkbook(){[Name="Params"]}[Content]{0},
    StartDate = Params[#"StartDate"],
    EndDate = if Params[#"EndDate"] is null then DateTime.LocalNow() else Params[#"EndDate"],
    Source = Sql.Database("N0049MF1", "Allmine", 
        [Query="SELECT '" 
            & DateTime.ToText(StartDate) & "' AS StartDate, '" 
            & DateTime.ToText(EndDate) & "' AS EndDate"])
in
    Source

Press Done to return to the Query Editor. The following banner will appear:

Pressing the "Edit Permissions" button brings up a dialog asking to set a Privacy Level - I used "Organizational.
Clicking the Save button shows the following dialog. You can see that the parameters were properly added to the query. Press Run to continue:

At this point, the query worked properly, and returned the following results:

Next, I tried deleting the date in the EndDate cell of the Params table and refreshing the query. This time, when the Native Database Query permissions dialog appeared, the EndDate was properly set to the current date and time, indicating that the M-code worked properly:

Upon pressing the Run button, the dialog disappears, but immediately reappears, with no error message. Pressure the Run button repeatedly dismisses and the dialog, with it reappearing quickly each time.
Pressing the Cancel button shows the following error:

[Permission Error] EvaluateNativeQueryUnpermitted failure: the query 'SELECT '1/1/2018 12:00:00 AM' AS StartDate, '11/8/2018 5:07:56 PM' AS EndDate' isn't approved for execution.

Conclusions

The query appears to be valid, and permissions appear to be set correctly. Changing the EndDate back to a valid date causes the query to resume working properly, but deleting the date causes it to fail again. Using the SQL Server profiler, I can see that the query is successfully executed when both the StartDate and EndDate parameters are present, but is never even sent to the database if the EndDate parameter is blank.

Is this a truly a permission issue? A problem with the M code? A bug in Excel?

Alexis Olson Alexis Olson · Accepted Answer · 2018-11-08T23:02:01

I can't reproduce the problem (the query works for me), but I'm pretty sure it is actually a permission issue.

See if turning off privacy leveling fixes it.

Excel Parameterized PowerQuery With Null Handling

1 Answers