Use PBI Template with parameter for different database??

[MojoGene]

Not sure if this is possbile with parameters and PBI templates.

Assume that we have Company A and Company B both of which are using a sales database with identical structure (same tables, fields, etc.), but the data in each of the databases are unique to each Company. Company A's datebase is named = Sql.Databases("CompanyA") and Company B's database is named = Sql.Databases("CompanyB").

Can a template be used to connect to either/both of the databases and if so how would I created the list of Accepted Values that would prompt a user which database to connect to?

Thanks.

[v-alq-msft · Accepted solution]

Hi, @MojoGene 

I'd like to suggest you create three parameters as below.

Then you may click 'Advanced Editor' and parameterize the data source. I use SQL Server to test.

Then you need to save it as a template file(pbit file).

When you open the pbit file, you may input the corresponding parameter to connect to the specific table.

For further information, I'd like to suggest you refer the following links.

Create report templates for Power BI Desktop 

Deep Dive into Query Parameters and Power BI Templates 

Power BI Desktop Query Parameters 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Best way to do this would be to make a parameter in the query editor to hold your database name(s).  You would then use this parameter name in your query.  If you want single select scenario and the user picks (parameters can only hold a single value), you would have a query that queries the database for that one value.  If you want the multiple option (all listed databased are queried, and user can pick desired database(s) from a slicer), you would make a table with the list of database names and then make a custom function that accepts same parameter but the database name for that row as input.  The results would all be appended, and you would still have the database name column for slicing.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[MojoGene]

Pat:

Thanks for jumping in. 

I could create a list with the database names for each Company. That seems simple enough. But how do I pass the selected list value as the Source for each table in the database?

[mahoneypat]

Create a custom function in query editor and in the query with the table with the list of database names, on the Add tab choose Invoke Custom Function, choosing the column with your database names as the input paramter.  See this post for instructions.

https://www.poweredsolutions.co/2019/02/19/parameters-and-functions-in-power-bi-power-query-custom-f...

To create the function, make a query that does what you want with one of the database, and then modify the query in advanced editor, replacing the database name with the name of your parameter.  In example below, the parameter name is in parentheses.  The values from your table of names is inserted there when you invoke custom function.

(databasename) =>

Source = sql.database(actualservername, databasename, ...)

If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat