Power Automate Compose, Join, Select, Filter, Create tables, Parse JSON | Data Operations in flow

hello everyone raza here in this video we will cover the power automate data operation actions actions like compose select filter arrays create table create csv pass json these data operation actions allow us to manipulate our data when we are creating our flows every flow maker must be aware of these data operation actions i will show different scenarios in which you can leverage these actions so let's get started with the video [Music] data operation actions in power automate are available to manipulate the data when you create flows i will create a new instant cloud flow so we can learn the different data operation actions if you search for data operation and select the data operation group this will list out all the data operation related actions in power automate the compose action use the compose action to save yourself from having to enter the same data multiple times i will select compose and the only input that it expects is a property called input inputs is where i can define the value for my compose action so we can pick dynamic content as input to the compose action or we can manually enter data in this case i've simply typed in my name the beauty of compose is no matter what input i provide it will automatically understand the data type here the compose action has a string value best practice always ensure that you rename the compose action to something meaningful so that when you want to leverage its value later on in the flow you can easily remember the compose action by its title i'll call it name i will add another compose action this i will make it an array of numbers i will rename this to array of numbers if i was to save the flow and test this flow manually when the flow runs the compose action called name the output is a string whereas the second compose action which is an array of numbers the output here is an array the variable action and power automate let's pick initialize variable these are strongly typed you have to define the type that you would be providing for this variable throughout the flow compose you can think of these as static variables when you define its value once and it stays the same throughout the course of the flow whereas a variable is one that you can change during the run of the flow here i'm initializing a variable called flag which is of type boolean and i set this to the expression false to begin with and later on in my flow i can use the set variable action to change the same variable to a different value let's say true i have added another compose action that has a list of email addresses the join data operation this action allows us to delimit an array with the separator of your choice my compose action here will generate an array of email addresses now if i want to combine the values of the email address array and delimit them for example with a semicolon the from property for the join action expects an array now email addresses was my compose action that has that array of information so i'll select this and i would like to join this with a semicolon let's test this out here is the output of the join action which is email addresses semicolon separated i've created another compose action that holds an array of objects let's look at the select data operation action this action allows us to transform the shape of the objects in an array we can add remove or rename elements in each object in an array so if i pick select the from property expects an array so let's give it the array of objects that i created by leveraging the compose action and from here i can start selecting the data points for which i would like to create another array these are key value pairs let's say i only need an array that has the name property of this array of objects to get that i call this title as my key and the value i can go to expression and the expression function that will give us the context of the current item of this array is called item we'll put a question mark and under square brackets under single quotes we need the name of the property for whom we need the value my property here is called name so i will call it name this is case sensitive i'll click ok this select action will create another array but that would only include the name property values with a new key called title let's test this out let's go to select the input is my original array that has these objects that have multiple properties name image and data function and if we look at the output the output is an array which has that key that i defined called title and the values are coming from the name property of my input array i would like to also show the data function associated with it right in the title itself and to do that i will select right in the value section right after providing the item name i will again type in an expression which is item of data function so it will put the name and the data function let's test this out my flow has run here's the output of select powerapps act power automate automation this is technically concatenating the name and the data function properties of my original array with select you can add remove or rename elements and change the shape of your data but you cannot change the number of objects in the array to do that we have a different data operation action and that is known as filter array the from property expects an array i will provided the original objects array and then i can define my filter criteria my filter here would be item of name so i would like to filter on the name property of the array let's say filter all the results what the name is equal to power apps this is case sensitive if i run this flow filter array the output would be an array that only contains those objects where the name is equal to powerapps and i only had one item at my original array that matches that condition and the conditions here that i can define aplenty let's say filter this where the name is not equal to powerapps so the output of the filter array action now would be the objects that do not include that specific name we have the create csv table and create html table data operation actions both of these expect an array and it will provide either a comma separated value table or it will output an html table let's start with create csv table from i'll provided the output of my filter array on similar lines i will leverage the create html table action and provided the output of filter array let's test this flow here is the output of create csv table we can see a table that has comma separated values these are my keys in the array of objects and these are its values for create html table the output here is an html table that has the columns and the rows we also have a parse json data operation action this expects content in the form of json here i have my compose action that has an array of objects so for the content property of my parse json action i will provide that and schema is the schema for that json object that you have provided now the beauty here is that you don't have to work to create that schema if you have the data associated with that schema like i do in this case i can copy this and for the schema property i can go to generate from sample and all i need to do here is provide the sample payload for that json content that i'm providing to the parts json action so i'll paste this in here click done and it will generate the schema for me the advantage that parts json gives us is if i add subsequent actions and if i look at parse json it will give me the property values associated with that json object so for example data function so if i select this it will put a for loop around it purely because my original json content is an array so if i'm trying to point to a specific value in an array it will apply the loop and then point to that specific property now let's try and use these data operation actions in specific scenarios one of the most common scenarios is around approvals i'll create an instant cloudflow and i will use the start and wait for an approval action the approval type that i will select here is everyone must approve i will call this my sample approval action and assign 2 is where i would like to define the names of my approvers semicolon separated now let's say i would like to get my approvers from a microsoft 365 group so for that in the office 365 groups connector we have an action called list group members here i can pick my group name cat team however list group members will output an object that has a lot of other pieces of information related to the user assigned to in start and wait for an approval only requires the email address so what i can do here is i can leverage the select data operation action from this needs to be an array so i will use the value property of list group members and for the map property i will switch to text mode and here i will use the mail property this will have the email address of the group members select returns and array i need to join them so i will use the join data operation action from would be the output of select and i would like to join these by semicolon so now if i select in here i can pick the output of the join action i'll save my flow and i will run this flow manually the flow has triggered list group members if you look at the output of this the value property is an array that has the information of the users who are members of that specific microsoft 365 group however i needed only the email addresses so i used the mail property in the select action so the output of this select action is simply an array of the email addresses and then the join action i leveraged so i can join the array that has those email addresses by semicolon so reza sarah are the members of that group so all of them would have now received this approval action here is the approval email that reza has received called sample approval the action is everyone must approve so reza approves this provides us commands and submits as response here is the approval email that sarah has received and let's say sarah rejects this and provides her comments and submits her response the moment the responses are received the approval action has completed if we look at the outputs of the approval action there is an array here called responses that has the response that each approver has provided so here is sarah's response the date and time at which sarah responded sarah's decision the comments that she entered so let's leverage this array to create a nicely formatted html table that includes the responses that are provided right here and we will even transform that data and send it out back to the requester i will leverage the create html table action from the start and wait for an approval action has this array property that's called responses so i will select this now this will create a table however i would like to define the columns in a custom manner and these are those same key value pairs very similar to that select data operation action so i would like to give the approver name now notice here i would like to give a space in the header name of the table not allowed to do that but if i go to expression and use the string function i can provide a space right here and click ok and then the value i can simply pick the dynamic content responses approver name the approval decision here i will pick the approver response commands that is responses commands now the thing about commands is that it is not mandatory so it could be possible that the approver has not provided any commands and in that case when i output the html table i would like to provide the value n a meaning not applicable the beauty of these data operation actions is i can change the shape of my data so for responses commands if i hover over this you can see the expression right there item question mark commands so now i will use expression coalace which picks up the first non-null value the first argument would be item of commands so if the command is there pick that if not then place the value n a the approval response date and time so i'll use the function string and my string value is response date and time and the value that i would like to provide here will come from responses response date now the expression here is the response date this is case sensitive power automate works with dates and times in utc format so this html table that would be generated would have the value of the response date in utc format and this is something that i would like to change to do that i'll note the expression behind this action which is item of response date i will go to expression and use the expression convert from utc the timestamp would be item response date what is my destination time zone i'll pick eastern standard time and what is the format of my date that would be month day year r minute and whether it's am or pm i will close this and that completes my expression and now i can leverage the send an email action to send an email to reza subject here kept it approval response and in the body of the email i will use the output of create.html table since this is an html table i can also provide some styling to the email for that i will use the compose action that i will rename to style and in here i will provide some style attributes for my table the styling information now i will provide it to the send an email action let's test this flow the flow triggers here's the email that reza has received reza approves it provides his commands click submit here is the email that sarah has received let's say sarah rejects it without any comments and submits once these actions are recorded the flow will move ahead generate that html table and send out the email and here is the email response that reza has received it's a full summary of the approval action reza and sarah were the approvers here are their decisions here are the commands sarah did not provide commands so it shaped the data and it provided an a and here is the response date and time converted to the eastern standard time and also formatted in a readable fashion here is a flow that runs on a weekly schedule connects to a sharepoint list called servicedesk and filters the items where the status is equal to pending i currently have four items that are pending as part of this list so i've leveraged the create html table data operation action to provide the value which is dynamic content array output of the get items action and here are my key value pairs i've simply picked the columns that i would like to export and here is my send an email action to send out that html table let's test this flow here is the email that i have received this includes those four items that are in pending status from my sharepoint list power apps triggering power automate here powerapps is sending the dynamic query over to powerautomate and also defining the individual who the report needs to be sent out to we are leveraging the get items action to query a sharepoint list based on the query that's coming in from powerapps then we create an html table based on that data the output of html table can be leveraged to create an html file in onedrive then we can use the convert file action in one drive to convert that html file into let's say a pdf file here is a data operation action once again to create a csv table and finally the flow sends an email in that email we have the html table body plus we have two attachments one is the csv file the content for which is coming from the output of the create csv table action and the second is the pdf file the one that we generated by using the convert file action in onedrive here is the powerapp in action that is connected to that flow the user has refiners of filters that they can leverage to filter the data so show me all the tasks in the system that are completed and right here if i click export it will go and trigger the flow and once that flow runs here is the email that raza has received it has the tabular data based upon the query which was the progress of my items are completed here is a csv file that got generated thanks to the create csv table data operation action and here's the pdf file that got generated here is another example of a report that goes out based on a manually triggered flow where the user can dynamically select the statuses of the items that they would like to export as part of the report this technique you can leverage with any data source of your choice sql database sharepoint i have a variable that i'm initializing of type array the user can pick multiple statuses for each of those statuses i am querying my data source to get those values then i am using the select data operation action to shape my data you can see i'm generating anchor tags i'm formatting date times i'm even writing expressions to change the font color then the output of select is what i leverage to create an html table all of this information i am plugging it into that array variable and finally when i am sending the email i am using the join expression to join all those array values i would send the email to reza the statuses that i'm interested in are available and in repair i will click run my flow the flow has run successfully and here is the email that i have received i have the status and the number of items in the status here is my html table i even have links to those specific items in my data source document review approval process i will upload a document for review here is the document that i uploaded i have a flow that triggers when a new file is created in this document library then for my approval process my approvers are being defined dynamically from a sharepoint group sharepoint group is called document reviewers james reza and sarah are a part of this sharepoint group in my flow i query that sharepoint group use the select data operation action to get their emails then use the join operation action to join those email addresses by semicolon and i'm providing that output right here so when the flow triggers it dynamically picks the approvers and assigns the approval task and once the approval decisions are taken i use the filter data operation action to filter the responses where the reviewers have said yes versus where the reviewers have said no and all that information is what i keep logging back in my system of record if you enjoyed this video then do like comment and subscribe to my youtube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 116,416

Keywords: power automate compose, power automate select action, power automate filter array, power automate filter json, power automate filter json array, power automate filter json result, power automate create html table, power automate create csv, power automate parse json, power automate parse json array, flow, power automate, reza dorrani, flow compose, flow compose vs variable, flow select action, microsoft, flow data operations, learn, compose, join, filter, select, actions, html, csv, json

Id: qxFx0hqJxj4

Length: 25min 10sec (1510 seconds)

Published: Mon Aug 29 2022