A user has inserted a new column into an Excel dataset and this has caused my scheduled refresh in the service to stop working. No matter what I try I cannot seem to get this to resolve. The new column has been added to the table correctly within the Excel file, i have republished the report, I have deleted the dataset and republished the report and dataset and I have published into a different workspace but no matter what I try I am being told by the service that the new column does not exist in the table. Everything is working fine in pbi desktop
I am hoping I have missed something obvious here as this is driving me insane - its just a simple column addition and its caused havoc!!!
My assumption is that you would have added this new column in between which has caused the problem. If you would have added towards the end of the end of the column, this would not have happened. I have faced similar issues in the past and the way I had to resolve was to create the model entirely again or add the column towards the end.
Yes may be the fear might come true, I learnt it that ways only not to add any column in between after you have built your model . Or you may try using edit queries to add the column instead of adding that column to the excel. This might do the trick.
I don’t believe you have to totally rebuild a report.
I’ve never had to do this ever for this scenario and I add columns all the time.
This would be crazy if it was the case and would have the community up in arms. I’ve done a bit of searching myself and can’t seem to find a huge amount of others with the same situation here.
The reality is, if you can’t something in an excel sheet, all you should have to do is manage it in the query editor and make sure nothing is falling over there. Then in theory it should all flow nicely back to the online service etc.
If it really is not working, you should also be able just to republish from scratch and setup up the updates again I would have thought, without having to start anything from scratch on the Power Bi desktop side.
I’m interested to learn more if this persists and is an ongoing issue. I just can’t believe it’s to widespread here or Power BI team would be dealing with far more aggravated people.
A few questions to hopefully identify a path to a solution.
Have you tried deleting the scheduled refresh and then re-adding it?
Is the Excel data source local such that it gets refreshed in the service via a data gateway? If so, is there something that needs to be refreshed in the gateway itself?
Do you have any data transformation steps that affect columns after the one added, such that the M code executing the transformations got misaligned somehow?
Have you tried deleting the report itself in the service, then republishing it?
