# Data extraction from specific rows of multiple workbooks using Power query



## Reetesh (May 30, 2021)

Hello All.

I've recently come across the power query feature in excel 2016 where in i got to know that i can extract data from multiple workbooks in a folder with actually having to open all those workbooks.
Now my question is, not sure whether it's possible or not, Can power query be used to extract specific rows of data from multiple workbooks?

For example if i want to extract the details only from the last two rows of all the workbooks in the folder, can this be done with power query?

The last 2 rows will be different in all the workbooks from which I'm extracting the data.

I've no idea on how power query works, as I'm still in the initial learning phase. So thats why posting my doubt here.

Thanks in advance.


----------



## Alex Blakenburg (May 31, 2021)

In principle it should be fine.

Data > Get Date > From File > From Folder
To get the workbooks
Home > Keep Rows > Keep Bottom Rows > enter no of rows
to get the last 2 rows


----------



## Reetesh (May 31, 2021)

Alex Blakenburg said:


> In principle it should be fine.
> 
> Data > Get Date > From File > From Folder
> To get the workbooks
> ...


Hello Alex, 
Thanks a lot for your help. I will definitely give it a try.

There is one more thing i wanted to ask if you don't mind. Again I'm not sure whether it can be done or not!!

Will be possible for me to change the positioning of the rows as well in power query?

I'll give an example for better understanding.

Since the power query will extract the last 2 rows from all the workbooks, suppose we call the rows as "Row 10" and "Row  11". I want positioning of the rows to be as "Row 11" and "row 10". The very last row, which is row 11 in this scenario, should appear on the top and after that Row 10.

Thanks in advance mate


----------



## Alex Blakenburg (May 31, 2021)

That should also be fine.

When you load from a folder, it creates a sample file query and the combine files query
Go to the sample file query and add an index column
Sort it descending
This will flow through to the combine query so each workbooks last 2 rows will appear as the last one first and the 2nd Last one last.


----------



## Reetesh (Jun 1, 2021)

Alex Blakenburg said:


> In principle it should be fine.
> 
> Data > Get Date > From File > From Folder
> To get the workbooks
> ...


Hello Alex, I tried using as you mentioned above.
However, instead of taking the last 2 rows from all the workbooks in the folder its only displaying the last 2 rows of only one workbook (the last workbook in the folder).


----------



## Alex Blakenburg (Jun 1, 2021)

When you import from a folder you get 2 lots of queries.
Anything you want done individually to each file before combining the files together is done to the sample file.
If you do it after you combine the files (Other Queries) you will only get the last 2 rows of the combined list.


----------



## Reetesh (Jun 1, 2021)

Alex Blakenburg said:


> When you import from a folder you get 2 lots of queries.
> Anything you want done individually to each file before combining the files together is done to the sample file.
> If you do it after you combine the files (Other Queries) you will only get the last 2 rows of the combined list.
> 
> View attachment 39795


I was not aware of that... Thank you so much for your help Alex.
Appreciated.

Its all working now as I want it to.


----------



## Alex Blakenburg (Jun 1, 2021)

Thanks for letting me know. Glad I was able to help.


----------

