cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tobias_S
Advocate I
Advocate I

Filter data from Excel file and export filter results in new file

Hello community,

 

I have a flow to build but I am stuck with the filter.

 

Current situation:

- I receive data collected in Excel (data list is not converted as table but data is listed with titles over multiple columns).

- The overall list will be saved on my OneDrive.

This file lists the data like the following:

NameSurnameDepartment

Function

Name ASurname ADevelopment

Python Developer

Name BSurname BProject Management

Project Lead

Name CSurname CDevelopment

Database Developer

Name DSurname DControlling

Project Controller

 

Now I need to extract data out of this Excel file. For each department I need a list of rows matching this filter. These filter results needs to be saved as "department overview list" as separate files. Filtering with "ODATA"?

 

These (in example case three) lists might look like:

 

Development file

NameSurnameDepartment

Function

Name ASurname ADevelopment

Python Developer

Name CSurname CDevelopment

Database Developer

 

Project Management file

NameSurnameDepartment

Function

Name BSurname BProject Management

Project Lead

 

Controlling file

NameSurnameDepartment

Function

Name BSurname BProject Management

Project Lead

 

Does someone have the needed hint how to get these files out of the overall list? Can this be done with Power Automate resp. Power Automate Desktop? I tried both but without success so far.

 

Thank you in advance for any help.

2 ACCEPTED SOLUTIONS

Accepted Solutions
tom_riha
Super User
Super User

Hello @Tobias_S ,

the data must be in a table if you want to filter it, you can follow @DamoBird365 solution on how to create the table in the Excel file.

Once it's in the table, you can use the 'List rows present in a table' with a Filter Query using the Department column, e.g.

Department eq 'Controlling'


[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

Hello @Tobias_S ,

you can name the table as you like, but the filtering column should be only a single word, otherwise the solution would be much more complicated.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

3 REPLIES 3
tom_riha
Super User
Super User

Hello @Tobias_S ,

the data must be in a table if you want to filter it, you can follow @DamoBird365 solution on how to create the table in the Excel file.

Once it's in the table, you can use the 'List rows present in a table' with a Filter Query using the Department column, e.g.

Department eq 'Controlling'


[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]
Tobias_S
Advocate I
Advocate I

Hello Tom,

 

thank you for the link and your help.

 

Is it also possible to rename a column title of a defined table then? The problem is that the current title has a text wrap.

 

Greetings

Hello @Tobias_S ,

you can name the table as you like, but the filtering column should be only a single word, otherwise the solution would be much more complicated.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (1,581)