Forum Discussion

emarc1's avatar
emarc1
Advocate II
5 years ago

Dynamic ODBC source selection in Power Query

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 s...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
emarc1
Advocate II
3 years ago

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.