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-Nummer | Stationsbezeichnung | Geschäftsfall-ID | Laufnr BO | Column |
101 | Affoltern am Albis | 956791b9-849b-470a-bfc6-96d89a7078e6 | 0 | - |
101 | Affoltern am Albis | e282d194-88d0-4ffc-8ec5-5e40f8729e26 | 0 | x |
102 | Affoltern am Albis | 252eedf4-3f51-4b20-aac9-70d17f0c560e | 0 | x |
102 | Bad Zurzach | 3060e652-ff03-4802-9a7c-e904fb6ce911 | 0 | x |
101 | Baden | 33d45685-e595-428b-a221-3d878b4a163b | 0 | - |
101 | Baden | 94b68b10-58f1-483d-93d9-179c3dce1567 | 0 | x |
102 | Baden | 66e27f79-02b5-455a-a0d1-cdf5faabe54c | 0 | x |
103 | Baden | 7698f9b7-dc24-4553-a923-8a72d20e9952 | 0 | - |
103 | Baden | f14a2e35-1bbf-4546-ae91-e5fb756c5157 | 0 | x |
106 | Baden | 9ad9d946-27b3-4eb8-b283-ec6b959594f8 | 0 | x |
113 | Baden | ae0c2467-f307-4162-811f-e5a928d88a82 | 0 | x |
114 | Baden | bb530084-67f7-45f0-b9f8-4fadfc450d12 | 0 | - |
114 | Baden | da81d794-d5fd-4390-bafa-9c41e2b9cec5 | 0 | x |
102 | Brugg AG | 9c3c396b-46e5-47b2-b8e3-d9a1be6b4015 | 0 | - |
102 | Brugg AG | 9d76efb9-a1b3-49c7-851f-f81971fb32b0 | 0 | x |
103 | Brugg AG | 6cac7dad-f372-49c9-828d-fbbdbde2dadd | 0 | x |
104 | Brugg AG | 50f9534c-fcc3-4fee-9299-a357c2921a43 | 0 | x |
101 | Schlieren | 0dffefa8-0afd-428b-8201-0971c3a550b1 | 0 | x |
101 | Schlieren | 973246 | x | |
102 | Schlieren | 6fd1b100-bd1f-49ac-b4ce-5a5daecdacd4 | 0 | x |
101 | Spreitenbach Tivoli | 80019936-a738-49bd-b1cc-c8a4793059f4 | 0 | - |
101 | Spreitenbach Tivoli | f7497756-4a1a-484b-9b68-0c228c132444 | 0 | x |
102 | Spreitenbach Tivoli | acfee5b2-1f50-44c9-a42d-91fb6c051f0e | 0 | x |
102 | Spreitenbach Tivoli | 548118 | x | |
102 | Thalwil | 40287c64-4517-41a7-8d70-1f5b16871d89 | 0 | x |
101 | Wädenswil | 820ea44b-e0ea-40ef-affd-2a94ea948e49 | 0 | x |
102 | Wädenswil | 1e667be6-87e7-4cde-9a7c-12449ba0b6d5 | 0 | - |
102 | Wädenswil | b1a4054c-72cf-4762-9ee2-8453c23b5640 | 0 | x |
Hi @Dunadaen
Thank you for posting.
I tried to reproduce your Filter Query statement but it works fine for me.
This is my Excel
This is my list
When I test the Filter Query as you I can get the Items
You can check the column name by the screenshot,confirm that your column is correct.
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? 🙂
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Announcing a new way to share your feedback with the Power Automate Team.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
User | Count |
---|---|
66 | |
24 | |
16 | |
15 | |
11 |
User | Count |
---|---|
115 | |
35 | |
30 | |
28 | |
26 |