Power Automate Tutorial: Detecting Duplicate Rows in Multiple Data Sources with Power Platform
Vložit
- čas přidán 7. 08. 2024
- Welcome to my Power Automate tutorial, where I will show you how to detect duplicate rows based on common columns in various data sources such as Dataverse, SharePoint lists, SQL, and JSON. In this demo, I'll guide you through building an efficient flow that will enhance your workflow automation skills and streamline your data processing tasks.
Throughout this video, we will explore essential Power Automate expressions like concat, addproperty, sort, reverse and range. By leveraging these expressions, we'll manipulate and manage our data effectively. You'll also learn how to repurpose data using the select action, optimizing your decision-making process.
Join me on this journey to master the art of detecting duplicate rows in Power Automate.
Learn more about JSON here:
5 Challenges to follow and learn • How to write expressio...
Complex switches • Power Automate Switch,...
JSON Array Basics • JSON Array in Power Au...
00:00 Intro
00:39 Introduce the concept of detecting duplicate rows
03:39 The datasource containing duplicates
04:16 Build a robust flow using key expressions
04:38 Using Select to repurpose data
07:18 Sorting Data in an Array
08:05 Detecting duplicates with an ezpression
11:47 Identifying the rows to keep or delete
13:36 Deleting items by ID
14:09 The flow history
17:03 Outro
Whether you're a data analyst, business professional, or automation enthusiast, this tutorial will empower you to create effective workflows that eliminate duplicate data across various sources. Let's dive in and unlock the power of Power Automate!
Don't forget to like, subscribe, and leave a comment if you find this tutorial helpful. Stay tuned for more exciting Power Automate demos and Power Platform tutorials.
#PowerAutomate #Duplicates #JSON #Demo #Tutorial
Please buy me a coffee www.buymeacoffee.com/DamoBird365 ☕ - Věda a technologie
I don't think I've ever watched such a clear and thorough explanation! It works perfect!
Thank you Daniel, it's really appreciated when I get feedback like this. Make sure you have entered my competition for reaching 10,000 subscribers if you haven't already - details in the community tab of my channel homepage.
This technique have helped reduced process time rather than using loops. Thank you Big Time!
This was Fantastic! Absolutely perfect. Still have some issues to work out, but this was a huge jump start for me. Thank you for all you do. I'm going to check out your other tutorials in the coming weeks.
Thank you ! I was able to create my flow with the same logic 💯
Deduplication is pretty common use case. That was my first PAD script to trigger a copy to clipboard a list of unique items that were duplicated in the source file. Great demo as always. Such a time saver. Keep them coming Damien. Thanks for posting! #repurposeTriggered
😉 repurpose 👍
Great Video! Thank you for sharing, this knowledge is gold! Greetings from Colombia.
Thanks Juan. Hope it gave you some ideas.
I was learning how to remove records uploaded from Excel to List that's no longer in Excel, they used JSON, but I didn't get it. I am excited to watch your approach. I have subscribed to your channel. Thank you!
I hope you learn something, you’ve been busy today 👍
Hi, Great content. Do you have any steps that would update a SharePoint List when duplicate records are found?
Hi, Great Content. Do you have any steps that would allow me to update the metadata fields for those duplicate records?
Brilliant video for something I’ve wanted to use at work! Thanks so much! If you need an idea for a future helpful video, how about migrating data from Excel to Sharepoint lists? In particular date data, I’m always running into issues with date formats in excel not being recognised when creating items in Sharepoint lists!😅
Interesting. There is the date setting on the excel action. What problem are you encountering?
@@DamoBird365 thanks for the reply! Yes so Im aware of that in the advanced settings of the excel action and I’ve tried switching it to ISO8601 but still encounter issues. The columns in the excel table are formatted as dates. I’ve tried parsedatetime expressions, converting the dates to integers and adding them to 1899-12-31… I eventually get things to work but it’s by a long convoluted trial and error process and I’d much rather just be able to map a date formatted column in an excel straight into a date formatted Sharepoint list column if I’m not being over simplistic!
Thank you for the video and for the help! It rly helps me a lot. I would just like to know how to proceed with deleting duplicate items in SharePoint. When using Delete Rows it presents errors. Could you help me?
Hi, Great video.
Is there a way to get all the duplicate records instead of ignoring the 1st (ex: if there are 3 duplicate records, need all 3)
Question: how would you adapt this for a very large Dataverse table, say greater than 100,000 rows? Are there limits on those variables for holding all that data?
You might have to batch it. You could explore the chunk expression. I’ve not worked with volumes larger than this.
Hi Damien, not the first time you've come to the rescue for me. Do you have any suggestions if there are more than 100,000 records?
Probably dataflows? I’m not sure otherwise. Some volume!
Hi, thank you so much for the nice presentation of finding duplicates. As many of the people mentioned, your explanation about the topic was awesome. I have a query. I have a SharePoint List which obtain data from the Invoices extracted using AI feature in Power Automate. So how to find duplicate entries prior to save the Invoice data in this SharePoint List.
You might manage with just using union on itself to remove duplicates. This idea was born from an issue with the CoE duplicating data.
how would you go about this with comparing only 2 columns? Is the 'sort' function necessary?
Helpful vedio , Pls also make a vedio on how to migrate on primise data to cloud using power automate.
Hi, thanks for your suggestion. Can you explain what onPrem data you want to migrate? I don’t have a video on the gateway tool yet.
Love this flow, one request, I would like identify all of the duplicates as "true", I currently am showing only the sub value as "true" verse both as rows as "true". So I need both the row above and the row below to be true. Is this possible?
Could you add another key value where the value below is equal to this value and then filter on true for both values?
I think that would work, would you be able to copy the code that you would recommend?
Thanks for your video more helpfull for me
can you add how to add sql data in sharepoint
I can add it to a list but I don't have immediate plans for this - what is it you are looking to see / achieve via SQL? Azure SQL or SQL onPrem?
The map expression in select 2 is coming invalid and doesn’t working for me. Please help
You could post further details on the forum powerusers.microsoft.com/
Thanks for the video. How do you tie back for deletion? I'm trying to use the Delete a row function for excel and getting the following error when attempting to use the output for Compose 3 as my "Key Value" in the Delete a Row inputs. "
Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The action(s) 'Compose 3' referenced by 'inputs' in action 'Delete_a_row' are not defined in the template.'.'."
Should it be compose_3?
Yep🤦♂
Hi what should I use for the key column? The concat used in “select” or something else? Thanks
@@rikifrihcanal the Key should be the ID of the row.
Thanks for the great video! This has helped me a lot. I have rebuilt it 1:1. And it works very well! However, it deletes the "most recent" entry and keeps the oldest. I need it the other way around though. If it has duplicate records, it should delete the older ones and keep the youngest record. Do you have any idea?
This should delete the oldest. Have you sorted the array?
@@DamoBird365 Have tried everything! Have deleted the sort, put back in! It always deletes the most recent entries!
@@alvi_universe Just based on my own testing of this today (after finding this wonderfulness). You may want to flip the T and F from the KeepMe to the DeleteME and see what happens with that. It shouldn't be an issue, but it's something I had to do while figuring out a couple other things. I went off script and flipping those around solved the issue for me. Not saying it will for you, but it's possible. Before implementing it. I'd take out the Delete action at the end and then just look through the other out puts and see if it worked.
@@alvi_universe Have you found a solution to this of why it may be reading the most recent and not the oldest record I'm also looking to do that.
@@dangage8819 Do you happen to have another fix for this?
What if you wanted it to search for duplicates for the whole list? If the data isn’t that clean looking just one below will it be far enough.
If you cannot sort your data like my demo, you could use xpath to search? I demonstrate some xpath techniques in the following video czcams.com/video/EOmsT9KcWxc/video.html.
Hello, I have created a scheduled flow that runs monthly. I have two excel file in SharePoint, and I created the flow that will pull data from one excel file (let's say Excel 1) and paste the data to ther excel sheet (Excel 2). So, my flow is : Recurrence>List rows in a Table> Add a row in a Table. So for the very first month (January), it runs well, however when my flow runs for February, it also pulls January data again along with February data. How can I stop getting the data that is already pulled by the previous run? Please help me out.
You would need to filter. Not something I have a video for exactly. But either filter from list rows (preferably) or separately by filter array.
@@DamoBird365 Thank you so much for your prompt reply. I tried to filter, Unfortunately it did not work for me. Maybe I did it wrong. Is there anyway that you can help me with please? I am just stuck with it.
Can you please explain the subtract bit @11:22 in a leymans terms for me pleas, I do unstand but at the same time I don’t lol
Sure. [“object1”,”object2”,”object3”] …?[2] = object3 …?[1] = object2. Then if item() = 2, then [item()] = [2] and [sub(item(),1)] = [1]
@@DamoBird365 thank you!!
@@DamoBird365 but then how doesn't it error on the first item considering there is no item[-1] to compare it with?
Good spot, ?[-1] returns null, remove the ? and you get an error.
@@DamoBird365 you're right, the ? makes all the difference, thank you for replying
Hello,
Great video, I've followed each step but I'm getting an error on the 2nd select. Really appreciate if you could point me in the right direction
The execution of template action 'Select_2' failed: The evaluation of 'query' action 'where' expression '@addProperty(outputs('Compose')?[item()],'IsDuplicate', if(equals(outputs('Compose')?[item()]?['concat'], outputs('Compose')?[sub(item(),1)]?['concat']),'T','F'))' failed: 'The template language expression 'addProperty(outputs('Compose')?[item()],'IsDuplicate', if(equals(outputs('Compose')?[item()]?['concat'], outputs('Compose')?[sub(item(),1)]?['concat']),'T','F'))' cannot be evaluated because property '0' cannot be selected. Property selection is not supported on values of type 'String'.
I guess you’ve used an invalid array in the compose and it’s being treated as a string.
@@DamoBird365 Thanks for the reply, its the same as yours in the video. Although when i type 'sort' in the expression field of the compose its doesn't appear as an option (like it does for you), the only option i get is 'SortBy'. I can still use the expression 'sort(body('Select'),'SortBy')' but interestingly it only pulls back one value rather than the 100+ in the first select.
Sort is the correct expression, here are the docs learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#sort. I can’t explain your experience and maybe it’s worth sharing a screenshot on the forum.
@@DamoBird365 Thanks for your help. Delete the compose step and re added it and it fixed the issue