I have a simple reconciliation problem and would appreciate if you could provide a simple way of handling it in flow. I am very new to Microsoft flow and still learning the various query syntax and odata rules. I found a way to run it but i feel its way too complex and the flow runs for over 20 mins..
Sharepoint List: Table1
Date | Customer Name | Txn Amount | From | Matching status |
2/1/2020 | ABC | 109.14 | Source 1 | |
2/1/2020 | XYZ | 109.14 | Source 1 | |
2/1/2020 | AAA | 109.14 | Source 2 | |
2/1/2020 | PQRS | 109.14 | Source 2 | |
3/1/2020 | AAA | 75 | Source 1 | |
3/1/2020 | BBB | 72.76 | Source 1 | |
3/1/2020 | CCC | 72.76 | Source 2 | |
3/1/2020 | KKK | 172.19 | Source 2 |
For each date,
If sum of amount for Source1 = Sum of amount for Source2, then all the rows for the date should be marked as Matched in the 'Matching status' column. If not, update as 'Not Matched'.
In the above example, the rows of 2nd Jan 2020 should be marked as 'Matched' and 3rd Jan 2020 should be marked as 'Not Matched'.
Hi!
"I found a way to run it but i feel its way too complex and the flow runs for over 20 mins."
As far as I know, there is not any WDL function that allows you to add values from an array, the maximum nr of elements that can be added is 2. So this means you probably need to use a loop and a variable... Power Automate has some performance limitations: Apply to Each combined with variables is usually slow.
Anyway... can you share a screenshot from your curent flow design? So hopefully we can improve a little bit, but it will not be as fast as a powerBI DAX expression
Thanx!
Proud to be a Flownaut!
Thanks @efialttes. For some strange reason, I am not able to paste the screenshots of the flow here. (may be there is some specific method of doing it and I dont know it yet). The logic I am following for the flow is as follows: As you can see, there are too many select statements and hits to the back end data - thats what causing the time taken. I am using sharepoint list and so this may not the most efficient way to deal with this. It works, but its not efficient. Any other ideas are welcome! Thanks a ton!
<start>
Select data from the table where 'MatchingStatus' is 'not matched'
Declare variable GroupDate, Sum1, Sum2
Apply to Each
Set Variable GroupDate = 'Date'
Reset Variable Sum1
Reset Variable Sum2
Select data from table where 'From' = 'Source1'
Apply to each
Increment Sum1
Select data from table where 'From' = 'Source2'
Apply to each
Increment Sum2
If Sum1 = Sum2
Select data from table where 'Date' = 'GroupDate'
Apply to each
Update MatchingStatus = 'Matched'
else
Select data from table where 'Date' = 'GroupDate'
Apply to each
Update MatchingStatus = 'Not Matched'
B,then update the Matching status for the N rows as Matched
Else, Mark them as Unmatched
<end>
Hi!
THanks for the effort. UNfortunatelly, text description do not help to precise if your 'Apply to each's are nested (i.e. each of them inside the previous one) or not, which has a strong, negative inpact on performance. WE also need to understand which input you assigned to each 'Apply to each'
Iif you need to share a screenshot from Power User editor, make sure you are writing from a desktop browser client (not every desktop browser client is compatible, and I believe mobile browsers does not display this menu) and do the following:
Hope this helps
Proud to be a Flownaut!
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 |
---|---|
48 | |
17 | |
15 | |
12 | |
12 |
User | Count |
---|---|
58 | |
40 | |
24 | |
20 | |
19 |