Dynamically Create Excel Table with Power Automate

hey everyone Jonathan Silva here with pragmatic works for this video what I'm going to use is power automate to dynamically create a table in Excel let's Jump Right In [Music] so here you have my Excel table it's really not a table action at all it's just a worksheet here it's a range that we have that I'd like to create a table so I'd like it to automatically when I either add this table to my SharePoint document Library when I get this from someone else to automatically create a table on it maybe when it's modified something right the trigger doesn't matter here but I like this to be formatted as a table automatically so the first thing is you can see my range that I have I have three columns and seven rows now the first row here is our header so we'll keep that in mind with our table range but what I'd like this to be is dynamic so no matter if I have seven rows or 70 or 7 000 in seven rows I want to make sure that all of the data within the rows that I have are contained within that table also a thing to note is how many columns we have because that's going to be important here for us to understand the range that we have within the table that we are formatting you can also see I have it sorted in a share Point document Library once again if this is in OneDrive there's really no difference between the different actions here you're just going to point to the other connector within power automate itself and then we can come over to our flow and so far I've started off with my trigger or an item or file is modified here in SharePoint again this could be something else when it's added it really doesn't matter it's going to be the same process here the one thing to note is make sure you have the site address you have the library name in this case and then the folder that we're pointing to in this case the folder if we're going to the general documents here within SharePoint is just going to be the shared documents it's the the general folder we have there so I'm going to select that then the next step we have here is to go ahead and create the table for Excel so I'm going to add a step here in this case we're going to choose Excel online our business version there go ahead and then grab the create table action now just as we did earlier we're going to go ahead and match up our location library and the file together so let's go and find the location again it is crucial that we go find the SharePoint location for that exact area and mine is my testing site there it is we're going to go to our document Library there's our documents and the file now this is really important this won't work it'll fail right here if you don't match this correctly so go ahead and put in your forward slash and what we want to do is use the dynamic content for file name with extension so we're going to come down here within our Dynamic content and we're going to find that from our trigger so file name with extension okay that's exactly what we wanted to have here we need that in order to point to the right file and this is the dynamic operation here we're not choosing just this exact file but any file that has been modified modified in this case we're going to go ahead and utilize that one and create a table there so it's not just the one we're pointing to now for the table range this table range we're going to get from Excel and we're going to use the offset formula in Excel in order to grab that here so I'm going to go ahead and copy and paste that I have on the side and I put this in the the description below as well this offset formula is pointing to sheet number one that is the default name for our worksheet here you can see that's here as well if yours is renamed and has spaces you're going to need to make sure you go ahead and add in your single quotes that surround that table name in order to properly carry that okay the other part is the most crucial area is right here this final number this final number here designates the number of columns that you have within your range and the table that you want to create okay right here it says 10 but if I take a look at my Excel worksheet here I only have one one two and three therefore I need to change that value from 10 to 3. that's going to ensure that it's going to properly match up to what I want to have finally within the table name if I want I can just add in sheet one here as well so I can point to that you don't necessarily have to do that you can leave it blank unless you want to name it something different within the column names you can go ahead and add in column names as well if they are not already there however I already have some in here okay name favorite color and state so therefore I'm just going to leave that blank now what we can do is go ahead and test this so I'm going to select test here we're going to do a manual trigger save and test and go ahead from here and see what this looks like so we're going to wait for this to start spinning and we can go ahead and start to make a test in fact what I'll do is I'm just going to rename this this should work for me if I rename this accounts data for today and rename that that should give me a just modify just now and now what I can see when I run go to my flow is there we have it the flow has run successfully when an item or file is modified we're creating a table there is our table that we're pointing to correctly there if I go now and check that Excel file I should see a table created automatically here that's now waiting there there you go there's a proper range as well that's waiting for me to work with I can come in if I'd like to do any filtering we now have the filtering opportunity here because it is a table it gives us all of the great information that we now have a table working with here in Excel thanks for joining me here again today working with power automate in the cloud and Excel and how we can dynamically add in a table to any Excel file that we want to point to whether it's in SharePoint or in OneDrive as long as you can get to it it's available to use thanks again don't forget to go ahead and drop in a like And subscribe below to get more content from myself and all of us here at pragmatic works thank you [Music]

Channel: Pragmatic Works

Views: 13,315

Keywords: power automate excel tutorial, power automate mail tutorial, power automate excel mail, power automate excel guide, excel power automate, microsoft power automate excel, microsoft power automate excel mail, microsoft power automate excel tutorial, power automate, excel, how to add rows to excel, add rows to excel, create table in excel, create table in excel power automate, excel and power automate, flow add columns in excel, flow create table table range, Jonathon Silva

Id: o5wxLrD8BZc

Length: 6min 37sec (397 seconds)

Published: Wed Aug 02 2023