Excel Power Query - All rows not getting imported

Copper Contributor

I am trying to import data from a folder which has multiple excel (.xlsx) files. Each file is in the same format and has more than 100 rows. However, when trying to merge & import the file, only the first 29 rows of the files get imported. So, if I have 10 files, my combined file should actually have more than 1,000 rows of data but I have only 290. The rest of the rows don't get imported.


I am using Office 365. The link to the folder containing all the files and the combiner file which contains the query is here :!AvlXw0Bv6UpPgagb-dARACmb4gqKeA


Please help. This was working properly until last month and now I don't know why this has gone rogue.

7 Replies
@YWolf970 - you had a similar problem in this thread ( but I didn't understand what the solution was.

@Sergei Baklan and @NikolinoDE - Any help on this matter would be greatly appreciated.

@anidas Had a look at your query and underlying files. Downloaded two of them and connect to them in a dedicated Folder, and returned 200 rows back to Excel. See screenshot. So, there doesn't seem to be a problem with the query.

Screenshot 2021-12-21 at 13.46.07.png

best response confirmed by anidas (Copper Contributor)


In Transform Sample File query please change first step as


//    Source = Excel.Workbook(Parameter1, null, true),
    Source = Excel.Workbook(Parameter1, [UseHeaders = false, DelayTypes = true, InferSheetDimensions = true], null),


With InferSheetDimensions = true PQ reads actual sheet dimension, not one which is in metadata.

@Sergei Baklan 


Worked like a charm. Thanks a lot. :) You are a life saver :)


Although one small change - I had to make the change in the "Transform File" option and not the "Transform Sample File". But thank you once again :)




@Riny_van_Eekelen - Firstly, thank you for looking into this.

Secondly, I have all these workbooks saved on the cloud in my onedrive. If I save them in my HDD and not on the cloud, will that actually resolve the issue?

@anidas , you are welcome

Thank you Sergei, this solved my problem as well. (I was looking for like button but couldn't find one so I thank you in this way).