Forum Discussion
Helper IQuery Parameter - Different columns between sources results in a failed refresh
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.
Super UserHi 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.
Helper IHey! 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).