powerbi online refresh fails after colum is removed even though column is not referenced in model

[gioris_g]

I have a model online tha connects to a postgres view that is beeing build by end users.

the view changes often
new columns are beeing added and columns are beeing removed.

I connect to the view with a native query like this

= Value.NativeQuery(PostgreSQL.Database(db_host, db_schema),"select * from "&db_name&".View name ", null, [EnableFolding=false])
I don't do any other transformation on power query (for example referencing any column on a later step)

the problem is that whenever a column from the view is deleted the online refresh of the model breaks
with the following error
Data source error: The 'COLUMN NAME ' column does not exist in the rowset.
even though the specific column is not referenced anywhere.

on the desktop power bi , if i hit refresh the error is not replicated and the desktop version does the refresh normaly.
so every time something changes on the view I have to open desktop version update localy and push new versiion online


i tried with different connection method 
like ODBC and 
let
    Source = PostgreSQL.Database("xxxxx", "xxxx", [CreateNavigationProperties=false]),
    a= Source{[Schema="public",Item="xxxxxxxx"]}[Data]
in
    a


but i get the same problem 

any ideas on how to address  this?

[pb1051]

@Anand24 I understand what you are saying, but let's say you have 20 reports connected to a single dataflow that has one column removed. You then have to open 20 pbix files, refresh and republish. How could this be solved to be handled from the service, either now or in the future?

[v-cazheng-msft]

Hi, @gioris_g 

If the column isn't deleted before Apply, the metadata and data(Import mode) of this column will be loaded into the dataset. Even the filed related with this column isn't used by any visual or calculation, it actually quoted by the dataset. It will be refreshed when refreshing dataset. If you want to delete it from the dataset, you need to update the dataset in your Power BI Desktop and then republish it to Service.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anand24 · Accepted solution]

Hi @gioris_g ,

When a dataset refreshes in services, it looks our for all the columns irrespective of whether they are referenced in the report in any applied step or even used in any visualization. Dataset refresh in services doesn't have the capability to validate if the existing column is used somewhere or not. This is the reason why it will throw error in services refresh.

Addition of a column to existing model doesn't throw this error because Power BI is sure that it is not referenced since this is a new column and its impossible for it to be referenced earlier.

If you have removed column from back-end, then you can probably download the dataset from services, refresh in desktop and then re-publish to services. I guess this should resolve issue.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[NotMyJob]

No, it doesn't as I'm working with Desktop solely.  I see no reason why if you remove a column from PBI, and that column does not exist in the data source file, that an error should be thrown.

Likewise, when the Table view lists all the columns that are are in the data source file (Excel) and an error gets thrown, that's just sloppy programming and horrible QA.

If there's somewhere else that this removed column is hidden that I have to remove as well, I've yet to find it.