Power Query Problems

How to Fix Missing Dates When Combining Excel Workbooks with Get Data

Huda Qurayshi Oct 8, 2026 869 views

Question details

Missing or zero-value dates appear when combining multiple monthly Excel workbooks.

Fix Missing Dates When Combining Excel Workbooks with Get Data

Product: Excel
Device & OS: not provided
Scenario: Consolidating twelve monthly employee summary worksheets that contain formula-driven dates using the Excel Get Data feature.
Observed behavior: Specific formula-calculated date values, such as sick-leave and bereavement dates, import as blank or zero, while other dates and day counts import correctly.

Before you start

Ensure all your source workbooks are fully saved and closed, and verify that all date formulas in the original files have completely calculated before initiating the data import.

Solution 1Recommended

Standardize Data Types and Clean Formulas in Power Query Editor

Use the Power Query Editor to enforce consistent date formats across all source columns and replace any unexpected zero values to ensure accurate data consolidation.

When Excel's Get Data feature imports multiple workbooks, variations in column data types or uncalculated formulas can cause specific cells to return blank or zero values. Formatting these columns strictly as Dates within Power Query resolves most inconsistency issues.

Launch Power Query Editor

Open your master Excel workbook, go to the Data tab, click on Get Data, and select your source workbooks. Instead of clicking Load immediately, choose Transform Data to open the Power Query Editor.

Enforce Date Formatting

Locate the specific columns containing the missing sick-leave or bereavement dates. Click the data type icon (usually displaying 'ABC' or '123') in the column header and select Date or Date/Time from the dropdown menu.

Replace Zero Values

If errors or '0' values still appear in the date columns, right-click the affected column header, select Replace Values, and replace '0' with 'null' or an appropriate blank value to keep the data clean.

Load the Cleaned Data

Once all date columns are properly formatted, click the Close & Load button in the top-left corner of the Home tab to import the consolidated, accurate data into your Excel workbook.

Standardize Data Types and Clean Formulas in Power Query Editor

Check Source Formulas: Power Query imports the stored values of cells. If a workbook was saved with formulas set to 'Manual Calculation', Power Query might pull outdated blanks or zeros instead of the actual dates.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Management

If you frequently encounter data import errors and formula calculation issues when combining workbooks in Excel, consider switching to WPS Office. It provides a free, lightweight, and highly compatible spreadsheet solution featuring a familiar user interface, making seamless migration easy while handling complex data without unexpected formatting glitches.

Fully compatible with Microsoft Excel (.xlsx, .xls) formats and formulas.Lightweight and fast, preventing system freezes when handling multiple monthly workbooks simultaneously.Intuitive data consolidation tools designed for easily combining multiple sheets without complex query setups.Completely free to use with a familiar user interface, requiring zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my Excel formulas import as zeros or blanks in Power Query?

Power Query extracts the raw data stored in the file at the time of the last save. If the source workbook was saved before formulas finished calculating, or if it relies on closed external links, Power Query will pull those unresolved blanks or zeros instead of the expected dates.

How do I ensure Power Query recognizes a column as dates automatically?

To force date recognition, always ensure the original Excel columns are formatted as Dates before importing. Inside the Power Query Editor, you can manually override the detection by clicking the data type icon on the column header and selecting 'Date' to apply uniform formatting.

Can I combine multiple worksheets from different workbooks without using Power Query?

Yes. You can use the traditional 'Consolidate' feature found under the Data tab, utilize VBA macros, or leverage the built-in sheet combination tools available in alternative software like WPS Spreadsheet to merge data sets without relying on the Power Query engine.

Excel & Data Problems