Forum Discussion
Excel Power Query - All rows not getting imported
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.
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.
- 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-id/76949) but I didn't understand what the solution was.
SergeiBaklan and NikolinoDE - Any help on this matter would be greatly appreciated.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.
- 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).
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.
- 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?
