cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dunadaen
New Member

Reading Data from Excel to Sharepoint with Update or Create Item. Filter Query not working with multiple Columns.

All right. I've been working for almost 8 hours now to figure this out, and somehow i can't get it right. I have a Excel List with a few columns. Unfortunately the Data in the list has no unique identifier, and i can't change that, since the list is an SAP Export and there is no absolute identifier there that i could use. However, inside the List i have four columns that will completely identify the record. Now here's the trick: Two of those columns are always filled. But column 3 and 4 are opposites. If there is an entry in column 3 then 4 is "0" while if column 3 is empty then 4 contains a number. Here is some Sample Data. In the column to the far right i've marked x or -. With the following statement (1) the Flow imports all x, but leaves the - out.  With the second statement (2) the Flow imports the two samples that have a value in Column 4 (column 3 empty)  and creates duplicates. 

Also: Column 3 is unique, and the other three are unique in combination.

I hope someone can help me understand what i need to do. also below a screenshot of the actual step i take to compare the excel table to records in the sharepoint.

 

Statement (1):

(Gesch_x00e4_ftsfall_x002d_ID eq '@{items('ForAll')?['Geschäftsfall-ID']}' or LaufnrBO eq '@{items('ForAll')?['Laufnr BO']}') and Stationsbezeichnung eq '@{items('ForAll')?['Stationsbezeichnung']}' and WS_x002d_Nummer eq '@{items('ForAll')?['WS-Nummer']}'

 

Statement (2):

Gesch_x00e4_ftsfall_x002d_ID eq '@{items('ForAll')?['Geschäftsfall-ID']}' and LaufnrBO eq '@{items('ForAll')?['Laufnr BO']}' and Stationsbezeichnung eq '@{items('ForAll')?['Stationsbezeichnung']}' and WS_x002d_Nummer eq '@{items('ForAll')?['WS-Nummer']}'

 

WS-NummerStationsbezeichnungGeschäftsfall-IDLaufnr BOColumn
101Affoltern am Albis956791b9-849b-470a-bfc6-96d89a7078e60-
101Affoltern am Albise282d194-88d0-4ffc-8ec5-5e40f8729e260x
102Affoltern am Albis252eedf4-3f51-4b20-aac9-70d17f0c560e0x
102Bad Zurzach3060e652-ff03-4802-9a7c-e904fb6ce9110x
101Baden33d45685-e595-428b-a221-3d878b4a163b0-
101Baden94b68b10-58f1-483d-93d9-179c3dce15670x
102Baden66e27f79-02b5-455a-a0d1-cdf5faabe54c0x
103Baden7698f9b7-dc24-4553-a923-8a72d20e99520-
103Badenf14a2e35-1bbf-4546-ae91-e5fb756c51570x
106Baden9ad9d946-27b3-4eb8-b283-ec6b959594f80x
113Badenae0c2467-f307-4162-811f-e5a928d88a820x
114Badenbb530084-67f7-45f0-b9f8-4fadfc450d120-
114Badenda81d794-d5fd-4390-bafa-9c41e2b9cec50x
102Brugg AG9c3c396b-46e5-47b2-b8e3-d9a1be6b40150-
102Brugg AG9d76efb9-a1b3-49c7-851f-f81971fb32b00x
103Brugg AG6cac7dad-f372-49c9-828d-fbbdbde2dadd0x
104Brugg AG50f9534c-fcc3-4fee-9299-a357c2921a430x
101Schlieren0dffefa8-0afd-428b-8201-0971c3a550b10x
101Schlieren 973246x
102Schlieren6fd1b100-bd1f-49ac-b4ce-5a5daecdacd40x
101Spreitenbach Tivoli80019936-a738-49bd-b1cc-c8a4793059f40-
101Spreitenbach Tivolif7497756-4a1a-484b-9b68-0c228c1324440x
102Spreitenbach Tivoliacfee5b2-1f50-44c9-a42d-91fb6c051f0e0x
102Spreitenbach Tivoli 548118x
102Thalwil40287c64-4517-41a7-8d70-1f5b16871d890x
101Wädenswil820ea44b-e0ea-40ef-affd-2a94ea948e490x
102Wädenswil1e667be6-87e7-4cde-9a7c-12449ba0b6d50-
102Wädenswilb1a4054c-72cf-4762-9ee2-8453c23b56400x

Dunadaen_1-1621371376106.png

 

2 REPLIES 2
v-LilyW-msft
Community Support
Community Support

Hi @Dunadaen 

Thank you for posting.

I tried to reproduce your Filter Query statement but it works fine for me.

This is my Excel

v-LilyW-msft_0-1621407624543.jpeg

This is my list

v-LilyW-msft_1-1621407624546.jpeg

When I test the Filter Query as you I can get the Items

v-LilyW-msft_2-1621407624551.png

v-LilyW-msft_3-1621407624553.jpeg

 

You can check the column name by the screenshot,confirm that your column is correct.

v-LilyW-msft_4-1621407624554.jpeg

 

v-LilyW-msft_5-1621407624555.jpeg

Hope the content above may help you.

 

Best Regards

Lily

Hello @v-LilyW-msft 

It's true that the filter basically works. But when i use filter one only 21 of 28 records make it into sharepoint. If i use filter 2 then the two records that have no 'Geschäftsfall ID' but a 'Laufnummer BO' get imported twice and duplicates are created. I studied your answer but can't see anything that helps me solve this problem. 

Do you have any suggestions why that is the case or where i make the error in my logical thinking? 🙂 

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.

Top Solution Authors
Users online (2,106)