Parameter Function ODBC - Navigate between tables

[ozanboy]

Hi all,

In our SQL database every study create another table with different name(ID). With everynew table I have to follow same steps in query editor in order to shape them suitable for my graphs/charts etc. I want to create a parameter which can navigate between tables in our database and apply already created query steps automatically.

But I could not able to create a parameter/function which can navigate between tables.

Below is advanced query editor script. My study id is 20190409001 in this table. I want to create parameter for this id and change it any number I want. For example 20190409005.

Thanks in advance

let
Source = Odbc.DataSource("dsn=XX Database", [HierarchicalNavigation=true]),
Company_Database = Source{[Name="Company",Kind="Database"]}[Data],
#"20190409001_Table" = Company_Database{[Name="20190409001",Kind="Table"]}[Data]
in
#"20190409001_Table"

[parry2k]

@ozanboy you can achieve this by parameter, create new parameter in power query, let's say we call it TableName. 

now update your script as below.

let
Source = Odbc.DataSource("dsn=XX Database", [HierarchicalNavigation=true]),
Company_Database = Source{[Name="Company",Kind="Database"]}[Data],
#"20190409001_Table" = Company_Database{[Name=TableName,Kind="Table"]}[Data]
in
#"20190409001_Table"

[ozanboy]

@parry2k 

I've created parameter named TableName and changed script as you said. but I got below error in query editor after apply changes.

And when I click on edit settings it directs me to navigation window,

Below all the tables in our database this image appears, it seems like it doesnot recognize TableName.

Then I also created function as TableName like below;

(TableName)=>
let
Source = Odbc.DataSource("dsn=XX Database", [HierarchicalNavigation=true]),
Company_Database = Source{[Name="Company",Kind="Database"]}[Data],
#"20190409001_Table" = Company_Database{[Name=TableName,Kind="Table"]}[Data]
in
#"20190409001_Table"

But got an again error message below and the advanced editor script of my error is like below.

let
    Source = #"20190409001"(20190409003)
in
    Source

Second one is the table id I want to navigate, first one is the default table id.

Maybe somehow changing all the table ids in initial script with TableName?

Thanks

[parry2k]

@ozanboy the change I proposed in the original query should work. Can you make sure name of the parameter you used in the query is exact match of parameter name, it is case sensitive.

Also make sure the value in parameter is not blank and you enter a valid table name. I don't expect you need to make much changes except change proposed in original reply.