How to use parameter for schema name

[cv77]

Hello,

Can anyone advise how to use a parameter for the schema name for tables using the import model?  I need to connect the Powerbi report to a database with different schema names for test and production.  I know how to create a parameter, but don't know how to use the parameter in the Model - Transform Data window to replace the existing hard-coded schema name in the tables' sources.  

Thanks

Carolyn

[amitchandak]

@cv77 , There is a blog on a similar topic, see if that can help

https://radacad.com/change-the-source-of-power-bi-datasets-dynamically-using-power-query-parameters

[cv77]

Thanks for the response.  That is the idea of what I need to do but doesn't address using a parameter in the schema for the table source.  I created a parameter to store the schema and then created a query to store the parameter value in, but haven't figured out the syntax to use that value in place of the hardcoded schema name.  

To clarify, in the Power Query editor. when I select one of my tables and under Applied Steps click Navigation, the current value is in this format  = SAMPLE_SCHEMA{[Name="MY_TABLE_NAME"]}[Data]

I want to replace SAMPLE_SCHEMA with the value from the parameter so that I can switch between test/prod schemas and between data sources where the schema name differs.   I have about 20 tables and would prefer to avoid having to set these hardcoded values, and don't want to maintain two powerbi reports.  The connection itself was easily parameterized, but I need to figure out the schema.

Does anyone have suggestions?  

[cv77 · Accepted solution]

I figured it out.  For each table I had to go into the Advanced Editor and replace the hardcoded schema name with the parameter: 

BEFORE:

let
Source = The.Database(connectionParam, [HierarchicalNavigation=true]),
SCHEMA_NAME= Source{[Schema='SCHEMA_NAME']}[Data],
TableName = 'SCHEMA_NAME'{[Name="TableName"]}[Data]
in
TableName

AFTER:

let
Source = The.Database(connectionParam, [HierarchicalNavigation=true]),
schemaParam= Source{[Schema=schemaParam]}[Data],
TableName = schemaParam{[Name="TableName"]}[Data]
in
TableName

This is working for me.