Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate

hi there folks in today's demonstration I'm going to show you how you can export data from powerbi to an Excel file so what makes my video different from everyone else's is this is actually an Excel file it's not a CSV and I'm not using an apply to each to write the data so where you might write tens of thousands of Records or rows from powerbi to an Excel file using apply to each and it takes 2 hours my flow will complete in about 15 seconds I'm going to show you two different meth methods using either the graph API or office scripts and I'm going to pitch myself against the traditional method they apply to each can I show you all of this before my apply to each flow completes will the graph or will offer scripts be faster so if you haven't already please make sure you like And subscribe and without further Ado let's see who wins the race to write data to excel so for today's demonstration I've set myself a challenge I have a flow pre-built that is going to add 20,000 rows from a Dax query into an Excel file so that Excel file already has a table and you're probably thinking this is not what I want I've seen loads of videos that do this well that's the point I'm going to run this flow and my aim is to complete this demonstration before this flow completes and then I'll show you the flows that run extremely efficiently you'll be able to write 20,000 rows of data in under 20 seconds via two methods either graph or office scripts so just as a quick explanation I have this run a query against the data set we can see that it gets the top 20,000 rows from my data set that I have um I have par Json which enables me to get all the values dynamically and then I have ADD row into a table which is in an apply to each Loop so this is going to run 20,000 times if we go and put this into test mode the time is ticking I now need to go and show you these other flows and uh do my demo if I jump across onto powerbi the service you can see I have a sample report here with the data that I'm actually crunching and I've got a button which can run a flow so this is one of the methods of running a flow from powerbi data I can click on this button here we can see it's triggering the limit here is a th000 records at the moment so this is going to run a very basic flow it's going to save a th records or a thousand rows from this data Source into an Excel file and if I was to jump across onto my SharePoint site and do a quick refresh we can see that we have a file that's just been created a few seconds ago and if I pop that open and then uh jump to the bottom I have a th rows or a,1 because there's a a header so that was how quick the uh flow run in order to generate an Excel file and populate it so in this flow I have the trigger for when the button is clicked nothing else there I have a select which is repurposing the data from the powerbi values array and simply putting in the square brackets the opening square bracket and the closing square bracket and then four of the columns that come through dynamically into this array so in order to get into this view you need to make sure that switch from the map to the text mode so this repurposes the data turns it into nested arrays and the bonus with the nested arrays is that it's set up to either send to the graph API or to the office scripts but before I do that I have a send an HTTP request to SharePoint and what this is doing is it's creating a blank Excel file 100% blank brand new Excel file based on this API call and it's with this Excel file that I then run my office script and that Office script will take an ID from that HTTP request which is the ID of the file and then it will pass across this worksheet data that comes from our select so this is all that we have in our first solution in order to populate a file the limit of a th000 records but it runs incredibly quickly and if I go back into the history we can see that it's 7:31 tonight it ran in 3 seconds that's how long it took to populate that file we can have a look at the select we can see all of those rows of data that have been repurposed from the array that came into our flow from powerbi the send an HTTP request which creates that blank Excel file and then the Run script which calls our office script to populate the data into our file now I'll have a quick look just to see how that flow is doing we can see it's still running it's still populating all those rows hopefully I've got enough time to complete my demonstration if I pop across now on to excel this is a look at the Office script so you can see on the right hand side I have a script it's JavaScript and it takes a few different parameters including um uh option A Boolean option for Does it include a header or not because I can pass across a header with my array and that will add it to my table do I want to format this data as a table so it's therefore accessible using the native actions to list rows Etc I have a sheet name which is a default sheet name will always be sheet one unless you've got a workbook with different sheet names that you want to populate and then you'll see I've got the worksheet data array which is the nested array that I created with the select so all that's doing is just doing a little bit of calculation to see where it should start based on the size of the data and then it's chucking all that data into the sheet in one big go which is why it took 3 seconds and at the very end you can see that I'm formatting the data as a table if the Boolean value of format as table has been set to true and you'll see all those settings in the runscript action in power automate now I'm going to jump across on to the new design designer um the reason I'm doing that is I'm now running this flow from the manual trigger and I have the same action as before I'm running a query against a data set and I have a Dax query that's getting 20,000 rows from this data set and then I have parse Json so with the parse Json I've simply run this initial action I've got a copy of the data as an output and then I have used a sample payload which has come from the history of the flow pasted it in here to to populate this schema and allowed me to call the data within this array dynamically then you'll see I've got a compose action and that composed action is a nested array a single nested array it's got some column names in it so column 1 2 3 4 5 six now I could have these column names as anything I please it's entirely optional but it's for my office script do I want to include the column names or do I not then I have a select and that select is exactly the same as we saw saw on that first flow in this occasion I have uh the body from the parse Json but again I put this select into text mode using this little switch here and then I've supplied the dynamic values into this array so square brackets Dynamic values and then commas between all those values and that gives me a nested array then you'll see I've got two branches I've got a left hand side and a right hand side because I'm actually creating two versions of the file I've got two methods of course I said there's two fast ways of doing this the first of which is involving the graph API and I've got a video of that already and then the second of which is using off scripts again I've got similar videos I've just made this slightly more improved and slightly more efficient if you can believe that so on the left hand side we have the send an HTTP request same as before I'm creating a brand new file it's called Excel graph api. xlsx on the right hand side side I have that same action and I'm creating a file called Excel office scripts. xlsx if we now work our way down the left hand side you see I've got some letters a through to zed so this is needed as part of the graph API and the reason I've got this is I want to dynamically calculate the cell range if you think about the cells on a sheet in fact if I jump onto Excel now we can see that if I select the following cells I've got a range of A1 down to E6 and that would be five columns and six rows so the graph API needs to know the range for the data that you're providing and therefore we need to calculate it dynamically so that's why I have a compose with the letters a through to zed then in my next compose I have a simple expression I say it simple but if we start from the middle I'm getting the length if we start from the middle you'll see that I'm getting the length of the first object in our select now the first object in our select is going to be one of those nested arrays which is basically one row of our data set and that one row is going to have however many columns there are so if there are six columns the length of the first body in our select is going to be the number of columns and I need to then subtract a one from that and so if there are six columns we're now down to the number five and you'll see that whole expression is in square brackets that's because we are now going to be selecting a letter from an array based on that number and arrays can be selected using what's called an integer index again I've got videos on this showing you how to build out Expressions but if we want to get the fifth letter from our string we would get number four hence we subtract one the next part of the expression is based on the outputs of our letters so we've got our outputs from our compose that has our letters a to zed and if we use the Expression chunk we can actually turn that string into an array so we're chunking into sizes of one so that'll give us an array of a through to zed and then we choose the appropriate letter based on the length which is the number of columns in our select minus one next up we have the dynamic range based on the starting cell of A1 we have the output from our composed letter which is going to be the letter F because I have six columns and then we have the overall length of our select so if I click on that you'll see I'm using the expression length of body select which is how many records I've retrieved from that Dax query so this has allowed me to be totally Dynamic for our graph API call into our first invok an HTTP request and it's worth noting at this point that this is a premium action the graph API is premium however The Office script is is not it's using standard actions albeit you need to ensure that your it have turned on the office scripts functionality so you'll see I have a call here which is getting an ID for my particular site so we have the name of my site which is demob bird 365. sharepoint.net from the graph API and this is important because I need it as part of the request to populate the document I then have another compose action and that is getting the ID of the file that we've created similar to the first flow that I showed you it's based on the body from the send an HTTP request and there you're getting the D and unique ID so you'll see in that expression that's on the screen question mark square brackets and then single quotes d SL unique ID so that gets me the ID of the file into compose action and then I have the site ID which actually comes across as a comma separated string there are three values I need to get the second so I'm using the expression split based on the output of our HTTP request for the site ID specifically the body ID and I'm splitting that with a comma to get the second value we've spoken about integer indexes because they start from zero the second value is the number one hence why I have the number one in Brackets so that's allowed us to get both the file ID the site ID so finally we jump into this action here again another invoking HTTP request and this allows us again to call the graph API we pass in the site ID we pass in the file ID and then we specify the sheet name which is sheet one followed by the range which is the sheet name exclamation mark and then that dynamic range that we've created so A1 through to the letter and the number of rows as part of the body you pass an object you can see I have a key called values and that values is a nested array so the output from the select that I initially created with all that data from our Dax query is being passed in as the values and that's it that's all that's required in order to generate a file and populate it using the graph API and it's actually a quicker method than the offer scripts but like I say it is premium if we jump back onto that flow we can still see it is populating that Excel file so it's still not completed those 20,000 rows so let's see if we can get on to having a look at how The Office script feature works so if we now take a look at the right hand side we've already covered this HTTP request that creates a file I then have a compose ID which like the left hand side is simply getting the ID of the file name and it's the send an HTTP request and then a question mark with d SL unique ID in single quotes far less actions required here we're straight into a run script we can see some of the properties that I showed you in the office script when we were in Excel so the script is called add data to excel from array there are script parameters so do I want to include a header yes I have um do I want to format the data as a table yes I do and what is the name of the sheet show sheet one you'll see that in the worksheet data parameter I actually have a union and what that's doing is it's bringing together the compos that we saw at the beginning that has the column names with the body select so the reason I have that is because I have include header set to yes so I'm combined in the data that's come from the Dax query that I've repurposed with a select with the compose that has my column names if you just want a quick reminder if I jump up to the compose here we can see I have my nested array make sure you've got two opening square brackets and two closing square brackets but I have my column names one through to six of course this isn't Dynamic it's fixed but it might be something you want to have in your data when you populate your file the select is just that data dynamically from the parse Json which of course is from our Dax query so back into our run script if we have a look at the file the file is based on the output from this composed file ID above and we've already covered the content of this worksheet data if you didn't want the headers then of course you could remove this and insert the output from the select above and of course you would then turn off includes header because the select does not include a header row or a header object by default the Dax quy doesn't include it scrolling our way down there's nothing else to that flow that Office script that I've created does all the work it accepts the nested array it adds the data to the file if I've got any of those parameters set it will create a table and if I've included a header it will know based on the yes or the no to finish things off I have get file content using path and all I'm doing is grabbing the file or one of the files that have been created the Excel offer script one and then sending it to myself in an email and you can see I've got an object here inside an array four attachments it includes a file name and the file content jumping back to this original flow it's still running and uh if we go and jump on to SharePoint we can go and have a look at this uh file with a table and maybe check the progress so far so control shift end we can see it's round about that the 800 Mark those rows are still being added so I've actually got plenty of time still to complete this demonstration but I can tell you that I'm almost complete so what don't we give this flow a Wei worldl we know it's going to take 20,000 records from this Dax query which comes from my powerbi data set if I put my flow into test mode we can watch this flow unfold in front of us have a look at some of the history as things go we can see my column names there in the nested array if we go into the select there's quite a large chunk of data in this one and I've already heard the Ping actually to suggest that the email is been received if we have a look at the output we can see the nested array of data as we move our way down we've create created two files for these send an HTTP request we can see my compose it contains the a to zed if I look at the column letters it's chosen the letter F based on the length which is based of course on the select we have the dynamic sheet range which is A1 to f F 20,000 because we know we've got uh six columns therefore we've got the letter F and we've got 20,000 rows in our select we have our invoke and HTTP request which is an order to get the ID of our site and we can see that there it's it's highlighted and you can also see that it's comma separated so we have a comma there and we have another comma there which is why I get the second value with the number one the goo that I've just highlighted that allows us to get both the file ID and also the site ID that we see there and finally we have that invoking HTTP request which basically fires across all that data that's been generated in the select above into our Excel file on the right hand side very much the same send an HTTP request to create a file a compose file ID which is just to get the ID of the file above and then we have that run script action that takes all of that data data from our select hands it over to the script and lets it do it thing so you can see the Run script took 7 Seconds the invoke and hdp request took 5 Seconds not really much between the two of them and then we have the get file content and the send an email so if I jump across into my email I have one called powerbi export and there is a file there we can have a look at the time it's 7:49 so it's only about 50 minutes ago that I actually started recording this video I can pop up that file and you can see that the data is in a table but also if I jump to the very bottom of this file we have 20,000 rows or 20,000 in1 because of course there is a header at the very top so if I jump right back up to the top you can see I've got column 1 to column six which are those values that came from the initial compose so if I close that out so if I jump across onto SharePoint we can see those two files the graph API and the office scripts file of course if I pop open the office scripts it's exactly the same as the one that was emailed to us if I just jump down to the bottom there we go we've got the full 20,000 and if I pop open the graph API file you'll see that again I have all that data there's no header on this one it's not formatted as a table albeit the graph API can do that and if I jump to the very bottom we can see that we have 20,000 rows of data all created within the space of about 20 seconds now if I jump back across onto that Excel that's adding rows one by one using that Loop let's see how far it's got to it's currently up at 1,68 so still plenty of time but that is the end of my demonstration I've shown you how you can output those records into a brand new Excel file using two methods one is premium One is using office scripts which needs to be enabled at tenant level but assuming you have that all in place you can see how you can very quickly output data into Excel from one of either two methods that I've demonstrated today

Channel: DamoBird365

Views: 2,282

Keywords: Export Power BI to Excel, Power Automate Tutorial, Create a blank Excel File, Power Automate Export to Excel, Export to XLSX, Export to XLSX, export to xlsx power automate, power automate export sharepoint list to xlsx, export list to excel power automate, can power bi export to excel automatically, how to automatically export data from power bi to excel, how do i automatically export data from power bi to excel, Export Power BI to Excel, export power bi to excel power automate

Id: gtlklzi6MDg

Length: 21min 26sec (1286 seconds)

Published: Sat Jan 13 2024