Dynamic Data Source

Power Query
1

Hi

I have an ODBC Data Source (AcctgDSN) that looks at one DB and requires credentials to be entered

In reality, I have 10 DBs – one for each Branch.

What I want to do is create an Excel template where in a named cell the user will enter the name of the DB for their Branch. Then when a query is run it looks to the DB named in the cell so that DB is the appropriate DB (Data Source) for that Branch selected.

Is this possible using parameters or will I need to use VBA or something else.
Any ideas how this could be done?

I have not included a file as I did not think it would be that useful. However the code for the Query is below

let
    Source = Odbc.DataSource("dsn=AcctgDSN", [HierarchicalNavigation=true]),
   FAASSET_Table = Source{[Name="FAASSET",Kind="Table"]}[Data]
in
    FAASSET_Table

Thanks

Allister

2

@AllisterB

First extract the database name from excel file(use drill down) and then pass that value to Original Source.

Here I’m dynamically change the excel file source base on the input entered in another excel file.

image
image1076×201 13.3 KB

Hope this helps

3

Hi @AllisterB, did the response provided by @Rajesh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

4

Hi @AllisterB, we’ve noticed that no response has been received from you since the 5th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

5

Hi @AllisterB, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!