Export Dataverse Table to Excel Using Power Automate Flow

hello this is again i'm here today to talk to you about how we export data versus tables to excel with power automate the use case for this is just to have an extra backups it was one of the requirements for me and to have exterior backups so we start by going to our solution here that we have and we have those tables and today we're going to work with the order items tables so i have i have the main columns for this table which is the item id i have the item flavors i have item price and i have the order number column which is a lookup column that look into uh the orders table that we have not all the columns that we need to export and we will talk about this more once we start doing the power automate flow the main columns that i'm looking for is the item flavor which is a choice column i have the item id i have the item price and i have the order number which is the lookup column so i have the lookup column which is the order number and i have the choice column uh which is the item flavor next step we'll start by creating our power automate cloud flow and it will be of a tie scheduled uh flow and we'll go to the flows in our uh power environment we will start by typing the flow name we'll call it dataverse to excel backup and we'll have it start uh next day at 10 a.m uh the interval will be uh once a day which is a daily uh recurrence for this flow and again you can you can set up what time it will start the first time um the next action we'll use in the flow is to list the rows from that dataverse table which is the order items uh so we will select the less rows action then we will have to uh get that table select the table and then one thing here is the default row count that will retrieve is five thousand if you have more than five thousand you can you know put the number there i will just put 10 000 here and then the next step will be to create the csv file uh or csv table and here uh we will take the output value from the previous action which is list rows so we'll get the less rows value as the input for the create csv table and we'll leave the columns for now automatic so we'll just generate these columns from the less rows values automatically and then the next action after the create csv table is to create a file from that csv table uh the the path for that full file is going to be one of the folder that we have it's i call that folder dataverse backup uh the file name i will i will give it the table name uh dash and then i will use expression the utc now which is just given me a unique identification which is the current time and date when that flow run don't forget the dot csv so you need to have that extension there so you can have you can you can be able to open this file the output will be the the file content will be the output of the create csv table let's run and test this flow and see how that look like it's running and we'll create the file now um successfully and then from here we will go to that folder location that we have in the onedrive we'll do just a hard refresher just to retrieve the file that was just created in this folder and we'll go ahead and open this file as you can see all these columns it's a system columns that was created in addition to the other columns that we need in this table so let's do a quick formatting for this table to take a clear look on to these columns that we have here and as you can see it's just like too many system columns that you might not need all of them and uh that's why we're gonna go to do another step which is creating a custom columns in our create csv uh table action in the flow here i have two columns that i i need to highlight which is the i have the lookup column and the reason for that that we will use actually the header of this column as a metadata in one of the expressions the other column that is going to be the choice column uh the same thing i will highlight this and i will i will show you exactly why i highlighted these two now let's go we need to only generate certain columns we're not going to use the columns automatic we're going to use actually custom and the first column will be item id the value as we can see we get it from the list trials action previously before the create csv table the nexus next column is going to be the order item the order number and in the order number we will use the expression function item because this order number we have here this is actually the good value of that lookup column so this is not going to be the value that we're looking for which is actually an order number to do that you come here and you take the header file which is a metadata it's actually a value we take this copy and we use it with the item function in the expression as we can write here item open and close brackets question mark square brackets single quotes and put that value of that column header the same thing will do for the item flavor it's going to be the same thing and to get the item flavor not to get the item value in the choice which is usually a number when you create the choice we need actually the value or the label which is you know the flavor banana chocolate strawberry these things the same thing we are taking the header column value and using it with an item function expression in the expression use an expression to have an item function the same thing item open bracket question mark then square brackets single quote and put this value of the header column there the last column that we would like to get out of this custom columns is the item price we'll look up for the item price again this is coming from the list rows actions previously and then here we are ready to go ahead and save this flow and run a test and let's see the result that will come out of this flow at this time i'll go ahead here and run the flow done it's running all actions were successfully we're waiting to create the file here you go we have the files being created then we're gonna go back to the our folder where we have this uh onedrive folder we have these backups i will just refresh to get the last file that was created let's open this file and when i open this file you can see that we have only certain number of columns this is exactly the columns that we customize or we requested to have in this table and uh we'll do a real quick formatting for this table to see those columns and this is something that we can use let's say that we have some kind of data loss happen or corruptions i can go ahead and bulk delete all the data from the data versus table order items and then use data flows to load this data into that table uh without any issues this is all the needed if you have more columns that you think you might need you can always add them here and just to have like a cleat a clean kind of learning columns you don't have all these system columns that you do not need uh but you know it depends on use case you might need some other columns that you know based on the system like owners organization and these kind of things uh and and that's it this is how you export nx data versus table to excel and make it a daily backup uh that you can actually save anywhere you would like save in our case we are saving this in a onedrive folder and again recurrence it's once daily you can set up the time i hope you enjoy the video thank you

Channel: Ahmed Salih

Views: 12,831

Keywords:

Id: 626sEDwgrYM

Length: 9min 31sec (571 seconds)

Published: Sun Jul 31 2022