Power Query - Not able to ingest data past 10 rows

[hamiltong]

Power Query, Data from Folder, Multiple excel 2k files with same data structure and multiple worksheets, every worksheet has 9 blank rows to start and the 10th row is the header data.   When Combining / Transforming or even just transforming it automatically recognized the 10th row as the header but only displays the fist 9-10 rows of data below.   I've tried remove blank rows - didn't have any affect.

Currently, I'm using a VBA script to remove the first 9 rows of each worksheet with the workbook and against all the file in the directory - than I can do a power query combine and transfor no problem with all the data.  

Wanted to have power query do it all 🙂 

[hamiltong · Accepted solution]

I figured it out.. I need to use the parameter InferSheetDimensions = true to get it to see the rest of the data..  Thanks. 

[ppm1]

You don't need the VBA. PQ can do it all. Please share the M code from the Advanced Editor transform sample file query (or just look for a step that is keeping only the 10 rows).

Pat