Different Schema in Testing and Production Database

[santhiya]

Hi Community !!!

Our Testing Database has columns that are not in Production Database . When Connected with Deployment Pipelines we are facing column not found error . Is there any way to overcome this situation in Power BI while loading itself
Note. We are not able to add new columns to Production Database

Thanks

[santhiya]

Thanks @tackytechtom , As I am not familiar with Power Query , can you please explain how this can be done with source query . Any articles related to this can help me to sort out . Thanks 

[tackytechtom · Accepted solution]

Hi @santhiya ,

So the idea is to already specify in your source query which of the columns you'd like to use. There are plenty of ways to do this, i.e. as  explained before you create a view so you establish the query definition in the database. Otherwise, you could of course also just specify the columns you want in your query by writing an sql statement, for instance. 

Here a source that might help you:

Import data from a database using native database query - Power Query | Microsoft Learn

If this does not help, please provide a screenshot of your M Power Query statement (advanced editor)

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[santhiya]

Thanks @tackytechtom 

[tackytechtom]

Hi @santhiya ,

I suppose you are not using the spare columns in your report, right? I'd suggest to exclude these columns before importing them to Power BI. You can do this in the source query in Power Query. Another option, which I usually go for is to create views in the source database acting as a contract for data consumers like Power BI. It would be the same concept, though. Use only the columns within the view that are both apparent in test and prod.

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/