How to import complete data from the files in a folder?

Question

I have a bunch of excel files in a folder, those i want to combine and transform. thus I imported them through Power Query. But while importing it, i got only first 30 rows of data, whereas the files contain data upto 10000 rows. it happens with each and every file. In case I open and save the data, then complete set of data can be downloaded. I tried many methods available online, but none work.

Answer 1

Hi Kashief khan,

The issue you're encountering in Power Query could be related to how the Excel files are being read by Power Query, especially if it's only fetching the first 30 rows. Here are some troubleshooting steps to resolve this problem:

1. Check the Preview Rows Setting:

Power Query might initially show only a preview of the data. You can change this setting to display the full data.

• After loading the data into Power Query, check at the bottom of the window where it says "30 rows." Click on it and see if there’s an option to view more rows or "Load more data."
• If this works, the issue is just in the preview, and the full data will load during transformation.

2. Check Data Load Settings:

Power Query might be truncating the data. You can check the data load settings:

• Go to Home > Data Load > Query Options in Power Query.
• Look for the section related to loading and ensure that there's no limit set on the number of rows being previewed or loaded.

3. Disable Background Refresh:

Sometimes Power Query loads partial data due to background refresh settings:

• Right-click on the query in the "Queries & Connections" pane in Excel.
• Select Properties.
• Uncheck the option for Enable background refresh.

4. Ensure Excel Files are Saved Properly:

Since you mentioned that opening and saving the files resolves the issue, it suggests the files may not be fully "unlocked" for Power Query. Consider automating the process of opening and saving the files:

• Macro approach: You can create a simple Excel VBA macro to open and save the files automatically before importing them into Power Query.
• Format Consistency: Make sure the files are saved in a consistent format (like .xlsx) and that no external links or pivot tables in the original files are causing issues.

5. File Size Limit:

If the files are large or if your system's resources are constrained, Power Query might not be importing all rows.

• Ensure that there are no file size or row number restrictions in place in Power Query or Excel.

6. Check for Errors in Power Query:

Sometimes, errors during import (like problematic rows) can stop Power Query from loading more rows. After importing the data, check the error messages, if any.

I use the AI to help me to help you