Missing Column error whenever a new Excel File is generated from an automated process

[Anonymous]

I have a Power BI report (in Power BI Desktop) that uses an Excel spreadsheet generated by an Automated Process as a datasource.

Every time the automated process runs to generate the input file, the Power BI report shows an error saying it couldn't find a specific column on the table. 

The problem resolves just by opening the file, hitting save, and then closing the file.

Has anyone come across such a problem? Any suggestions how i can resolve it?

[Anonymous]

Unfortunately, clearing the cache and restarting Power BI desktop did not solve the problem for me. 

The only workaround (manual) was to open the file, hit save, and then close the file. These steps solve the problem every time without fail. 

[Anonymous]

Hi @Anonymous 

Based on your descriptionif there is a step in your automation process to save a file every time you change data or add data, you may need to add a step to save a file and then refresh the file in your process.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SamWiseOwl]

Hi @Anonymous 

Does it error at a particular step in the Query Editor? Undefined data types could cause this.

The other thing to try is duplicate the table in the back end.

Delete the original.

Rename the copy to match the original.

Close and Apply.

Please Please Please make a back up of the PBIX file before attempting!!!!

[Anonymous]

Thanks for the response, @SamWiseOwl .

It errors out on hitting the Refresh button in Power BI after the new input file is generated from the automated process. This is how it looks like.

In Power Query, everything looks good till 'Promoted Head'. The error shows up in 'Changed Type'. Here is a screenhot.

[Anonymous]

Also, want to add that the manual workaround is simple; open the input file, hit save, and close the input file. The Power BI Report works seamlessly after that. This behaviour repeats everytime.

I am trying to figure out how to make it work in an automated setup without having to open and save the input file.

[SamWiseOwl]

I had a look on the forum and a few people have had a similar issue.

The accepted solutions recommend clearing the caches:
Solved: Column does not exist in the rowset error - Microsoft Fabric Community

"Sometimes this error is because a column was moved, renamed or removed in the original data source.  You should try to open PBI desktop, clear your cache.  Then, just to be paranoid about it.  Close Power BI and re-open it and open that file. "

Another suggests delete and rebulish:
Data Source Error - The column does not exist in t... - Microsoft Fabric Community