Working with complex data types - Power Automate Tutorial

- [Narrator] It often happens when trying to automate different kinds of processes that we come in contact with data of different kinds and formats, may that be data that is simple or complex in different kinds of formats. We have to learn how to manipulate this data in the best way so that our processes run smoothly and efficiently. In this video, we are going to learn what kind of actions in Microsoft Power Automate Desktop are going to allow us to work with complex data types. And by the end of this video, we are going to learn how to handle different data types and structure and therefore create more powerful and efficient automations. There are three kinds of data structures in Microsoft Power Automate Desktop or actions that allow us to deal with three kinds of data structures. And the most easiest one is lists. Lists are just ordered collections of items and items in a list can be of any data type, including other lists itself. Also, we have tables. Tables are made up of rows and columns and each cell in a table can contain the value of any data type and the most complex data structure is objects. Objects are a collection of named properties. Each property in an object can contain a value of any data type. We have learned when to use actions for different kinds of data structures, so when do we use lists? Lists should be used when we need to store a list of items in a specific order, such as a list of steps in a process. Also, we need to use lists to store a small amount of data that we need to access quickly. Also, there is plenty of different actions that allow us to manipulate and work with lists efficiently and quickly without having to do too many actions. And remember, the less actions that you put in your automations, the less debugging you are going to have to do in the end. Let's dive into Microsoft Power Automate Desktop and see how to use and work with lists. If we want to work with lists, it is very easy to access all of different actions that we have for lists. We have type in lists or search for them under the tables and variables, and we can see all of different kinds of actions that are going to allow us to manipulate data that can be in list format easily and effectively. We have clear list, remove items from list, sort it, shuffle it, merge it, and so on. It is important to know that we don't have to always create a new list starting from scratch, but Microsoft Power Automate Desktop creates lists automatically for us when we create different actions. In this process right here where we did find and delete empty files in our computer, lists are created when we add the action of get files in a folder. If we click on this action, get files in folder, we can see that there is a variable that is produced. This variable is called AllFilesinFolder. If we click inside of this variable right here, we can see that this is a list of files, so this data type is of lists. Therefore, we can work with all of the different kinds of actions that we have in Power Automate Desktop. To be sure that this is a list, we can only see that there is one column right here that is the item, and lists are just a list of different things in an ordered process. So this is the order of which this list is ordered, and that is very important now because we can change different order and access items based on the order of that list. We can also loop through the items inside of a list with a for each loop. So we create a for each loops and we loop through the variables inside of that list. If we wanted to access the current variable inside of this file, all we have to do is just access the current file. If we're trying to work with data tables in this example, it will be more complex and we will see that in another example. We can also create our own list. To create a custom list that we might want to use to store data that we extract from a webpage, from some text files, and so on, it is also very easy. In this process right here, I'm going to get all of the files inside of a folder, create a new list, go through all of the files that I have in this process, read the text that is inside of this file, and add this text to the list. Let's run this process and see what happens. So the process is running and it's adding items to this list right here. So in each one of the files, I just have Test1, Test2, Test3, and it has read each one of these files and it has created a list of values. This was done by adding a list action. So we added a create new list, created a new list. We could add a name, and after that we just add items to that list that we got from the read text from file, and that's how easy it is to create lists in Microsoft Power Automate Desktop. Remember that you should create and be using lists when you have simple data structures and data types that you want to access very easily or that you might want to work with in this format. Also, it is easier to create loops on lists than it is on data tables. When we need to work with large amounts of data, we would use tables. In Microsoft Power Automate, tables are managed with the action of data tables. We need to store large amounts of data such as, for example, a customer database or a product backlog and we are extracting this information from a webpage, we would use data tables. Also, when we need to work with data that has to be put into multiple columns, we would use tables or data tables in Power Automate Desktop, information such as, for example, the name of the item, the price, the description, and any other information that we might be wanting to extract and manipulate. Also, when we need to perform complex calculations on data such as sorting, filtering in, or aggregating that data, we would use and put that data into data tables. Let's dive into Microsoft Power Automate Desktop and see how to work with data tables. Data tables like this can be created automatically by using different actions from Microsoft Power Automate Desktop. One great example of this is using the extract data from web page action. This action that extracts all of the information from a specific table or from a webpage can automatically decide if to put your information into a list or into a data table in the OutputData variable. If you are extracting more than one column, it will create a data table. If you're extracting only one column, it will create a list. How do we know that? Just like with lists, we would click on the variable that was generated automatically and we will see that this variable is of a type of Datatable. Now each column right here can be a different value, and each column represents something that we are extracting from a webpage. We are sure that this is a table also because it has multiple columns and multiple rows, unlike lists that only has one column. Sometimes in our flows, data tables might not be enough to work with the data that we have, so then we have to use objects. If we need to store data in a structured way with named properties, we would use objects, also, if we need to store nested data inside of a structure, or for example, convert a JSON file that will be, we will be receiving from a application or downloading it from a file to be used within our automation. Let's see this together with lists and data tables, how it can all work together. In this process right here, I am going to simulate that I am receiving a data file that looks something like this. This is a common JSON file that has books and title fit. It has different attributes for this book, the title, the author, the publication here, genre, the ISBN, and the price. And we have different titles and different books. Now if the data is like this, it is completely unusable by me. I cannot do anything with this data inside of Microsoft Power Automate Desktop except convert it into something that I can work with. Now this right here, we are going to convert this into a object that we can work. After that, we are going to create a new list and create a new data table. We are going to take different items from this object and put them inside of the data table and inside of the list, inside of the loop that we create, Let's run this process and see if it works. So the first action is going to set a new variable that is going to be all of the different items that we have inside of our JSON file. And then we're going to loop through all of these items. Now I want to bring your attention to a couple of things on this process. The first thing is that right here we have our custom object, the custom object, it says right here, when we double-click on the action that was created from the convert JSON from custom object. We can see that there is different items inside of the value, the first value of books. And that is just like the structure of our JSON file. We see that it has title and all of the different values. Now if we click inside of this list, inside of this object right here, we can see that there is different attributes that we can access. Now why is this important? Because if I call for this genre or for the author or for the title, I can access this value or this value or any other value whenever I need inside of my process and perform different kinds of calculation with that value. I can add, subtract, do whatever kind of checks, controls, if statements, and so on. The important thing is that I need to know how to access this value. There is a little observation right here that says ['books'] in square braces and then again, [0] in square braces. That is how we would access that index. But for example, in our loop, if we wanted to cycle through all of the books, or for example, all of the links that we extracted, we can access this JSON object and access the books from in that column, in that action. So we are just going to loop through all of the books. Inside of our loop, we have a insert row into a table, and this is going to create a new date inside of our new data table. We are going to insert only the title. So for each one of the books, we are going to put only the title inside of our data table and the same thing for the list. Inside of our list, we're going to do the same thing. We are going to add a simple list and add only the title. After that, we can perform any kind of other actions on these data tables or on the list that we have created. If we take a look at the list that was created, we can see that there is a list of different text values, which are the names of the different titles of books. And if we look at the data table, we can see that there is rows and it's not a list, it's a data table, and we have the different names of the different books as well. Working with data tables is very important and also working with the best action for the data that you are working with will make your processes and your automation much better and much simpler for you to work with and therefore debug in the future. A couple of tips to leave you off with this video would be to use descriptive variable names when you are creating different data tables, and also leave comments to document what you are doing in the different steps of the process. Also, remember that lists are great for storing ordered data. Data tables are great for storing structured data, and objects are great for storing complex data structures.