Power Platform Community Forum Thread Details

From an excel table, segment different files and save them in different sharepoint folders

I don't really understand what files are being created?

A new excel file for each category, and that new excel file has a table like the original except it only contains rows from that category?

Then if the flow ran again it would create separate new files in each category?

Name	Category	SharePoint Link
Nadal	Tennis	http/tennis
Federer	Tennis	http/tennis
Peyton	Football	http/football
Brady	Football	http/football
Curry	NBA	http/basketball
Lebron J.	NBA	http/basketball

And then you want the files to separate by category and then go to the link:

New Excel File 1: Goes to http/Tennis

Name	Category	SharePoint Link
Nadal	Tennis	http/tennis
Federer	Tennis	http/tennis

New Excel File 2: Goes to http/Football

Name	Category	SharePoint Link
Peyton	Football	http/football
Brady	Football	http/football

New Excel File 3: Goes to http/NBA

Name	Category	SharePoint Link
Curry	NBA	http/NBA
Lebron J.	NBA	http/NBA

Does that seem correct?

And if you had a new source file come in, then it would do the same thing except make New Excel File 4 Tennis, New Excel File 5 Football, New Excel File 6 NBA?

Are you receiving this initial source excel file from somewhere and know what all the categories could be?

How many rows are going to be in the source excel file (100? 10000?)

How often are you having to get a source file and separated the table (daily? monthly?)

I have 2 ideas:

First idea is something like make a copy of the source file and put it into each category folder, then go through each one of the newly created files and delete the rows that are not in that category. Then check the file to see if there is at least one row remaining after all other categories were deleted. If the table doesn't have a row (it's empty), delete the file.

Second idea is to get the source file, filter out the specific category names, which will give you a count/list of categories in an array. Then use an apply to each on the category array, and make another call to the source file and filter the table by the category column. And then create a file, create a table in the file, and insert rows into the file.

All of this seems like a lot of work to make excel files have separate data in separate folders.

It seems like you could solve that from how the source creates the initial table. (Instead of 1 table with everything, it makes 3 separate and emails them to you.)

Or how the end user deals with the files. (Instead of getting individual files of Tennis, just get all of the source files and filter category to Tennis, Power Query can do this easily.)

Could you have a single excel file and table for each category instead of separate files for the same category? (It would be easy to make a blank excel file for each category and put it as a destination. Then use an apply to each on the source file rows and just add a row to the destination table.)

Quick Links

Leaderboard