Dynamic ODBC source selection in Power Query

[emarc1]

Hi.

I know how to parametise dsn names in Power Query, so they can be switched easier... I'm wondering if more is possible.

I have an Excel file that is used by multiple people and I'd like to to set Power Query up so it can automatically select the correct dsn name, depending on the system that the file has been opened on. Are any of these possible within Power Query ODBC query?:

• Ping ODBC data sources to see if they are active, without needing credentials to be entered first.
• Pull down a full list of the system's ODBC data sources.
• See the current PC name or username.

Or does anyone have any other suggestions as to how this might be possible?

[Mokshi]

@emarc1 ok thanks for your response

[Mokshi]

@emarc1  I had tried it , it doesnt work
sorry one query is from demo2 dsn and the other query is from demo3

one query is successfully working while the other gives an error ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
 I dono which and where did i miss

[emarc1]

Check it works when you manually change it. Maybe the DSN isn't set up properly in your ODBC driver. Other than that, I'm not sure I can help as I'm really not an expert either! Try making a new thread for it and someone more experienced may be able to help.

[Mokshi]

@emarc1  I have connected my data through odbc connection
I wanted to created a dynamic data source in powerbi
I have created my parameter name as flower and dev

one of my database editor looks like this:

let
Source = Odbc.DataSource("dsn=demo2", [HierarchicalNavigation=true]),
flower_Database = Source{[Name="flower",Kind="Database"]}[Data],
orders_Table = flower_Database{[Name="orders",Kind="Table"]}[Data]
in
orders_Table

and the other database advance editor looks like this:

let
Source = Odbc.DataSource("dsn=demo2", [HierarchicalNavigation=true]),
dev_Database = Source{[Name="dev",Kind="Database"]}[Data],
orders_Table = dev_Database{[Name="orders",Kind="Table"]}[Data]
in
orders_Table

and now I'm applying my parameter name in flowerdatabase:

let
Source = Odbc.DataSource("dsn="&Parameter&"", [HierarchicalNavigation=true]),
flower_Database = Source{[Name="flower",Kind="Database"]}[Data],
orders_Table = flower_Database{[Name="orders",Kind="Table"]}[Data]
in
orders_Table

it works with one database when i switch to other database it comes as

ODBC: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

i have created on system dsn only
kindly help me

[emarc1]

I think that would hint that you don't have a correct dsn set as a value in the parameter. In both of your original queries, the dsn is "demo2", so I'd expect something like that there. You said you made the parameter names, "flower" and "dev", but looking at your code, I wonder if you mean the values of "Parameter" to those options. As you've put your parameter there, wouldn't that mean it's putting the "flower"/"dev" in place of this? If you want to flip it from one query to the other, you might want to insert the parameter in all the places through the query where it currently says "flower" or "dev". You'd probably want to just put it after "Name=" on the second line, and remove it from the other parts.

I'm not sure if it'll run into privacy setting issues as the query changes, or if you intend for it to be used on other computers.

[Mokshi]

@emarc1 can u pls explain that how to parametise dsn names in Power Query , i want create a dynamic datasource in powerbi with parameters connected to odbc connection

[emarc1]

In PowerQuery, Add a new parameter (via 'Manage Parameters' on the Home ribbon) and then reference it in your queries. We could modify this to have a parametised DSN name:

Source = Odbc.Query("dsn=DSN_NAME", "select * from SCHEMA_NAME.TABLE_NAME")

If I make a parameter for selecting between several schema names, I can then modify the query to use the parameter text:

Source = Odbc.Query("dsn="&PARAMETER_NAME&"", "select * from SCHEMA_NAME.TABLE_NAME")

I used this in an Excel file and wanted it to dynamically select the correct DSN name for a user's computer, but it was more hassle with privacy permissions than it was worth. Generally, I'd have thought for Power BI, you wouldn't want to have it switch DSNs...  Unless you're not publishing the files to the web service I suppose.

[emarc1]

I don't think that solution isn't applicable here; it seems more useful for use on the Power BI online service.

My problem related to Power Query, purely within Power BI Desktop or Excel, and how to automatically adjust DSN (ODBC driver) selection, depending on which computer the pbix/xlsx file was on (or to query all the possibilities and use the one that works). Not sure why I didn't put this into the Power Query sub-forum though, sorry.

I did manage to obtain a username by looking at what user folders were on the system, but it was just as much of a pain with privacy settings, so I dropped it.

[V-lianl-msft]

Hi @emarc1 ,

Please check whether the preview feature can help you solve the problem:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.