SCHEDULE Exports from Power BI AUTOMATICALLY // Power Automate to SharePoint Folder Data Exports

in this video I'm going to show you how you can export data from your power bi data sets into acsv file automatically I'm going to show you how to set it up in power automates and we're also going to look at some of the options that you have to customize the exports of your files all of that and more so without further Ado let's get started hi my name is fernan and welcome to the solutions abroad YouTube channel or recover tips tricks and best practices when working with power bi I upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out so in the previous video I covered how you can export your data into Excel and I got a comment about its limitation which I didn't know there is a 1000 row limit for that videos method so I started looking for another way that we could get around the limitation and not only that I also in this video wanted to show you how you can set up scheduled exports out of power bi using power automate so here's the reports that we're going to use today it's the Northwind database as usual so we have just a few tables that I have uh trimmed down a little bit we have the categories table which categorizes the products the different types of products that we sell we have the orders which is when the orders were made we have the order details so how much they ordered and for what quantity and we also have some information about our customers now this report actually has nothing in its report Pages just blank and I'm showing it to you in power bi desktop but in fact it's already been published in the power bi service so if we look at the power bi here you can see that we have this one export weekly report which if I open there is data in its data set underneath it's just the report page is blank so we're going to use this report in the power bi Service as our starting point for this demo so let's set the scenario here let's say one of our colleagues want a weekly export of all our orders information in our data sets to a file for their own reporting purposes so they need columns from our different tables that we have here so data from the categories they want to know what products they ordered when who ordered it and how much were the total sales now we can simply create a table here in our report and every week we can do it for them or they can export it from our power reports or a better solution is we can use power automate to do this for us so let's start by going to power automate here and let's start by creating a new flow from here we have a few options on what type of flow we want to create we usually use automated Cloud flow or instance but in this case we are going to use scheduled Cloud flow so we're going to name this one export weekly because we want this report to export every Monday so we're going to change this once a week every Monday at let's say 9 am there we go hit create so the next thing that we need to do here is add a new Step so from here we'll need to look for what is available to us in power bi I'm just going to scroll down here and look for Power bi power bi here so you can see you have a few options on what you want to do in this Cloud flow and the first thing that you might see is you can export to file but this only works for a power bi premium capacity so if you have just Pro this won't work for you so what we can use instead is run a query against a data set so we will need to Define which data set we want to run a query against so in this case we want to look for our export weekly report file here export weekly here and the query text is where we will type the query that we want to run so the query is just a set of instructions that will return as a value from our data set so let's first Define what we want as an export so we know that we want to get different values from each of these tables into one single table which we want to export into a file for our customer so in this case let's drag a couple of values here from our from our table so we have the order ID we want let's say the product name we want category and order dates perhaps and then we want the customer name so we will just put it as company name here so we want this export table to be in our file and just to give you an understanding of how big this is just gonna count it so it's about 2 000 rows of data which we want to export so this is double of the limitation that we were covering in my previous video and we want to export this using power automate so the first thing that we want to do is we first want to write the query ourselves and we want to preview how this looks like so we can actually start by creating a virtual table which will give us a part of what we want in our query so first we want to just you select columns so this will return as a table and we want just order details here and then we will select and we'll create the different columns that we want so the first one we will name this order ID and we want the order ID from there the next column we want the products which we want the product name for so we'll do are related product name and just to preview how this looks like this is how it looks like so every order the products that were ordered and we're just going to keep building on top of that so let's continue let's add the next one which is category related category name next is the order dates related order date and then finally customer company name so this is the table that we want to export so it's not quite finished yet because although we have the table or the um the expression to create the table it's still not a in a query format and the query format that we're looking for is actually the one the same query that we write in DAC studio so to do that so let's first just copy this select columns here let's go to external tools and use DAC studio if you having if you have it installed yet but if you don't have it installed then don't worry so much just follow the syntax I'm just using it to preview how the query would look like if we use it in power automate so here in this area in the middle we will paste our select columns here and if you know how to use DAC Studio you first need to obviously Define and wrap your uh your expression in a variable so we'll just name this one table and then at the end we want to evaluate that variable so if you run so we just need to actually missing their VAR let's hit run and there we go so you can see at the bottom here it gives us a preview of the query itself so this is just checking that the syntax that we have written in our query is correct so we can see that result here and this is what we want in our file we simply copy everything here let's go back to power automate here we'll paste it in the query text here we'll hit save and we will add a few more things here so we will create a file and we will create a CSV actually so create the CSV table based on the first table rows in our data set so the result of our query here and then we will save it somewhere so we will create a file in SharePoint which I mean you can create it in one drive but in this case I'm just going to create the file in our SharePoint sites but it has to be an online source so I'm going to choose the folder path I'm going to say shared documents under the demos folder we're going to name this one orders.csb so what happens or what will happen every week is that every week it will create a new file in this folder and if the file that was there previously if there is a file it will overwrite that otherwise it will create a new one now if you don't want to overwrite the file that is already there you need to make sure that the file name is different every single time now to make sure it's always different you can add an expression here let's say um dates so that will make sure that the file name is always different every single week lastly the file content will be the output of the CSV table we'll hit save and that's your flow pretty much ready to go so if we want to test it and let's say we want to just run it manually for now let's have a look let's wait for this to run and there we go so it's finished so we can check and see how that file looks like so if we go to the SharePoint site here and let's go to my demos folder you will see that a new file has been created here orders which if we open has all of the products that we were expecting to see along with all the columns that we wanted and if we look at the number of rows of data in this file it's definitely more than the 1000 rows limitation from my previous solution so what's great about this solution is that you can essentially use Dax to add filters to the table itself so maybe you want to get just the orders in the last month or maybe just get orders for just certain categories and what you can do if you wanted to see the values first is you can test it out in in your power bi reports or in DAC studio and then update the query in power automate so for example in this case um let's say we want to add a filter context in our select columns here so I'm just going to wrap it in calculate table which is essentially the same thing as the calculate function and in this case we're going to use a filter to say if the year of the order date is equals to 98. so that will filter and just give us any orders that are in that in that year but if you imagine the possibilities this filter could be anything you can filter it to current year or current month or whatever context you want to apply in your query and that's really it for this video I hope you now know how to use power automates to automatically export data from Power bi into an Excel or CSV file thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't want to do better for next time ask your questions in the comment section box below so I can help you and you can help others if you like this video we have a patreon page where you can support the channel and get exclusive perks like Early Access demo files and credits at the end of these videos thanks again for watching and see you in the next one bye

Channel: Solutions Abroad

Views: 41,951

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, power bi 2021, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips, power bi automation, power bi export data, power bi export excel, power bi excel, power bi automate, power bi export

Id: ZTkbC8zhA5k

Length: 14min 11sec (851 seconds)

Published: Wed Sep 28 2022