Excel Power Query - All rows not getting imported

[anidas]

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 : https://1drv.ms/f/s!AvlXw0Bv6UpPgagb-dARACmb4gqKeA

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

[#2]

@YWolf970 - you had a similar problem in this thread (https://techcommunity.microsoft.com/t5/excel/excel-power-query-wont-load-all-rows/m-p/1744695/thread...) but I didn't understand what the solution was.

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

[#3]

@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.

[#4 · Accepted solution]

@anidas 

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.

[#5]

@SergeiBaklan 

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 🙂

[#6]

@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?

[#7]

@anidas , you are welcome

[#8]

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).