Missing last column in Power Query loading from .xlxs

[fog226]

Hello,

I have a Power BI report that is fed by a SharePoint xlsx file. The xlsx file is updated via a Power Automate flow that saves the new updated file directly replacing it on SharePoint. Once Power Automate runs a refresh, the Power BI connection becomes broken with this error message:

Expression.Error: The column 'Column10' of the table wasn't found.

Details:

Column10

Column 10 is the very last column of the xlsx file in SharePoint. When I visit SharePoint, that column is still in the xlsx file, yet for some reason Power Query cannot see it when it steps through.

let

Source = SharePoint.Files("SHAREPOINT_URL", [ApiVersion = 15]),

#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "SHAREPOINT_FOLDER_URL")), /* navigating to specific folder */

#"REPORT FILE xlsx_SHAREPOINT_FOLDER_URL" = #"Filtered Rows"{[Name="REPORT FILE.xlsx",#"Folder Path"="SHAREPOINT_FOLDER_URL"]}[Content],

#"Imported Excel Workbook" = Excel.Workbook(#"REPORT FILE xlsx_SHAREPOINT_FOLDER_URL"),

#"SHAREPOINT FILE_Sheet" = #"Imported Excel Workbook"{[Item="SHAREPOINT FILE",Kind="Sheet"]}[Data],

After this step is a promote headers step. The data type is then changed which triggers the error since column 10 isn't there. When you step backwards through the applied steps, you can visually see when you expand the "Table" by clicking the two arrows that point away from one another that column 10 is not in the SHAREPOINT FILE in the "Imported Excel Workbook" step.

The only workaround that I managed to find is to manually go to SHAREPOINT FILE and just save it again, but this obviously can't be automated.

Thank you for your help.

[lbendlin]

Instead of connecting to the sheet, define your actual table in Excel and then connect to it by table name.

[fog226]

Hello,

this solution resolves the issue initially, but my setup overwrites the previous file with new updates every day, causing me to lose the table definition. I plan to create a Power Automate flow to generate the Excel table while updating data sources.

Do you have any insights into why this sudden problem has arisen? Nothing has changed in my setup for months, yet the issue began just a few days ago.

Thank you!

[lbendlin]

Can you pinpoint the exact date? May have been caused by a service version change.