Forum Discussion
Dynamic Data Source based on parameter
Hello all,
Hopefully this is easier to explain that Im finding it is to do.
I have done work with parameters that place the SQL Server and Database names into the Source in the Advanced Editor.
Its working well and I feel comfortable in what its doing.
However I am scratching my head over syntax errors Im getting trying to do the following.
The data source for the Query may come form TableA or TableB.
A list of fields will be included that is slightly different in both tables so not as easy as passing the parameters in the Table section of the Select * From <<Table>> area.
I would like to have a parameter that is effectively a Yes/No question.
If the answer is a Yes, then I would like the "Source =" line in advanced editor to be the full source line for TableA
If the answer is a No, then I would like the "Source =" line in advanced editor to be the full source line for TableB
In concept its simply
Source = If parameter = yes then Sql.Database(.............TableA else Sql.Database(.............TableB
Im just really struggling with the syntax and the old Token Comma expect is often arising.
Thanks in advance for time taken to read or reply
shaunwilks,
Here is an example for you.let #"Table 1" = (para as text) => if para = "test1" then let Source = Sql.Database("servername", "test",[Query="select * from Main"]) in Source else let Source = Excel.Workbook(File.Contents("path\Book1.xlsx"), null, true), #"Security public - Copy_Sheet" = Source{[Item="Security public - Copy",Kind="Sheet"]}[Data] in #"Security public - Copy_Sheet" in #"Table 1"
Regards,
Lydia
7 Replies
shaunwilks,
I am not very clear about your logic, could you please post a screenshot about the code in your Advanced Editor? How do you create parameter in Query Editor?
In your scenario, why not create another table name parameter?
Regards,
LydiaSorry if I was not clear enough but you are on the right track in response.
The data source is not as simple as the Table name, Sql server name or database name.
It could be excel or SQL, it could be a different sheet name in different excel or different table in a different SQL database.
I wanted to present the 3 or 4 options in a plain english list of options in the "Parameter" and then handle the data source inside the M code.
So for simplicity sake the parameter would read Option 1, Option 2, Option 3,Option 4....
I wanted the M code to read something like
If Parameter = Option1 Then Source = Excel.Workbook(File.Contents("C:\Data.xlsx"), null, true) else
If Parameter = Option2 Then Source = Excel.Workbook(File.Contents("F:\Database.xlsx"), null, true) else
If Parameter = Option3 Then Source = Sql.Database(SQLServer, AdventureWorks, [Query="SELECT * FROM Data", CreateNavigationProperties=false]) else
Sql.Database(SQLServer99, AdventureWorks, [Query="SELECT * FROM DataView", CreateNavigationProperties=false])
Continually having syntax issues trying to achieve it and was hoping a sample "If" statement could be provided that changes the data source property in the Advanced Query Editor
shaunwilks,
Here is an example for you.let #"Table 1" = (para as text) => if para = "test1" then let Source = Sql.Database("servername", "test",[Query="select * from Main"]) in Source else let Source = Excel.Workbook(File.Contents("path\Book1.xlsx"), null, true), #"Security public - Copy_Sheet" = Source{[Item="Security public - Copy",Kind="Sheet"]}[Data] in #"Security public - Copy_Sheet" in #"Table 1"
Regards,
Lydia
thanks for sharing this great idea!!! 😛
