cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ozanboy Frequent Visitor
Frequent Visitor

Parameter Function ODBC - Navigate between tables

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"

3 REPLIES 3
Super User
Super User

Re: Parameter Function ODBC - Navigate between tables

@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"




Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




Highlighted
ozanboy Frequent Visitor
Frequent Visitor

Re: Parameter Function ODBC - Navigate between tables

@parry2k 

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

CaptureParameter1.JPG

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

CaptureParameter2.JPG

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.

CaptureParameter3.JPG

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

Super User
Super User

Re: Parameter Function ODBC - Navigate between tables

@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.





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.