Forum Discussion
Dynamic Data Source based on parameter
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
Sorry 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
This is an amazing solution!
I am building a template for users that may decide to use SQL or Excel as their data source and this is much more elegant than maintaining 2 versions of the same file (one for each data source).
I took this solution one step further by referencing an already defined parameter to determine which data source to use. So, when the user first opens the Power BI file as a template, they are presented with a "Data Source" parameter option where they can select either "SQL" or "Excel" from the list. The table is then populated with the appropriate source based on this choice.
I'm still working out the best solution for handling connection parameters (SQL requires Server, Database and Table parameters, but Excel only requires a path parameter) but there are multiple solutions to explore there.
Here's a code example:
if #"Data Source Type" = "SQL" then let Source = Sql.Database(#"SQL Server Name", #"SQL Database Name", [Query="SELECT * FROM [dbo].[view_" & #"Table Name" & "]"]), #"Extracted Date" = Table.TransformColumns(Source,{{"Date", DateTime.Date, type date}}) in #"Extracted Date" else let Source = Excel.Workbook(File.Contents(ExcelDataFile), null, true), FactTable_Sheet = Source{[Item="FactTable",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(FactTable_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Value", Int64.Type}, {"StageIndex", Int64.Type}, {"Date", type date}, {"Version", Int64.Type}, {"OsBuildRelease", type text}, {"DeviceFamily", type text}, {"FlightRing", type text}}) in #"Changed Type"Thanks for Sharing Very Usefull Idea
Thanks so much Lydia.
I was tackling it a little different to that but your way is nice and clean.
