How to get specific rows from multiple excel files

[fahadarshad]

Hello everyone

I have multiple excel files, with multiple excel sheets. What I intend to do is to go in every sheet of every excel file in a particular folder and only pick out the rows from those sheets that are between certain cells with certain contents.

For example:

excelfile1 has sheet1 where in row 17 there is a cell with content that says "List of shops" and then the subsequent rows have data about shops. Once the list of shops is finished the next cell reads "list ends here". each sheet in each excel can have any number of shops in the list. some might have 4, others might have 14. but i only need the row data from each sheet in each excel that is between those cells "list of shops" & "list ends here"

Also for each of the rows picked up i need a column that says while file-sheet name the data was picked from.

Kindly help. Thanks

[v-juanli-msft]

Hi @fahadarshad 

I tried with JohnThomas's method, it works on my side.

Just a little modification to fit my scenario, it works.

If you have any problem, feel free to ask us.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

@fahadarshad ,

Try filtering it on your sample file.

Ricardo

[JohnThomas]

Can you upload some sample workbooks?

[fahadarshad]

Here you go,

I just need the rows that have shop names, nothing before of after that.

[JohnThomas · Accepted solution]

Try this on for size.  I made some assumptions about your data...  If you need something different just repost.

Getting Shop List from Excel Workbooks and Sheets 

Thanks,

John

[JohnThomas]

This seems rather simple.  Can't you just filter your column to anything with the word "Shop" or "List" in it?  Or are the Shop 1, Shop 2, etc. realy some other names?

= Table.SelectRows(#"Previous Step", each Text.Contains([ShopList], "Shop") or Text.Contains([ShopList], "List"))

Is the data in a table, or just raw data in worksheets?

Is there a column header?  Are you needing to know how to filter the list, or is the real problem combining all the sheets and files together into one table with all shops?

It will help structure a good solution to your problem if your sample data is as close to real as you can get it.