Power Query Problems

Fix Power BI Power Query Cannot Find the Last Excel Column

Algirdas Jasaitis Oct 1, 2026 869 views

Question details

Power BI Power Query fails to load the final column of an Excel workbook stored in SharePoint after it gets overwritten by a Power Automate flow.

How to Fix Power BI Power Query Missing the Last Excel Column

Product: Power BI / Microsoft Excel
Device & OS: not provided
Scenario: Importing or refreshing an Excel workbook from SharePoint into Power BI after it has been automated or overwritten by Power Automate.
Observed behavior: The final column of the Excel dataset is missing in the Power Query Editor because the internal worksheet metadata does not reflect the newly used range.

Before you start

Ensure you have access to the Power BI Desktop application and the necessary permissions to edit dataset queries within the Power Query Editor.

Solution 1Recommended

Use InferSheetDimensions in the Advanced Editor

Update the M query code to force Power Query to read the actual worksheet dimensions instead of relying on cached file metadata.

When an Excel file is overwritten automatically via Power Automate, Excel's internal metadata for the used range often fails to update correctly. This causes Power Query to miss newly added columns at the end of the sheet.

Adding the InferSheetDimensions parameter bypasses this corrupted metadata, forcing the engine to read the actual data grid and ensuring all columns are detected.

Open Power Query Editor

In Power BI Desktop, click on 'Transform Data' in the Home ribbon to open the Power Query Editor window.

Access the Advanced Editor

Select the affected query from the left-hand pane. Navigate to the 'Home' tab and click on 'Advanced Editor'.

Locate the Excel.Workbook Step

Find the line of code that connects to the file, which usually begins with Excel.Workbook(Source, null, ...).

Add the Detection Parameters

Update the step to include [DelayTypes = true, InferSheetDimensions = true]. The modified code should look similar to: Excel.Workbook(Source, null, [DelayTypes = true, InferSheetDimensions = true]).

Apply Your Changes

Click 'Done' to close the Advanced Editor. The missing column should now appear in the preview. Click 'Close & Apply' to update your data model.

Use InferSheetDimensions in the Advanced Editor

Performance Tip: While InferSheetDimensions safely resolves missing data, it may slightly increase data refresh times for very large Excel files as it scans the entire worksheet.

Free Microsoft Office alternative

Edit and Manage Spreadsheets Seamlessly with WPS Office

If you frequently encounter metadata and file structure issues with standard Office workflows, managing your spreadsheets in a lightweight, reliable alternative can save you time. WPS Office provides full compatibility with Excel formats and a smooth experience without complex background errors.

1. Download and Install: Visit the WPS Office website to download the free suite and install it on your device.
2. Open Your Data: Launch WPS Spreadsheets and seamlessly open your existing .xlsx files without conversion.
3. Save with Confidence: Edit and save your files safely, ensuring accurate data ranges and clean metadata for downstream processing.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Lightweight architecture minimizes file bloat and metadata corruptionFamiliar spreadsheet interface ensures zero learning curveCompletely free to use for daily data editing and reporting tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Automate cause Excel columns to go missing in Power BI?

When Power Automate overwrites a file in SharePoint, it doesn't trigger Excel's internal recalculation of the 'used range'. Consequently, Power Query reads the outdated metadata and fails to see the newly appended last column.

What exactly does InferSheetDimensions do in M code?

The InferSheetDimensions parameter commands Power Query to actively scan the rows and columns of the worksheet to determine its actual dimensions, rather than trusting the potentially inaccurate metadata saved within the file.

Can I apply InferSheetDimensions to multiple queries at once?

No, you must manually update the M code in the Advanced Editor for each individual query that connects to an affected Excel workbook. For frequent use, consider wrapping the connection step in a custom function.

Does InferSheetDimensions slow down the Power BI refresh process?

It can cause a slight delay. Because the parameter requires the engine to evaluate the physical boundaries of the spreadsheet data rather than just reading a metadata tag, large datasets might take slightly longer to process.

Excel & Data Problems