Forum Discussion
Query Parameter - Different columns between sources results in a failed refresh
Hey!
Within our company we want to start using Dev/Test/Production in our Power BI, within their respective workspace. Therefore we now have 3 workspaces with a Deployement Pipeline running between them.
I suggested to use Query Paramaters to dynamically switch between data sources (production/test etc), depending on the workspace. This seemed to work great, untill I tried to refresh the Power BI in the service enviroment. It gave me an error stating certain columns were not found in the source.
This is true: Our test has, for testing purposes, more columns that we use and might implement for production.
I noticed however that If I want to refresh the dataset in Production, that has a visual build with test data (not available in production) It gives me an error stating the columns do not exist.
What I expect is that it will refresh, but show errors in the report, like it does in Power BI desktop. It refreshes just fine there.
My question:
Can I not use Query Parameters between sources that are not identical, if I have build a visual using one of the columns that does not exist in other sources?
If so, are there any workarounds?
Love to hear it,
Daniël
Hi Foxxon28
What you’re seeing is expected behavior: in the Power BI Service the dataset refresh is schema-validated, so if a column referenced anywhere in the model (Power Query, relationships, measures, or visuals) does not exist in the current source, the entire refresh fails, unlike Desktop which is more forgiving at design time. Query parameters only swap the connection — they do not abstract schema differences — so Dev/Test/Prod must be structurally identical if you want pipelines to work reliably. The only safe workaround is to standardize the schema by exposing views in each environment that always return the same columns (add NULL or default values for missing columns in Prod), or use Power Query to add missing columns with nulls before loading. If the schemas aren’t aligned, deployment pipelines + parameters will keep breaking in the Service.
7 Replies
For semantic model refresh in the Service, your “switched” sources must end up with the same schema (same column names + types for anything that gets loaded into the model). If your PROD source doesn’t have a column that exists in TEST, the refresh will fail because the model metadata still expects that column. For example, if TEST has extra columns, you need to add them as NULL in PROD so both environments return identical columns.
As an another alternative you could directly work on PROD in which I assuma you have all the columns here, and then publish it on DEV after changing the server to DEV. Then you can work with a proper CI/CD environment.
Hello Foxxon28
Workarounds
Here are two practical options:1. Align Schemas Across Environments
- Ensure that Test, Dev, and Production have the same column structure, even if some columns are unused in Production.
- You can add placeholder columns in Production (e.g., ALTER TABLE ADD [NewColumn] NULL) so the schema matches.
2. Use Power Query Conditional Logic
- Instead of relying on Query Parameters alone, add logic to handle missing columns:
let Source = Sql.Database(ParameterServer, ParameterDB), Table = Source{[Schema="dbo",Item="MyTable"]}[Data], Adjusted = Table // Add missing columns if they do not exist & if Table.ColumnNames(Table) <> {"ExtraColumn"} then Table.AddColumn(Table, "ExtraColumn", each null) else Table in AdjustedOfficial References:If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
Hi Foxxon28
What you’re seeing is expected behavior: in the Power BI Service the dataset refresh is schema-validated, so if a column referenced anywhere in the model (Power Query, relationships, measures, or visuals) does not exist in the current source, the entire refresh fails, unlike Desktop which is more forgiving at design time. Query parameters only swap the connection — they do not abstract schema differences — so Dev/Test/Prod must be structurally identical if you want pipelines to work reliably. The only safe workaround is to standardize the schema by exposing views in each environment that always return the same columns (add NULL or default values for missing columns in Prod), or use Power Query to add missing columns with nulls before loading. If the schemas aren’t aligned, deployment pipelines + parameters will keep breaking in the Service.
Hi Foxxon28,
Have you tried making the model originally on the prod connection so that the schema only contains columns that exist in prod, and then changing it to the dev/test connection?
Power BI will try to refresh all columns it has defined, so columns disapearing will cause issues, but I don't think columns appearing should cause any problems.
Alternatively if your source is a database, you could write a select statement that only pulls in the columns you need/have in production and use that as the source query.Hey! Thanks for the swift reply.
Whilst that probably works, it seems a bit counterproductive. How am I ever supposed to test new data changes if I can only test it with the production data. Where tables from both sources have to be the exact same layout.
Writing a select statement probably works, but I want to flexibility to have more / fewer columns. Thats what the testing phase is for.
You say "Power BI will try to refresh all columns it has defined" - what exactly does "defined" mean in this context? It seems to fail the refresh if I've built a visual using columns that no longer exist in production. Does the making of the visual "Define" the column?
How is that different to Power BI desktop, as it gives me no errors there (except for an empty error table).
Hi Foxxon28 , Thank you for reaching out to the Microsoft Community Forum.
We find the answer shared by Zanqueta & cengizhanarslan is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.
Thank you Zanqueta & cengizhanarslan for your valuable response.
hI Foxxon28 , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.
