Answered

Comparing 2 Excel sheets to give a digest on what rows have changed

Like (1) Share

Report

Posted on 18 Nov 2024 01:50:32 by Hazza

I'm looking to build a flow which can compare a daily stock report sheet to the previous day's and give a quick digest to what has changed, and if there's any new rows.

So far I have actions that list the rows in a table for both sheets, and select actions to look at the columns I'm interested in. Then I have 2 apply to each loops which compares the new sheet to the old, and the old sheet to the new. Using compose actions and filter arrays after this, I can get 2 outputs - the rows from the previous days report which have changed, and then the rows on the newest report which have changed AND it includes new rows as well.

With this data, I'm sure there's a way to isolate the duplicates from the new unique rows, and then compose this into what I need, but I'm struggling on how to implement it.

Each row will have a unique identifier (serial number). The columns I'm interested in are "serial number" and "condition".

I want the end result to send an email digest out with a table that states if a serial number has changed condition, and if there are any new rows added on the latest stock report.

I've been working on this for the past few days, my brain is a little fried so apologies if there isn't enough detail to go off here.

Let me know if I need to provide any further info, and thanks in advance!

Harrison

Categories:

Business Process Flows

All responses (6)

Answers (1)

Hazza 40 on 20 Nov 2024 at 18:22:18

Like (0)

Report

Comparing 2 Excel sheets to give a digest on what rows have changed

I've got this working now! Thanks for all of you help and patience, Chriddle! :)

Harrison
Verified answer

Chriddle 7,742 Super User 2025 Season 1 on 19 Nov 2024 at 08:54:21

Like (1)

Report

Comparing 2 Excel sheets to give a digest on what rows have changed

As I said, you don't need loops.

The first filter action takes all rows from the "new" sheet and filters for items that have a Serial Number not in the list "Select-old-serial". These are the new items.

The second filter also takes the data from the "new" sheet and filters for items that are not in the old list and also are not in the list returned bt the previous filter. These are the changed items.

Important for this is the contains() function, which checks wether an item is present in a list.

My flow below works fine with your data after I changed my property name "serial" to match yours ("SerialNumber") in the expressions.

The first filter array returns the new elements, the second the changed elements:

Hazza

40 on 18 Nov 2024 at 19:16:52

Like (0)

Report

Comparing 2 Excel sheets to give a digest on what rows have changed

Hi Chriddle, thanks for your replies!

After reading through your instructions and playing around with this flow periodically throughout the day, I'm still unable to get this working :(

If it's not already obvious, my abilities with power automate are limited - my previous flows have been rather simple and mostly using the dynamic content available to me, so my understanding of the language used in expressions etc is poor! (but not entirely non-existent. I can understand the language after using examples and watching it work.)

I also think I've become a bit fixated on how I believe the steps in this flow should unfold, therefore I'm not seeing the fuller picture.

Are you saying that I don't need any apply to each loops involved here at all? I thought I'd need at least one of these loops to compare each sheet and their rows so that I can outline the rows that are new/changed, and then use some other actions with this output to identify the duplicate serial numbers (meaning this was an existing row, but the condition has changed) and leave me with the unique rows meaning that these are the new additions on the newest sheet.

I feel like I'm almost there with this flow seeing as I can output that first bit of data (the new AND changed rows), but I'm also not entirely convinced that I'm not going down the completely wrong path to get to my final desired result.

I really appreciate the input you've given so far, and if you have the time/patience to provide a little more detail on how you would get this flow running I would be very grateful!

To clarify with an example (just in case it's needed - I mentioned this has fried my brain a little so not sure if my original question was that clear!), this is what I'm trying to achieve:

Previous/Old sheet-

Serial Number	Device	Condition
1234	Laptop	New
5678	Laptop	New
ABCD	Laptop	Used
EFGH	Laptop	In Workshop

New/Current sheet-

Serial Number	Device	Condition
1234	Laptop	New
5678	Laptop	New
ABCD	Laptop	Used
EFGH	Laptop	Refurbished
IJKL	Laptop	New

OUTPUT:

Existing devices that have changed condition
Serial Number	Device	Old condition	New condition
EFGH	Laptop	In Workshop	Refurbished

New devices
Serial Number	Device	Condition
IJKL	Laptop	New

Please let me know if I need to provide any more detail, and thank you to anyone who takes the time to read through this.

Harrison

Chriddle 7,742 Super User 2025 Season 1 on 18 Nov 2024 at 09:31:52

Like (2)

Report
Comparing 2 Excel sheets to give a digest on what rows have changed

Don't do this with loops and condition, use Filter array.

1. Create an array of old serial numerbers (Select-old-serial):

{ "inputs": { "from": "@outputs('Compose-old')", "select": "@item()['serial']" } }

2. Filter new data for items with serial numbers that are not in the array above (Filter array):

{ "inputs": { "from": "@outputs('Compose-new')", "where": "@not(contains(body('Select-old-serial'), item()['serial']))" } }

3. Filter new data for items that are not in the array above and not in the old data (Filter array 2):
{ "inputs": { "from": "@outputs('Compose-new')", "where": "@and(not(contains(body('Filter_array'), item())),not(contains(outputs('Compose-old'), item())))" } }

Result:
Chriddle 7,742 Super User 2025 Season 1 on 18 Nov 2024 at 09:21:50

Like (2)

Report

Comparing 2 Excel sheets to give a digest on what rows have changed

A good example that LLMs do not yet have enough training data to generate reasonable Power Automate flows ;)
Suggested answer

Robu1 1,239 Super User 2025 Season 1 on 18 Nov 2024 at 06:12:16

Like (0)

Report
Comparing 2 Excel sheets to give a digest on what rows have changed

Hi  Hazza,

Thank you for choosing Microsoft Community.

To compare two Excel sheets and generate a digest of what rows have changed using Power Automate, you can follow these steps:

Steps to Compare Excel Sheets in Power Automate

Create a New Flow: Start by creating a new flow in Power Automate

2. Open Excel Files: Use the "Open Workbook" action to open both Excel files [

3. Read Data: Use the "Read Range" action to read the data from both sheets

4.  Apply to Each Loop**: Use an "Apply to Each" loop to iterate through the rows of the first sheet

5. Find Matching Rows: Within the loop, use the "Find" action to search for matching rows in the second sheet

6. Check for Changes: Use conditions to check if the rows match or if there are any differences.

7. Create Digest: Add actions to create a digest of the changes, such as adding rows to a new table or sending an email summary.

Example Flow Structure

1. Open Excel Files:

   - `Open Workbook` (First Excel File)

   - `Read Range` (First Sheet)

   - `Open Workbook` (Second Excel File)

   - `Read Range` (Second Sheet)

2. Apply to Each Loop:

   - `Apply to Each` (Rows in First Sheet)

     - `Find` (Matching Row in Second Sheet)

     - `Condition` (Check for Changes)

     - If Match: Add to Digest Table

     - If No Match: Add to Digest Table with Changes

3. Create Digest:

   - `Create Table` (Digest of Changes)

   - `Send Email` (Summary of Changes)

Additional Resources

- How to compare Excel Tables/Sheets in Microsoft Power Automate](https://www.youtube.com/watch?v=CRJXfVVhYrA)

- PowerAutomate: How to compare between 2 excel files items and get the matching result](https://www.youtube.com/watch?v=-odX-LeBJ5w)

If this resolves the issue pls mark as answered to enable others in the community find it.

Happy to help.

Robu1

Community site session details

Comparing 2 Excel sheets to give a digest on what rows have changed

Helpful resources

News and Announcements

Quick Links

Announcing the Engage with the Community forum!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Warren Belz – Community Spotlight

Leaderboard > Power Automate - Building Flows

Featured topics

Product updates

Community site session details

Comparing 2 Excel sheets to give a digest on what rows have changed

Helpful resources

News and Announcements

Quick Links

Announcing the Engage with the Community forum!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Warren Belz – Community Spotlight

Subscribe to this forum!

Select categories

Leaderboard > Power Automate - Building Flows

Featured topics

Product updates