power query excel file unrecognized column

[GeekAlfPro]

Hello,

here is my issue

i load an excel file stored on sharepoint.

this file has 19 columns

until a week ago, loading worked fine all my columns were read by PQ

But since, i need to open first manually the file and add a space for example, save and reload to fetch my 19th column.

The trouble is that my dashboard is automated and i can't open the daily file manually
how can i fix that issue ?

Thanks for any help.

[GeekAlfPro]

Hello,

Thanks @v-kpoloju-msft i asked my HR IT for a csv export to make the flow more reliable

[v-kpoloju-msft · Accepted solution]

Hi @GeekAlfPro,
Thank you for reaching out to the Microsoft fabric community forum.

It sounds like Power Query is not consistently picking up the last (19th) column from your Excel file on SharePoint. This kind of issue often happens when the file is not fully saved, or its structure isn’t properly committed especially if it's generated or uploaded automatically. You mentioned that manually opening the file and saving it fixes the issue, which suggests Power Query might not be reading the sheet's full content by default.

Here are a couple of things you can try to avoid needing to open the file manually. In Power Query, when connecting to the file, use this formula:

Excel.Workbook(File, true, false)

That last false tells Power Query not to guess the layout and just read everything as-is. It usually helps bring in all the columns, including the ones that might otherwise be missed. Alternatively, if you can change how the file is saved or exported, try saving it as a CSV instead of Excel. Power Query handles CSVs more reliably and won’t miss the last column.

If the file is coming from another system, make sure it’s properly saved and uploaded before Power Query tries to load it. If you'd like, I can help review your M code to apply these changes.

Also, kindly please check this official Microsoft Document for better understanding:
Excel.Workbook - PowerQuery M | Microsoft Learn

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.