Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
@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
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |