For the uninitiated, Microsoft Flow is an amazing low-code tool for automation built into the Microsoft Office suite. It is simple and can often help end users develop powerful automation in plain English steps. In my case, I recently had a list that contained a column that I needed to extract. The challenge with this was the column had duplicate values and my new list will not work if it has duplicates. This presented a little extra challenge but Flow is able to do it no problem.
The simple logic here is we want to check each item in a list against the new list. If the value exists in the new list, skip it. If it doesn't, add that to the new list and move to the next. The first thing I had to do in my list is set a flag on whether or not the value had been processed by my flow. Since my original list was more than 100 items long, this flow would have to run more than once to capture every item. I recommend using a Yes/No field and I'll call that field isOnList in the example. This will let us filter the original list and will be the way we tell our flow to not add the value to the new list.
Once that's created, set your flow on a timer. A long timer. I use a day. You can manually trigger scheduled flows, so anything longer than a few minutes works. After you have that set, we need to get all the items in our list. We'll use the "Get Items" action. Enter your site address and list name and click on the advanced options button. We'll add the Filter Query isOnList eq false in order to grab only the items we haven't processed yet.
Our next step, is that for each row (Flow calls this Apply to Each Value), we are first going to get the new list and compare the value to the values on the new list. This is quickly achieved by creating a filtered array. The action Create Filtered Array uses the value of the original list to filter the second list items to create a new array.
The next logic step checks to see if the array we created is empty. If it is empty we'll create the value and mark our item as processed. If the array contains any values, we're going to skip adding it to the list and just mark the item as processed. To check the array to see if it's empty, we have the condition empty(body('Create_Filtered_Array')) is equal to true. Use the expression bar and dynamic content to get the proper syntax for the if statement and use the expression bar to get the true function.
Once you have that condition set, simply follow the rest of the steps and you're off to the races.
I had to run this flow three times for my 200-some item list. The max number of items the flow will process at once is 100 items, so you can do the math there. Using the OData filter at the beginning means we're not going to have any duplicate efforts on Flow's behalf.
If you have any questions or suggestions, please let me know, otherwise happy Flowing and enjoy your de-duplicated list!
Comments