cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RR111
Regular Visitor

step list row present in a table filter array dont work

Hello, i'm trying to filter my excel table in one drive so that i import only the right row in my sharepoint list. So in the: step list row present in a table for excel, in the section filter query, when i do departement eq 'hr', it work but when i put more thant one condition like:departement eq 'hr' and departement eq 'finance'. It fail. i dont understand. So basicly, i would to filter the right row in my excel table before importing in my sharepint. EX: Import all from departement column where there is finance and hr. Help pls

RR111_0-1631254604799.png

RR111_1-1631254761773.png

RR111_2-1631254797354.png

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ekarim2020
Community Champion
Community Champion

Sorry, that was my mistake. See below, where you need to select the BODY element (i.e. content from the filter array):

ekarim2020_0-1631315275285.png

I ran the flow again, and only finance and hr data is created:

ekarim2020_1-1631315297123.png

Update: Here are the expressions - if you need them - for departement and categorie:

ekarim2020_2-1631315648523.png

Ellis

View solution in original post

10 REPLIES 10
ekarim2020
Community Champion
Community Champion

You want to select rows using OR - as you want to select rows that contain either 'hr' OR 'finance':

 

(department eq 'hr') or (department eq 'finance')

 

There is also the Filter Array action that could be used to filter the row items:

ekarim2020_2-1631262437150.png

Using Filter array in advanced mode, you could enter an expression similar to:

ekarim2020_1-1631262381639.png

I'm guessing your column names:

@OR(equals(item()?['department'], 'hr'), equals(item()?['department''], 'finance'))

 

Ellis

RR111
Regular Visitor

1)i try the first method it didnt work

RR111_0-1631295062366.png

2) how do i use the data in the filter array to fill my sharepoint list?i'm a begginner in power automate.

3) what is the ? and @ symbol in the code?

here are photo of my sharepoint and my excel, once it work i will use the same technique to import my real database:

sharepoint:

RR111_1-1631295218354.png

excel online in one drive:

RR111_2-1631295297034.png

i want to import data from excel one drive where departement is either hr or finance inside sharepoint list.

Thank you

 

 

 

ekarim2020
Community Champion
Community Champion

(1) You will need update the query to use the correct column names from your Excel file. I just noticed that I my examples are a different spelling for the column name. I used department, where as your file has departement.

 

(2) For the List Rows present in a Table action, it appears that the query is missing the OR operator:

ekarim2020_1-1631296405626.png

Try the filter query again , inserting the OR between the two grouped queries:

(departement eq 'hr') or (departement eq 'finance')

 

ekarim2020_4-1631297154684.png

 

Ellis

i did, it won't work even with or

RR111_0-1631299246317.png

 

here the error detail:Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.
clientRequestId: 6f3a7297-f0af-470e-8c1e-9b1e8b7ef02a
serviceRequestId: 57f776dd-3d33-4845-81c7-3df7cfb0158c;40ffdda4-abf9-4713-b0f3-273a0ebfec67;d64a1eaa-78fd-44fe-90b7-078662ca3b4a

ekarim2020
Community Champion
Community Champion

OK, so although the OData query is valid, the List rows action currently only supports 'eq', 'ne', 'contains', 'startswith' or 'endswith':

ekarim2020_3-1631304853550.png

In that case we will use the Filter Array option I mentioned in the first post.

This is the Excel file:

ekarim2020_0-1631304688101.png

This is the flow that I tested:

ekarim2020_5-1631305284263.png

 

Here are the details of the flow:

ekarim2020_1-1631304730391.png

 

ekarim2020_2-1631304780123.png

Expression:

ekarim2020_0-1631305636882.png

And here is the output produced by Filter array action:

ekarim2020_4-1631305106669.png

Ellis

RR111
Regular Visitor

Thank you, The filter array does work, but how i import the data from filter array in my sharepoint, before without the array, i just need to select the colum of the excel table like the picture here in the create item of sharepoint and match the right column in sharepoint?

RR111_0-1631313492505.png

 

ekarim2020
Community Champion
Community Champion

Filter array returns an array of values, even if there is only one value in the array. So no need to worry about the apply to each loop being used to create items in the SharePoint list.

ekarim2020_1-1631314690079.png

I've created a test list called payss, ran the flow and it created the following entries in the list:

ekarim2020_2-1631314745014.png

Is this not the outcome you are looking for?

Ellis

Thank you for the reply, it has marketing in departement in your sharepoint list while i only want finance and hr to be import in sharepoint. That why i was looking for a way to filter (not delete) the excel table before importing it in sharepoint.

ekarim2020
Community Champion
Community Champion

Sorry, that was my mistake. See below, where you need to select the BODY element (i.e. content from the filter array):

ekarim2020_0-1631315275285.png

I ran the flow again, and only finance and hr data is created:

ekarim2020_1-1631315297123.png

Update: Here are the expressions - if you need them - for departement and categorie:

ekarim2020_2-1631315648523.png

Ellis

View solution in original post

Thank you ekarim, i was looking for a solution for hours and hours, thank you for your help , it work great now with the solution you provide. Thank you 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,468)