Power Query not detecting last column in xlsx files

[VicCC]

Hi 

For the past fortnight, Power Query has failed to detect the last column in the Excel source files (xlsx) which underpin our Power BI dashboards.

I have not been able to solve this in Power BI e.g. by rebuilding the queries, reloading the source files etc.

The source Excel files typically have a blank first column, which has not historically posed an issue as all columns have been detected. If I delete this blank column in the original file - or even delete it and then reinsert a blank column - Power Query recognises the last column again.

Is this is known issue and is there a solution that does not involve modifying the source files?

Thanks in advance

[Anonymous · Accepted solution]

Try adding the InferSheetDimensions parameter, like

Excel.Workbook(File.Contents("C:\YourFileName.xlsx"), [DelayTypes = true, InferSheetDimensions = true])

This reads the content of the sheet, instead of the (possibly no longer correct) metadata info, to get your data.

--Nate

[VicCC]

Nate, superb. This seems to provide the fix I'm looking for. Thank you!