The column of the table wasn't found (Excel Online in SharePoint)

[CoG_Analytics]

I have a dataset, saved as an excel spreadsheet, that I receive via email on a daily basis. I have a Power Automate Flow that creates that file in SharePoint, which is referenced by Power BI (the name of the file never changes, so I delete the old file and load in the new version). Every day, when Power BI refreshes that data, I get the following error:

The column 'Column1' of the table wasn't found.. Column1. . The exception was raised by the IDbCommand interface. Table: Table1.

My assumption was that the underlying report had changed and that field was removed. However, when I opened the file in SharePoint, the column was still present. I went back to Power BI, refreshed again. For some reason the column was now showing up again, and the error did not occur.

The column is the last column in the file for whatever that's worth although I've never seen an issue like this. Every day, I get the error, and the only thing I need to do to resolve the error is open the Excel Spreadsheet from SharePoint. Then I can refresh the data in Power BI and everything works as expected.

Has anyone else seen anything like this? The most common cause for an error like this is a change to the underlying data, which is kind of present since the file is being "overwritten" every day, but the data schema has not changed.

[danextian]

does your m code explicitly reference column1? is there column1 in your data? possibly when you initially connected, your data had column1 (power bi detects the used range and that column can refer to a column without a header).

[CoG_Analytics]

Thank you for your response. To answer your questions:

1. Yes, the column is explicitly and intentionally referenced in the M code 

2. Column1 is in my data (See the bit about needing to open the file in original post)

The problem is that Power BI is not seeing a column that does exist in my data, unless and until I open the file in question, at which point, I can refresh the data source and Power BI then sees the column it did not moments before.

[danextian]

So the data that's coming from Power Automate is supposed to include Column1 as well?