Data extraction from specific rows of multiple workbooks using Power query

Reetesh

Board Regular
Joined
Sep 6, 2020
Messages
50
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
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

1622514926084.png
 

Reetesh

Board Regular
Joined
Sep 6, 2020
Messages
50
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Reetesh

Board Regular
Joined
Sep 6, 2020
Messages
50
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
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.

1622539992480.png
 

Reetesh

Board Regular
Joined
Sep 6, 2020
Messages
50
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,062
Messages
5,704,062
Members
421,326
Latest member
pfaustino

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top