Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
Try filtering it on your sample file.
Ricardo
Can you upload some sample workbooks?
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.