How to compare Excel Tables/Sheets in Microsoft Power Automate
Vložit
- čas přidán 28. 08. 2024
- This video tutorial shows, how to compare Excel Sheets and Tables with Microsoft Power Automate. Three examples are created and data are taken out, where the tables match (intersect), where the tables not match (distinct) and where we join the tables.
You could also watch:
🔵 Power Automate for Beginners - • Microsoft Power Automa...
🔵 Power Automate .XLSX to .CSV - • Microsoft Power Automa...
0:10 Our Power Automate Compare Excel Use Case
Presenting the case, where we want to compare Excel tables with Power Automate
1:35 Create our flow to find matching Excel Rows
We iterate through each row in two Excel tables and find matching rows. The result we chose to write in a third table.
1:58 List rows present in a table
Use the action to get all the rows in the Excel table. Because we will read two tables, it's important to rename the activity to have a better overview and not get confused.
2:54 Apply to each
The Apply to each action will loop through all the rows in the Excel table.
3:29 Create a nested loop
The other Excel table is read with another List rows present in a table and an Apply to each. The intuition is that we take a row one by one in the first table and compare that exact row with the rows one by one in the second table.
5:19 Condition
We create the condition to write matching rows to a new table.
7:26 Flow to find rows in Excel tables that don't match
The intuition is almost the same as the previous flow. We will also do a nested loop, but we will introduce a boolean variable to help us keep track of the non-matching rows.
15:31 Flow to join to Excel tables
Again we can apply the same intuition from our two previous flows to join the two Excel tables with Power Automate. Instead of editing our existing tabled we create a new table to write to.
Connect with me:
🔔 Subscribe - www.youtube.com...
💼 LinkedIn - / andersjensens
👥 Facebook - / andersjensenorg
💌 Email Newsletter - andersjensen.o...
#powerautomate #excel #microsoft
My new complete Excel with Power Automate guide is here: czcams.com/video/PlCnYDkbw3I/video.html
Thanks for all your support 🙌🔥
Exactly the flow i was trying to build! Thank you!
Thank you for the kind words. It means a lot, Anders
at last an easy enough to understand video. much appreciated.
I'm so glad to hear, thanks a lot, Mahmoud 🙌🙂
Exactly what I needed to append data I receive into a separate sheet without creating duplicates. Thank you
Hey Alan. Thanks a lot :) I'm happy, you could use it. Kind regards, Anders
Thank you for this video which is exactly what I was looking. From past 5 days l was looking for a solution from power query how to append data from one table to another but it should not overwrite the existing details, kind of history of details for past one month, I couldn't find the solution and finally got into your video with flow. Thanks once again 😊👍
Hey Shalini - thank you so much 🙂 I'll be doing my best to share a full Power Automate course here on CZcams very soon. Kind regards, Anders
This is a fantastic set of videos. I have started at the beginning on the Beginners Introduction and I have found this extremely useful . Great Tutorials
You're very welcome, Alan Hanley - your support helps me a lot 😍
Are you a beginner at Power Automate. Then watch my Power Automate Beginners Tutorial: czcams.com/video/omQ-fAF4QpI/video.html It's my best Power Automate video and it will take your through all the basic steps to get started.
Thank you soooooo much! This worked! This is what I needed and you explain the steps very well. Thank you for this video.
Thanks a ton, Jennifer Rice 🤩 Have a great day. You're more than welcome at my Discord, where we're 5800+ RPA developers networking and solving problems: czcams.com/video/xWFz-S96XGo/video.html Kind regards, Anders
Very informative video. Completed the fundamental of power automate and watching your videos to check the realtime usecases
Thanks a ton, Kapil Kumar 🤩 Have a great day. You're more than welcome at my Discord, where we're 5000+ RPA developers networking and solving problems: czcams.com/video/xWFz-S96XGo/video.html Kind regards, Anders
Your video is fantastic!! Thanks for sharing and for doing easier
You're very welcome, Jorge Lopez Cillero - your support helps me a lot 😍
Thanks ☺️
More than welcome 🙏😊
I found this video as I wanted to compare an excel file that we recieve daily to the previous days file.
This video was a great starting point but as both files had about 60 records it was looping through about 3600 times which was taking up to 20 minutes.
I was able to refine the flow so instead of having an 'Apply to each' on the2nd table (Green Table), I used a Filter Array on the 2nd Table. Then using a conditions to check if the outputs of filter array is empty will determine if the row exists or not. Using this method the flow now runs in 30 seconds.
I understand that the filter array functionality may not have been availabe when the video was made or it may have already been addressed in a later video
Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 10,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): czcams.com/video/xWFz-S96XGo/video.html Kind regards, Anders
Hey! I had some questions about how you ended up doing this. Is there a way to message you for questions or see an example of what you did?
Very good topic explained clearly, thank you!
Thanks a lot, Virupaksha 🙂👌
thanks... u got my attention
My pleasure, Shivas 😊🙌
Really helpful
Thanks a lot, SUNIL 🙏😊
@andersjensenorg Thank you for this very clear step by step instructions. Is there a way to remove duplicate entries for the join?
Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 9000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): czcams.com/video/xWFz-S96XGo/video.html Kind regards, Anders
Great video! Definitely subscribing
Hey Hugo. Thank you very much 😊 Kind regards, Anders
Great explanations, thanks for the help.
Thank you, John 🙂
Hi Anders. Thx for vdos as I learn so fast with them. I have a little question please : last part - I dont need a JOIN in a file but simply update/add new row in the table. Or simply I want to add Green to Blue (so I dont need JOIN table). How do that ?
Hey CaseyJones Thanks for writing. I'm getting more than 50 questions/comments every day and having difficulty answering everyone (I try my best). I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: czcams.com/video/_QRI-Yo2h8U/video.html Have a great day. Kind regards, Anders
Very informative video. 2 questions:
1. Is it advisable that we can use this Flow wherein we have thousands of rows in each of the files?
2. Is it possible to have one single flow to do the Match and No Match?
Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 12,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): czcams.com/video/xWFz-S96XGo/video.html Kind regards, Anders
Excellent mate you save my day, how can i contribute more to your channel?
Hey Mikhail. Thank you 😊 By commenting like you do, you are helping me more than I can ask for! Kind regards, Anders
Greetings, and thank you for all that you do. I can use some direction. I am using this method to match 3 aspects of a document (Name, Price & Order Number). The matching seems to be working but I am seeing many duplicate records. The only difference between your video and my flow is I am adding two additional criteria in the condition block. Is there a way to eliminate the duplicates? Is there another method that accomplishes the same task? And thanks again mate..
Hey EDJK Thanks for writing. I'm getting more than 50 questions/comments every day and having difficulty answering everyone (I try my best). I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: czcams.com/video/_QRI-Yo2h8U/video.html Have a great day. Kind regards, Anders
Hi Anders,
I keep getting a flow run timed out error when I use the method you show for the do not match filter. I have a large data set (321x1 compared to 30x1) so I feel like this may be the issue. Is there anything you could suggest to split the table up to help speed up the flow? I am getting correct results in my output table but the flow just times out each time. Thanks, and great videos by the way!
Hey Ryan. Thanks for the question. At the moment, I’m on paternity leave, meaning I don’t produce videos and solve problems until the 23rd of August. Feel free to post the problem at my Discord, where many bright automation minds are gathered: czcams.com/channels/Pdtz4gd_iYebJFYq9N8pWA.htmlcommunity?lb=UgxD1Qe9lWKL1I7HyAJ4AaABCQ Kind regards, Anders
Thanks Anders for the fantastic video. i have been working with the second scenario (No match/destinct) but everytime i run the flow i get different results. Could you please help me with understanding what i might be doing wrong though i have created exactly the same flow and tables as is in the video, still no luck.
Hey pinkfl0ss
Thanks for writing. I'm getting more than 40 questions/comments every day and having difficulty answering everyone (I try my best).
I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: czcams.com/video/_QRI-Yo2h8U/video.html
Have a great day.
Kind regards,
Anders
👍
🙌🙌🙌
That's a great video. I'm very new to Power Automate and it's all explain nicely and easily. I'm trying to figure out how to copy items from one list to another but discard duplicates? e.g. one list with names (first name) and we have number of Michals, in the second list I need to see just one Michal, any ideas how to approach this problem?
You can run an Excel Script, and learn how to write code to remove duplicates and run the code in Power Automate.
Hey Michal Blichowski
Thanks for writing. I'm getting more than 40 questions/comments every day and having difficulty answering everyone (I try my best).
I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: czcams.com/video/_QRI-Yo2h8U/video.html
Have a great day.
Kind regards,
Anders
Thanks for the video Anders, I tried to duplicate your join table flow and everytime I do I get an apply to each under the condition at the end and I get hundreds of results as it draws it through multiple times before timing out. Is there way to stop power automate from automatically adding an apply for each, it won't let me drag it out as other places have suggested.
Hey Matthew Conway
Thanks for writing. I'm getting more than 40 questions/comments every day and having difficulty answering everyone (I try my best).
I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: czcams.com/video/_QRI-Yo2h8U/video.html
Have a great day.
Kind regards,
Anders
WOuld recommend me using Uipath or Microsoft Power Autom,? Do I have to pay tu use MPA or Can I use an studio free version like Uipath?
I would like to see a Video or download attachments in Outlook and copy the name of one file (either txt or pDF) just the name of the file and paste somewhere in internet to extract information.
Would you give some advice also about copying part or paste part of the text we identify? For example copy some information, a number for example or a name and paste only part of it, las 6 digits, or eliminate the first 4 digit or something like that.
Thank so much for your videos!!
Regards,
Manuel.
Hey Manuel. Thanks for your feedback and your kind words 😊 You can get UiPath Studio for free as you say and you can get a Power Automate for free as well, however it's kinda limited and lack some of the premium features. I would definately go with UiPath for UI automation (and Power Automate for a lot of the Microsoft Automation, but I/you can do totally fine with UiPath alone). Check this video about downloading attachments, czcams.com/video/EYZpM0g67JE/video.html Then I would use this to get the files (just skip the opening part), czcams.com/video/Pr8ZBew6C0A/video.html, now you got the file names and then I would apply regex to it, check this (or check the video, that I will upload in 8 hours) czcams.com/video/VVRdjAbhVLU/video.html I really hope, that helps you, please let me know 😊 Kind regards, Anders
@@andersjensenorg Thank you! I am checking the videos, I do have a lot o work to do, I will let you know how it goes! And thank you for the advices, i am more used to Uipath but If I have time I will check eventually MPA..
You are posting a new video everyday, thats impressive, thanks again!
Thanks, Manuel. I don't think PA should be a priority, when you got UiPath, but it can come at a later point 😊 Kind regards, Anders
Hey Manuel. That day came earlier than expected. Microsoft just bought Softomotive meaning I would gp that way now 🤯🙂
@@andersjensenorg Thank you! So, do you recommend to go for power automate instead of Uipath?
Is there a way to make it run faster for a larger data set besides boosting concurrency for the similar values in each list?
Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 8000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): czcams.com/video/xWFz-S96XGo/video.html Kind regards, Anders
Is it possible to run an excel formula in a saved excel file in onedrive automatically using Power Automate? And then send that file as an attachment to a person via email?
Hey Musharraf Hamza
Thanks for writing. I'm getting more than 40 questions/comments every day and having difficulty answering everyone (I try my best).
I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: czcams.com/video/_QRI-Yo2h8U/video.html
Have a great day.
Kind regards,
Anders
Hi Jensen,
It is taking a lot of time for records more than 30k. Can you please explain pagination and batch for large datasets comparison
Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 11,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): czcams.com/video/xWFz-S96XGo/video.html Kind regards, Anders
Quite useful Video, Could you also let us know how to run excel macro through flow as I was not able to select xlsm file through Flow? Any video on this would be really helpful
You cannot used .xlsm files. You have to Run Office Scripts which is written in TypeScript and not VBA.
@@Ethan-dd4ln could you show us the sample typescript file?
@@Dany-ns6hg what is your email?
Hey Dany
Thanks for writing. I'm getting more than 40 questions/comments every day and having difficulty answering everyone (I try my best).
I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: czcams.com/video/_QRI-Yo2h8U/video.html
Have a great day.
Kind regards,
Anders
I follow your training and i have a question. What are the potential reason for seeing duplicate records showing up in the "match" table?
Hey Kevin. That's a case you'll see in many companies - compare two Excel tables, find the matches and do something 🙂
Excellent
Thank you 😊
Is it possible to match live data entry from MS form with an existing excel sheet? I am trying to make a live attendance checking system using Power Automate for MS Form & excel sheet?
Sure, you need to combine the two solutions: czcams.com/video/ZzAmAgxYRxw/video.html Kind regards, Anders
Hi Anders ,
If we have extra column in one Excel how will compare that?
Hey Abbidi manasa
Thanks for writing. I'm getting more than 40 questions/comments every day and having difficulty answering everyone (I try my best).
I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: czcams.com/video/_QRI-Yo2h8U/video.html
Have a great day.
Kind regards,
Anders
Hi @andersjensenorg
If i want to compare 3 columns of 1 excel sheet with the 3 other columns from other excel sheet.
What will change ?? Because i tried implementing this same method , but it didnt work for 3 columns of the table
Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 9000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): czcams.com/video/xWFz-S96XGo/video.html Kind regards, Anders
For the NO Match, why we have to go out of all and create "Conditional 2"? If we update with "If no". Is it okay?
Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 7000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): czcams.com/video/xWFz-S96XGo/video.html Kind regards, Anders
I am getting duplicate rows in my Join table. I've been through the video a couple of times and can't find my error. Any ideas?
Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 11,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): czcams.com/video/xWFz-S96XGo/video.html Kind regards, Anders
I need to compare ID(excel file) Stored in Microsoft Azure storage explorer and ID (data verse table) stored in table in power automate, so how do I compare both ID's in power flow, could you please help me.
Hey Sandeep Kumar Thanks for writing. I'm getting more than 50 questions/comments every day and having difficulty answering everyone (I try my best). I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: czcams.com/video/_QRI-Yo2h8U/video.html Have a great day. Kind regards, Anders
I am doing the same thing for matching rows in a table. My flow is working but I have 10000 data so taking to much time to run. There is any solution solution to fast run.
Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 9000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): czcams.com/video/xWFz-S96XGo/video.html Kind regards, Anders
I am getting duplicate rows in my join table. Four #1s, two 2s etc. I've been through the video a couple of times and can't find my error.
Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 11,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): czcams.com/video/xWFz-S96XGo/video.html Kind regards, Anders
Hi can we get the excel table column name, column count, column ordering and column data types using flow?
Hey Darsh. Thanks for the question and sorry for the late reply. At the moment, I’m on paternity leave, meaning I don’t produce videos and solve problems until the 23rd of August. Feel free to post the problem at my Discord, where many bright automation minds are gathered: czcams.com/channels/Pdtz4gd_iYebJFYq9N8pWA.htmlcommunity?lb=UgxD1Qe9lWKL1I7HyAJ4AaABCQ Kind regards, Anders
@3:10 - Please note: Special characters """, "%", "&", "\", "?", "/"".". are not allowed when naming elements.
I tried to name it to "Zeilen in blauer Tabelle auslesen/auflisten" and was wondering about the error message when I tried to save the flow at the end.
Hey Klaus. Thank you for your clarifying comment. I always tend to avoid using special characters, when naming elements. I'll always stick to the camelCase. Kind regards, Anders
@@andersjensenorg camelFall- never heard, but learned something new again) :)
@@klausmassler6262 It's not of great importance as long as you stick to the same regime 🙂 Kind regards, Anders
Hye, could you pls help me, it will take a minute for you but am struggling from last week, in first usecase, if value math then add row, you are matching with equal, i want to match with contains and wherever match give total count of match in a column, can you please help me🙏
Hey Shubham. Thanks for the question and sorry for the late reply. At the moment, I’m on paternity leave, meaning I don’t produce videos and solve problems until the 23rd of August. Feel free to post the problem at my Discord, where many bright automation minds are gathered: czcams.com/channels/Pdtz4gd_iYebJFYq9N8pWA.htmlcommunity?lb=UgxD1Qe9lWKL1I7HyAJ4AaABCQ Kind regards, Anders
can i compare it inside just 1 table then send a email that the data is already existed?
Hey Mayomi Thanks for writing. I'm getting more than 50 questions/comments every day and having difficulty answering everyone (I try my best). I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: czcams.com/video/_QRI-Yo2h8U/video.html Have a great day. Kind regards, Anders
Hi,
I am new to power automate. I have a main course table as shown below
Course First Name Surname Email Address
Then I have the 3 course tables
Physics
Course First Name Surname Email Address
Chemistry
Course First Name Surname Email Address
Biology
Course First Name Surname Email Address
So I want to do the following
1. Trigger a flow once a student registers using our registration form to send the course, name and email address of the student to the main course table (This is working fine already)
2. Loop through the main course table and the other tables (Physics, Chemistry and Biology) and once the course in the main course table matches the name a table, it should copy the details of the student from the main course table to that table
How do I go about number 2 please?
Hey EHMETH
Thanks for writing. I'm getting more than 40 questions/comments every day and having difficulty answering everyone (I try my best).
I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: czcams.com/video/_QRI-Yo2h8U/video.html
Have a great day.
Kind regards,
Anders
Kindly show VLOOKUP in PA Desktop
Hey Swaraj Chhallani
Thanks for writing. I'm getting more than 40 questions/comments every day and having difficulty answering everyone (I try my best).
I've created my Microsoft Power Automate Groups, where we all can post and solve problems or hang out around our favorite tool, Power Automate. So you're very welcome. Get the addresses here: czcams.com/video/_QRI-Yo2h8U/video.html
Have a great day.
Kind regards,
Anders
Your power automate video is very helpful, I successfully created a flow to sync 2 spreadsheets using your method when I do a simple table. But when it comes to the real thing, my 3000+ entries file, it just won’t work. Would you happen to know why? I have already turned on the Pagination and set to 5000. 🥲
Hey Diana. Thanks for the question. At the moment, I’m on paternity leave, meaning I don’t produce videos and solve problems until the 23rd of August. Feel free to post the problem at my Discord, where many bright automation minds are gathered: czcams.com/channels/Pdtz4gd_iYebJFYq9N8pWA.htmlcommunity?lb=UgxD1Qe9lWKL1I7HyAJ4AaABCQ Kind regards, Anders