I am new to power automate (this is my first project), so forgive me if I am missing an easy solution. I am using power automate desktop and I have powerfx enabled.
I am trying to automate a transaction reconciliation process between two export spreadsheets at work. So far I have used power automate to:
- Normalize transaction IDs (leading zeroes) between a commercecloud export spreadsheet and a CRM export spreadsheet.
- Identify, highlight, and zero out duplicate records in the CRM export spreadsheet.
- Calculate sums and have them write to a third reconciliation spreadsheet where a variance is calculated.
- Subtract the transaction ID columns of the Commercecloud and CRM spreadsheets using the subtract lists action. This identifies 'unposted' transaction IDs which were processed by commerce cloud but are missing in our CRM.
I am unhappy with the current state of step 4. It only identifies the transaction IDs of unposted transactions, and while I can write these values to a spreadsheet, analysts still have to open the commerce cloud spreadsheet to hunt down the full row data listed for these IDs.
It would be a gamechanger if I could:
- Subtract the CRM spreadsheet rows from the Commerce Cloud spreadsheet based on the values of only the transaction ID columns of each spreadsheet. Then write the remaining 'unposted' rows to a spreadsheet where an analyst can review them directly.
For Example:
Commerce cloud
| 000123 |
John Smith |
|
000321
|
Jane Smith |
| 000213 |
John Doe |
CRM
Unposted (Commerce Cloud - CRM)
Is there a way to do this? I feel like I have tried a million different ways and I keep running into syntax errors with loops and expressions.
