Data extraction from specific rows of multiple workbooks using Power query

[Reetesh]

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]

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]

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

View attachment 39769

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]

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]

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

View attachment 39769

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]

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]

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]

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