Dec 20 2019 03:12 AM
Hi,
I have written a query to pull 3 columns from several workbooks all housed in a folder.
there are dozens of workbooks all with the same format and layout. Each workbook has mutiple tabs (all identical)
I have managed to select all the tabs I want to see in to the query, I have also managed toget the columns I want from each of the selected tabs..
the bit Im missing is I only want row 4 from each tab, in each worksheet..
using the "keep range of rows" and selecting row 4 as first row and 0 for number of rows only gives me row 4 from the first workbook? IE a single row is returned from the query.. I have around 75 workbooks with 6 worksheets in each that I am trying to return.. so expecting 75*6=450 results...
I just want to know how to only return row 4 from each tab?
for reference I followed this guide to getto the point im at..
any help much appreciated
thanks
J
Dec 20 2019 12:29 PM
On the step where you have filename, sheetnames and sheet.data.columns, merge first two into one column; group by it using Advanced and All Rows in aggregation. After that add to each resulting table index as described here https://www.excelguru.ca/blog/2018/06/27/number-rows-by-group-using-power-query/. Expand resulting column and filter it on Index=4. As result it will be 4th row of each sheet for each file.