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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
fog226
Regular Visitor

Missing last column in Power Query loading from .xlxs

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.

3 REPLIES 3
lbendlin
Super User
Super User

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

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!

 

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

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors