CODEWITHSUNDEEP
c#sql-serverexceldynamics-navpowerquery
AronChan
AronChan

Reputation: 245

How do I change the connection string to SQL Server in Excel Power query programmatically?

I'm attempting to create an Excel pivot table based on data in a Microsoft Dynamics NAV database.

The trick is I need the Excel to access the data directly from the SQL Server database with power query - and furthermore it must be able to access the data from the same table in multiple databases with different names and table names.

Does anybody have any experience or advice regarding this issue?

Upvotes: 2

Views: 1278

Answers (2)

S M
S M

Reputation: 81

Step 1. First you should make a function where you can pass a server name, database name and table to be queried. Something like

let getData =(servername,dbname,tablename)=>
let
Source = Sql.Database(Servername, dbname, [Query="select  abc , def  from" & tablename & " where condition etc etc"]),
#"CustomStep1" = some action on Source,
in
.
.
#"CustomStepn" = some action on Added CustomStepn-1
in
#"Added CustomStepn"
in  
getData

You have a function ready which you can use in a table to create a custom column.

Step 2. Now use a parameter table approach. Create a table in normal excel area.Something like 

Server Name|DatabaseName | Table_to_be_used

Use now use menu option fromtable in powerquery options (or Data tab in Excel 16) Add a custom column in this table in powerquery steps using function getdata created in previous Step. Perform any other "Expand" (By default first function is going to return a table if you are not doing any other transformation) , "summarize" , Rename Operation.

However powerquery formula firewall is going to give you hardtime as powerquery doesn't trust Native SQL queries and you will have to approve each and every native sql query. You may try to uncheck checkbox for "Require user approval for new native database query" in query option.

Hope you get the idea and it helps.

Upvotes: 2

Jake Edwards
Jake Edwards

Reputation: 1217

Perhaps it would be worth looking into creating Query objects and exposing them via oData which is something Excel can read from. The benefit here is that it can handle table relations natively and can expose Flow Fields which you cannot see in direct SQL queries to the table.

Aside from a stored procedure to manage the different table names, there's not a simple way to query specific tables without hard coding the names in some capacity.

The Company table will give you the prefix$ and the table names are static between companies. You could write some fancy Excel logic to loop through them.

Upvotes: 1

Related Questions