You’re offline. This is a read only version of the page.
34
Hi,
I'm unsure how to do this but I have probably a couple hundred or so files to go through for data and I'm unsure how to easily extract the data from them onto one sheet. Luckily, each sheet only has one tab of data so my hope was to create a process that could take each sheet and put onto a new single sheet with each excel file it's pulling being a tab on the new sheet. I've put a basic format below which I hope will help explain what I'm trying to do. Additionally, If I can only extract sheets from one folder that's fine too. Just need to speed up the process by any means.
Procedure steps
Let me know if you need more information. Thanks
Strangely I was getting an error earlier and it would only pull one file but I found a reference online and managed to get it to work although I still hit a row limit on imported data. Thanks for your help
Hi @MStark171 ,
If you are using Power Query, the tab name is not important because you can get the first sheet everytime. The key to Power Query (cloud or non-cloud) is that the columns are named the same and in a consistent format.
There isn't a limit with Power Query on the # of rows.
So I ran into a problem where it turns out besides each sheet being named differently. They also named each tab in said sheets differently as well so I haven't been able to import everything besides a single sheet. Additionally, each file seems to have on average 70k rows of data and I believe the limit is 100k? Any ideas how to work around this and pull with a filter as well?
Hi @MStark171 ,
You will love it! Here's a sample solution that works amazing!
In the middle of the night, I receive 4 separate inline html tables of data by email. With Power Automate I turn that data into four separate csv files and save in a folder OneDrive or SharePoint. At the end of this automation, I then use the Power Automate action "Refresh a dataflow".
That dataflow is essentially Power Query (in the cloud) and I take the 4 csv files in that folder, combine them, do other things and save the data in dataverse table. Dataflows lives in Power Apps but is the cloud version of Power Query.
Then I have a separate flow that says, "When a dataflow refresh is complete"... do these actions.
Once you use this, a lot of automation options will open up for you!
I did not know that was an option. I just figured power automate since I've used it in the past to automate other things and figured it was the easiest way. I'll try it out and see if it works. Thanks
For clarification: Power Query inside or outside of Power Automate does not require your data to already be in tables; that's the really benefit here, mass file processing of range data and your data will come back as a single collection of data. It could be a csv, excel data, or actual table inside excel or in dataverse but regardless it is available downstream in automation.
@MStark171 There are really 3 options for this.
Regardless of the method you choose to do it exactly as you described you will need a good understanding in power automate desktop or visual basic. Both of these can handle the no tables and changes in the worksheets.
Hi @MStark171 ,
You can do that without the use of Power Automate or you can use inside Power Automate. If this was my project, I would do outside of Power Automate unless there was a process that required it.
So essentially open a blank workbook and Get Data from folder. Here you can combine all the files from a folder. You can also do this from other folders, etc, etc, etc. and then you can easily combine all excel sheets into a single table of data to use in automation projects.
Now if you need to do this in automation, again ask yourself why you need it, you can get data with dataflows and do this in the cloud. Then when you combine sheets you can save the output in a dataverse table and use in automation.
Again, unless required, my preference is to combine manually with Power Query since you are working with files from the past; there's an assumption they are not changing.
Another option to consider is write quick automation that copies all sheets into a temp folder and then simply process that folder you get data > folder > combine ....
You have options and you should be able to accomplish this with Power Query directly from Excel or Dataflows in Dataverse.
#1
Michael E. Gernaey
566
Super User 2025 Season 1
#2
David_MA
516
Super User 2025 Season 1
#3
stampcoin
492
Share
Report
All responses (
Answers (
