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

    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.