Flow Alt Delete – Josh Cook [Microsoft MVP]

Tag: Append Array

  • Power Automate Expression Union – Return Unique Values

    Power Automate Expression Union – Return Unique Values

    This blog will be looking at an expression called union() This expression can be used to remove duplicates from 2 collections. In this example, I will be using an Excel file to demonstrate this. But this can be done with any data

    The Scenario

    I will be taking an Excel file that has many Paper Items.

    We want to remove all the duplicate items from the Plastic Item column. Why would we want to do this?
    One reason may be that we get this file from a 3rd party, but we only care about the unique items to import into another system (CRM, CDS/Dynamics, SharePoint, Etc.)

    Steps

    First we want to add an Initialize variable, Type = Array
    Next, we need to get the data we want to use. In this example I am using List rows present in a table

    Now we add a Apply to each loop, and use the dynamic content value from our Excel action
    Inside the Loop we add a Append to array variable action, and add the column we want to remove duplicates from. In this example Paperitem

    We are done inside the loop..

    Outside the loop we add a Compose action, this is where we will put the expression union
    In the Expression tab type union()
    Select the dynamic content tab and pass the array variable to union twice
    (Click your variable comma click variable again) Your expression should look something like this:

    union(variables('paperItemArray'),variables('paperItemArray'))

    Done!!

    Final Thoughts

    Now we have a Array of all unique Plastic Items, which can be imported into another system based on your needs.

    Thanks for reading!


  • Authentication on Microsoft Forms

    Authentication on Microsoft Forms

    Microsoft Forms is great for many use cases. However, with some use cases you may not want the whole organization to have access to this form.

    Summary

    In short, we want to setup a way to allow certain users to fill out a MS form.
    How can we achieve this?
    My Flow below will show how to take a list from SharePoint that has users emails, to only allow them to process the Form.

    ** Note – You do not have to use SharePoint, you can use any means necessary. **

    Setup

    First you will need to have a Microsoft Form created – Any MS Form will work

    Next, we setup our List of ‘Authenticated’ Emails. Again in this demo I am using SharePoint
    I created a simple SharePoint List with a single text field to store email addresses

    Steps

    In the Flow, we create as normal with a Microsoft Form Trigger and action to get the Details
    Trigger – When a new response is submitted
    Action – Get response details
    Next add a Variable, we make the Type an Array
    Under the Variable, add Get items action, and choose our Authenticated users list that we created above

    Next, add an Apply to each loop. Select the value from the Get items action.
    Inside the loop, add a Append to array variable, select the variable that we initialized above. Value = the column name that stores the email Address in our SharePoint List

    ** Note – Make sure the Initialize variable is type Array **

    Outside the loop – Add a Condition
    Select the Array on the left side – Contains – Responder on the right

    — We use Contains to check if the User filling out the form is in this Array of emails —

    If the user who is filling out the form does not belong to the ‘Authenticated’ list, we Send an Email letting the user know they are unauthorized to fill out this form.
    Under the Send an email action, add a Terminate action with Status of Success, this is to ensure the flow stops running, while not throwing an unwanted Failed run

    Here is what a User will see when they do not have access

    Conclusion

    I am sure there are a million ways to add your own intuitive Authentication methods.
    I hope my method helps or inspires someone else!
    Thanks for reading