Power Query Problems

How to Identify Faulty Rows When Importing CSV Data in Power Query

John Wilson Oct 23, 2026 868 views

Question details

The user needs to locate specific rows that trigger errors during a CSV data import in Power Query, particularly when the file contains duration fields longer than 24 hours.

How to Identify Faulty Rows When Importing CSV Data in Power Query

Product: Microsoft Excel (Power Query)
Device & OS: not provided
Scenario: Importing CSV data containing time duration fields that exceed 24 hours into a spreadsheet using Power Query.
Observed behavior: The import process reports errors on certain records but does not clearly identify the invalid rows, causing data transformation failures.

Before you start

Ensure you have the latest version of your spreadsheet software installed and create a backup copy of your original CSV file before applying any data transformations.

Solution 1Recommended

Use the 'Keep Errors' Feature to Isolate Faulty Rows

Power Query has built-in error handling that allows you to filter and view only the rows containing data type mismatches or parsing errors.

This is the most efficient native method to pinpoint exact rows causing import issues, provided the error occurs during a data type conversion step.

Open Power Query Editor

Load your CSV data and open the Power Query Editor to view your applied steps.

Select Keep Errors

Navigate to the 'Home' tab on the ribbon, click on 'Keep Rows', and select 'Keep Errors' from the dropdown menu.

Inspect Error Details

Look at the resulting table. Click the whitespace inside the 'Error' cell (do not click the word 'Error' itself) to view the specific error details in the bottom pane.

Use the 'Keep Errors' Feature to Isolate Faulty Rows

Empty Error List: If filtering for errors returns an empty list, the error might be occurring in a subsequent step. Ensure you apply 'Keep Errors' immediately after the 'Changed Type' step.

Solution 2

Import Duration Fields Exceeding 24 Hours as Text

Standard time data types often cannot handle durations over 24 hours. Changing the column data type to text prevents these import errors entirely.

Solution 3

Manually Isolate Errors Using the Split Method

If Power Query's interface fails to display the errors, you can manually locate the problematic data by systematically halving the source file.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Imports and CSV Handling

If you frequently encounter complex Power Query errors in Microsoft Excel, consider switching to WPS Office. WPS Spreadsheet offers a highly compatible, lightweight, and user-friendly interface for importing and managing CSV files without the steep learning curve.

1. Download WPS Office: Visit the official website to download and install WPS Office for free.
2. Open WPS Spreadsheet: Launch WPS Spreadsheet and navigate to the 'Data' tab on the top ribbon.
3. Import Your CSV: Click 'Import Data' to easily load your CSV file using the built-in, error-free import wizard.

Fully compatible with Microsoft Excel file formats, including .xlsx, .xls, and .csv.Lightweight and fast, easily handling large CSV imports without freezing or requiring complex query setups.Intuitive Data tab with straightforward text-to-columns and import wizards to handle duration data correctly.Familiar user interface ensuring seamless migration from Microsoft Office.Completely free to use for everyday spreadsheet tasks.

Frequently Asked Questions

Why does Power Query show an error for durations longer than 24 hours?

Power Query often maps time fields to a standard Date/Time or Time data type, which is strictly designed for clock time. When a value exceeds 24 hours (e.g., 25:00:00), it falls out of standard clock bounds and triggers a parsing error. Importing these values as text bypasses this restriction.

What should I do if 'Keep Errors' returns an empty table?

An empty error table usually means the error is happening at the data source level or in a later transformation step. Check the 'Applied Steps' pane on the right side of the Power Query Editor and ensure you are clicking 'Keep Errors' immediately after the specific 'Changed Type' step that introduced the failure.

Can I parse a >24-hour duration text string back into a usable number?

Yes. Once you import the duration as text, you can use the 'Split Column' feature by delimiter (colon) to separate the text into hours, minutes, and seconds. From there, you can create a Custom Column formula to multiply and add these values into a total decimal hour or minute format.

Excel & Data Problems