Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
gioris_g
Regular Visitor

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

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?

1 ACCEPTED SOLUTION
Anand24
Super User
Super User

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.

 

PBI_SuperUser_Rank@1x.png  

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

View solution in original post

4 REPLIES 4
pb1051
Advocate II
Advocate II

@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
Community Support
Community Support

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
Super User
Super User

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.

 

PBI_SuperUser_Rank@1x.png  

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

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors