Using Paramters to define ODBC connection

[simonb]

Hi,

Wondering if there's a way to use a parameter to define the ODBC Data source. I want to connect via CData's ODBC driver for Xero. This provides access to multiple tables via SQL statements. I have parameters for the ConnectionString, and the SQL filters. The SQL filter works fine, but if I swap in a parameter for the COnnectionString it reads the parameter reference as the ConnectionString and throws an error.

This works:

= Odbc.Query("dsn=BFA Sys", "Select * from TrialBalance where date=[PeriodEnding]")

would like it to be:

= Odbc.Query("dsn=[Connection]", "Select * from TrialBalance where date=[PeriodEnding]")

where [Connection] is a parameter returning the connection source, but this gives an error saying [Connection] is not a valid source.

Is there some syntax I'm missing?

Thanks!

[EwoutVisionBI]

Since this Power BI question is now in the Fabric community, how would one do this with a Fabric Dataflow Gen 2?

When I try, it works like above with a foreach-loop, but when publishing it fails with the error

Dynamic datasources are not supported

[parry2k]

@simonb I believe it would be like below, assuming your parameter name is Connection

= Odbc.Query("dsn=" & [Connection] & ", "Select * from TrialBalance where date=[PeriodEnding]")

[simonb]

@parry2k thanks,  that returns a Token Comma Expected error highlighting Select

 I've tried a few iterations but can't find one that works...any thoughts?

Thanks,

Simon

[parry2k]

@simonb sorry I had typo in my message, try this

= Odbc.Query("dsn=" & [Connection] & ", Select * from TrialBalance where date=[PeriodEnding]")

[simonb]

@parry2k 

That changes the error to:

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Sorry!

[klinejordan]

I think this will work:

= Odbc.Query("dsn=" & Connection & "", Select * from TrialBalance where date=[PeriodEnding]")