SOLVED

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

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

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

@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 :)

 

anidas_0-1640098379459.png

 

@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).
1 best response

Accepted Solutions
best response confirmed by anidas (Copper Contributor)
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.

View solution in original post