Help ... "Some" of our PCs are unable to connect to our SQL Server using Power Query functions

[ND_Pard]

Monday, May 15, 2023
Hi,

So far I have worked with 10 users spread across our state.  We all are using MS Office 365.  The Excel file has three (3) variables that are filled in by the user on the Excel worksheet prior to running a macro which then refreshes a Power Query.   The Power Query "Source" is a "SQL Server Database" and it's Advanced Editor refers to each of the variables via a function.  The variables are: Start_Date, Ending_Date, and Division.  I sent each of them the same file; 7 users had no problem running a macro that refreshes the Power Query, 3 users can only make the connection work if we hard-code the variables in the Power Query Advanced Editor.  I have tried two (2) methods of passing the variables to the Power Query's M Language; they both refer to functions.

The 1st method uses Named Ranges for each of the three variables as per the function: fGetNamedRange

https://www.howtoexcel.org/power-query/how-to-reference-a-named-range-in-power-query/

The 2nd method uses a Parameter table to refer to the three variables; the table has two (2) column headers:  Parameter and Value as per the function: fParameter

https://www.howtoexcel.org/power-query/how-to-parameterize-your-power-query/

Either of the above 2 methods work for the 7 users but not for the 3 users.

Obviously, the 3 users that the macro will not work for unless the variables are hard-coded into the M Language, can connect to the SQL Server's Database Table, so I know they have access to the data. 

We have compared their Excel File |  Options  | Trust Center settings and they are identical to the 7 users that the macro does run for without hard-coding the variables.

Any suggestions or tips will be appreciated.

Thanks in advance 🙂

[ND_Pard · Accepted solution]

It appears I stumbled on a solution ... 

I tried a couple of different things and so far have got 2 PCs working using the macros and variables referred to in the request for help.  I still have 2 PCs to work with (I found a 4th PC that it wasn't working on this morning).  I'll report back if the following doesn't resolve their problem with running the macro/query.

1.)  In the Advanced Editor, we commented out the lines using the functions and replaced it with a line with the variables hard-coded in the M Language.  The macro worked.  We went back into the Advanced editor and commented out the line with the hard-coding and and uncommented the original line using the function(s).  It ran successfully ... go figure???

2.)  In the Power Query, I clicked on "File", then "Options and settings", then "Data Source Settings".  I discovered a reference to the source shown as:  ServerName\Instance and also as:  ServerName.DomainName\Instance.
I high-lighted: ServerName\Instance and clicked "Clear Permissions" which removed it; leaving the: ServerName.DomainName\Instance.  I did this under the "Data sources in current workbook" and "Global Permissions".  The macro/query ran successfully.

3.)  In the Power Query, I clicked on "File", then "Options and settings", then "Query Options", then "Privacy".
I changed the "Privacy Levels" to: Always ignore Privacy Level settings.  The macro/query ran successfully.

I am not positive which of these steps was the solution, one them or all of them, but, bottom-line, the macro/query now runs successfully.

🙂