Export MORE THAN 100k rows of Power BI data to CSV using Power Automate | No ROW limits!

so hello everybody and welcome to another Power bi video in today's video I'm going to show you how you can batch a power bi exports using power automate so you don't hit the API limit okay let's get started make sure that you check curb weight learning portal all the videos I've ever created have really worked for you by searching or categorize so you have complete tutorials modeling 101 anything that you might want to learn about power bi is available here check it out curveball.com learning portal so you often ask me like how do you come up with these ideas for the videos the answer is I have amazing customers they keep on telling me all the time and it's not often almost never that I get to say oh the customers are but this time I'm able to so I was working together with Johan and Luke to create the solution today for you so say thank you because we did it together okay so the first thing that we're going to do is look at the data set this is the data set that we're going to work with is the power based salaries that I showed you recently we're going to support this data set and it has a hundred and two thousand rows so it just hits the limit so you won't be able to export everything okay so I have shown you how to export data from Power bi using power automate I have an entire playlist go check it out if you want to know the inner workings of this simple flow and the code will be available on curable learning portal okay also there will be a link down below so this is what I did I have a clue that it goes let me put it in edit mode because it's going to be easier for you to see so it basically once a day goes and it runs a Dax query that allows to get data out of the published report on the service and then creates a CSV table and create a file now if I run these you're gonna see that it's going to hit the API limit there's a an API limit there's quite a few limits but one of them is a hundred thousand rows this one is 102 000 so you're going to see that it hits that and instead of providing all the data it will sample it will give you a piece and bits of everything which was quite interesting actually I thought it would go give you a hundred and stock but it doesn't do it it gives you a round it will depend obviously on your data but let me show you I have actually created an index to the table so we can actually see what gets exported so it's important to this SharePoint Library I'm going to open it here and you will see especially with our Index this is our index you see it starts at 4000 already random and if I control shift arrow down it will go to the bottom of the Excel file and then you can see that it's only 50 000 rows we want to get all the 50 only 100 000 rows how do we do it okay so we're going to do this in steps because it will be easier for you to understand I believe so let's go back to the query and what we need to do here let me go here once more so I'm gonna explain how I thought about this problem and this is quite simple we have a 102 let me edit the work book convert so we have a hundred around 100 so it's a little bit over but something like that thousand rows right so we need to Loop these so we don't hit the limit you will have to test about what the limit is I don't know if the API gives you the limit if you know let me know in the comment box I just tasted when it starts to go wrong you can actually lower that a little bit just to make sure that it will always work as the data grows so we have here a hundred and two thousand so what we're going to do is to Loop it so I'm going to look it at 25 000 rows so we have one twenty five thousand and then we have the next one will be twenty five thousand one fifty thousand and then so on and so forth right okay so you can see it here so these are the loops that it needs to do so it actually needs to do one two three four loops to be able to calculate how many Loops we do what we could have is we could take the all the rows and then divide it by the number of Loops that we want to do which we said it was 25 000. and then we can actually round this up to get all the loops um up is there a Roundup I believe there is in Excel too to uh five Loops okay so now we need to translate this into powerapps Logics how do we do it so the first thing that we need to do is we need to know how many um rows the data set actually has it will grow so we want to have this dynamically and then just add these parameters so we can Loop okay let's convert this into a power ultimate flow so we're going to modify this a little bit the first thing that we're going to do is click edit pin we're going to initialize variables so we're going to get one variable for the mean rows one variable for the increments and one variable for the loops because we want to tell part query or power bi that filter the data set between 1 and 25 000 between 25 000 and 50 000 right and do the exports the first trial of our power automate will give us different xcsv files for each Loop but then at the end I will do a transformation that will convert all the CSV files into one in case that you need one but if you need separate you will have it too so everything okay so first of all let's create these variables this variable and that variable so we're going to go in if you just write variable you will get variable you will get all the variable options we are going to initialize a variable this is going to be called I have actually one in front of me minimum row so this is the number of minimum rows that your data set has I have an index that starts at one so my minimum row this is going to be the same as the name of the variable and it's going to be an integral a number and mine starts at 1 your minus starts at zero you need to decide how you want to do it the next one that we're going to add is the increment so isn't again a variable to specify initialize the number of increments that you want to do the loops on and again this is a trial and error if you know if the API gives you what is what limit it hits just let me know and I will redo this video modify so increment and then um the rows so how many increment rows should we have I'm going to actually call the same in here increment rows and this is going to be an integer again and we said that we're going to Loop 25 000 rows 25 000 there you have it then we're going to count the number of rows in our data set so we need to do a new run a query for power bi right we need to do these my workspace is social media this is the place where I publish the power bi report I want to do the export on so this is the workspace the name of the report is patch exports and then here I need to have a code that will count the number of rows and I've actually already created that so I'm just going to copy paste because this is not a Dax um lesson but anyhow all this code it will be available in a curable learning photos you just go there it will be a link down below and then you can get the code so what we're going to do here is basically calculate the number of rows of the index you could do a count row on the data on the table you could do there's different ways to count it depends on how your data looks like I'm just creating an index you can actually see right we're getting all the data obviously you don't need to do that afterwards so what we're going to do is we're going to get the number of rows and then we're going to divide it by the increment rows exactly what we did here you remember that we got the number of rows divided by the increment so that's what we're doing translating index and I am going to add one because for some reason it still needs one more otherwise you will not get the last numbers I'm not really sure why so now that we have that we are going to you don't see this now but with this returns I think it's like an array and what we need to have is a number for later so I'm going to convert the results of the wrong a query against the data set to number so we're going to go in here and I'm going to use a compose operation and this is actually quite convoluted how to get it but we're basically getting the first value that gets into the data set and it is like this I am going to post the code here so you can see it and copy again curva learning portal you will get the code there too so you can copy paste okay so I'm going to get it there this is going to convert the array into a number or whatever format this comes out I think it's an array I'm not sure and we're going to call this a Loops to run so this will tell us how many times we need to run our code so it's going to do this this count right and then we are going to need another variable to do the looping to get from one to two because these give us that we need four and then we need a variable that will go one two three four right that will Loop so that's what we're going to do now so it's variable uh initialize the variable we're going to call it um loops or it's a variable to Loop and then we're going to call this same Loops bar and then this is an integer and then the value here is one because we're going to do this Loop in one two three four right so now we have counted the rows we have the first row we have the increments that we need to do and we have said we know the number of Loops we need to do and we have a variable that would do the loop okay still not doing anything useful what we're going to do now is we're going to run an action that is called do e do until let's go do until so we're going to run these from Loops bar so this is going to be whatever we put in here so 1 is equal no it's greater than or equal to the loops to run so we're telling do these this is going to start with 1 and it says is 1 equal or bigger than 5. no so keep on running two is two equal or bigger than five no keep on running so when this gets to six it will stop okay that's what he's doing and now we need to move our data set in here in this I believe okay we need to do a little bit of change in here because we need to now modify the Dax so it gives tables that are smaller enough so you don't hit the limit okay so we need to have um the same table we were going to filter it let me show you this is the tax code again the code will be available for learning portals we need to copy paste okay so this is the part that is doing the filter in the filtering tag so you're saying that the index it should be filtered by the mean index which is the minimum rows so if index is bigger or equal than one and the indexes smaller and equal than the increment so we are in here we're doing this part then run so now it's going to give to power automated table of 25 000 rows right and now we are going to create a CSV table we have it here and it is the first table of rows from here the same as before and now we need to start doing the increments with the looping right so first is 1 to 25 the second one as you know is from 25 to 50 000 so we need to add to our initial variables 25 000 and 25 000 okay so what we need to do is here again do the right variable and you do increment variable and the increment variable first we're going to increment the minimum rows and we're going to increment it by 25 000 because that was with what we decided and we're going to rename this just so you know what it does I mean when you do bigger flows you need to document them otherwise it's a mess and then we're going to add another increment increment variable and this one is going to be for the increment row so we need to have the 50 000 so it's 25 000 plus 25 000. you write the number the correct number of zeros and then we need to increment the looping right by one so we're going to initialize another variable but oh it's actually not that hard but it's just a lot of steps that's all and then this one we are going to increment the loop and the loop increments by one because is this one we're incrementing okay so we're going to call these increment rows two and we're going to call these increment the loops and the next one that we're going to do is to create the file put in there and this one is going to be the output no change there so we're going to save and we're going to close our fingers I hope that this thing works let's give it a go this is going to return one CSV file per Loop you might want this you might not want it but we will fix it later if you don't want it let's give it the world and hopefully it runs make sure you don't skip a step because if you do you're running into trouble this is like Precision surgery it doesn't take long actually for a hundred thousand rows once this is completed it will take about a minute so I'm looking forward when you guys test it for bigger data sets let me know if it works it will be really really good to know my problem is to load uh bigger data sets to my power bi service because I have a pro license and you know there are limits to how big you can have them you have to grow them yourself instead but okay so just run let's go to and see what we've got we should have gotten six right so we have about a minute ago this is the one and then we have this one should be number two so from 25 000 to 50 000 and then you have it there and so on and so forth and this should be from a hundred thousand to a hundred yeah so it worked perfectly wonderful okay so now what happens is instead of having all these files you want to have everything on one file so this is what we're going to do now we're going to go back to this flow and we're going to modify it a little the trick in this is that let me show you it's actually a lot easier if you see how things run I'm going to look at one that already run and you see that these do until here what it does is it gets five Loops you see one two three so in this create CSV table step you will see that it has five different CSV files created right and what we need to do is to append them together into one so if we go to edit what we're going to do is to go to here Loops to run and then we're going to initialize another variable right variable you will get all the options for variables and you will have initialized a variable and this we are going to call append CSV I was actually doing this wrong and getting stuck because I was using an array when it was actually looked I thought hey how about we use a string and that's when everything start working again so what we did is we initialize a variable we're going to call this append csv2 and leave it empty okay so what this is going to do is going to capture the content of the CSV so we are going to initialize it and here on the loop at the end of the loop we're going to add an action and we're going to a pen if you write variable again right variable we're going to append the contents of the variable in here so we're going to get whatever the append CSV variable up there okay it's append it here on every Loop so we're going to capturing every single result from the craziest video which is so cool because in here we're going to put create here to eat that one okay so the outputs are going to get appended in here now we're going to take this outside the loop and you will not be able to I will not be able to show you now but I will show you at the end that this is going to come into a Json array format okay nothing and that won't work to create the CSV so you we need to transform it to C to CSV format so we can actually create a file so what we're going to do here we're going to add an action and we're going to add a compose combos like we did before and in here we're going to get the content of a pen CSE okay and the we're going to call these so we know what it's doing a pen convert to CSV right and now we need to don't forget these ones you will not get it working this one is not anymore the output of this USB up there is the output of here or convert to CSV that output okay you can see it there that's why it's so important to name it correctly we're going to save we're going to cross our fingers as we always do and hope that this thing works let's give it the world test and run okay as you can see it just run let's go to the SharePoint and see if we got what we wanted always nervous let's click on it we got a file which is a good thing and here is one control shift arrow down a hundred and two thousand yes so we got everything in one file now if you want to try it with bigger data sets please let me know what the outcome is you can tell me on Twitter or you can tell me here in the comments it doesn't matter it will be cool to know what the actual limit is of this probably will be more on the CSV side than on the API side but who knows just let me know I hope that this was useful to you again all the code will be available learn quiver learning portal and yeah looking forward to next video very very soon take care bye

Channel: Curbal

Views: 37,948

Keywords: Power bi, powerbi, Curbal, power bi desktop, power bi tutorial, power bi training, power bi for beginners, ruth pozuelo, export power bi data, export power bi data no limits, export power bi data no api limits

Id: fbeMw9jtJB4

Length: 23min 13sec (1393 seconds)

Published: Thu Sep 22 2022