Home

Excel Power query question

%3CLINGO-SUB%20id%3D%22lingo-sub-1076164%22%20slang%3D%22en-US%22%3EExcel%20Power%20query%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1076164%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20written%20a%20query%20to%20pull%203%20columns%20from%20several%20workbooks%20all%20housed%20in%20a%20folder.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethere%20are%20dozens%20of%20workbooks%20all%20with%20the%20same%20format%20and%20layout.%26nbsp%3B%20Each%20workbook%20has%20mutiple%20tabs%20(all%20identical)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20managed%20to%20select%20all%20the%20tabs%20I%20want%20to%20see%20in%20to%20the%20query%2C%20I%20have%20also%20managed%20toget%20the%20columns%20I%20want%20from%20each%20of%20the%20selected%20tabs..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20bit%20Im%20missing%20is%20I%20only%20want%20row%204%20from%20each%20tab%2C%20in%20each%20worksheet..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eusing%20the%20%22keep%20range%20of%20rows%22%20and%20selecting%20row%204%20as%20first%20row%20and%200%20for%20number%20of%20rows%20only%20gives%20me%20row%204%20from%20the%20first%20workbook%3F%20IE%20a%20single%20row%20is%20returned%20from%20the%20query..%20I%20have%20around%2075%20workbooks%20with%206%20worksheets%20in%20each%20that%20I%20am%20trying%20to%20return..%20so%20expecting%2075*6%3D450%20results...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20want%20to%20know%20how%20to%20only%20return%20row%204%20from%20each%20tab%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20reference%20I%20followed%20this%20guide%20to%20getto%20the%20point%20im%20at..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.excel-university.com%2Fretrieve-values-from-many-workbooks%2F%3Funapproved%3D7160%26amp%3Bmoderation-hash%3Da7806e00b467b5e4abf7eca4eab0e962%23comment-7160%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.excel-university.com%2Fretrieve-values-from-many-workbooks%2F%3Funapproved%3D7160%26amp%3Bmoderation-hash%3Da7806e00b467b5e4abf7eca4eab0e962%23comment-7160%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eany%20help%20much%20appreciated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJ%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1076164%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

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

 

https://www.excel-university.com/retrieve-values-from-many-workbooks/?unapproved=7160&moderation-has...

 

any help much appreciated

 

thanks 

 

J

1 Reply
Highlighted

@JamesBowdidge 

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.

Related Conversations
excel formula
loriam1225 in Excel on
1 Replies
Process the big data
yoepls in Excel on
1 Replies
Row height
yorkiebasie in Excel on
1 Replies
Load the Analysis ToolPak plugin in Excel
Daniel10 in Excel on
0 Replies
Cant understand why i cant apply formula
Kwaker in Excel on
10 Replies
Getting data from web
PeterG380 in Excel on
0 Replies