How to create a Export to Excel / CSV button in Power BI

so hello everybody in today's video i am going to create a export to excel button and export to csv button in power bi so you can download the data to excel or csv let's get started okay so in order to be able to do this you need to have power bi desktop april 2021 update if you don't have it it won't work so make sure you have it why we need to have the integration with vlog you see it here now a lot of people ask me when i said i wanted to do x excel export to excel button why would you do that why would you export data to excel once you have it in power bi well there are different reasons one reason is for example you have data in external service you analyze the data in power bi and then you want to move that data for example billing data into dynamics 365 to build your customers or to whatever erp system you have so that would be one reason to download the data in order then to put it up again now with flow we will be able to do it all the way so it's actually quite neat another thing would be version handling of your clean data so you've just done files in case your source system does not handle version handling so there are different scenarios where it does has nothing to do with data analysis is just to move the data the clean data right um so that's what we're going to do let's start with csv that is the one that i was interested in and that i wanted to build first because again i'm not exporting to excel to analyze just to get the data out that is clean so we're going to first you go into the uh visual app store and then you search for flow you'll find the flow even if though is called a power automate you add it and then i have pin it into my visualization pane i definitely want to have it i tried to do this painting yesterday it didn't work but it's working today so if didn't work yesterday for you try it again how do you do that you clicking click into the visualization and then they did a fabulous work telling you what to do in here i mean the power how to make the team beautiful software produces beautiful beautiful software really needs and what we're going to do is we're going to export the data on this chart sales by week number as an example so the first thing is it tells you is add the data so sales put in sales whatever fields you put in here is the ones that you can actually do something later on right and then you have your month uh i think is like that now we have our data it says here set up your flow so select edit in the dot dot thank for that because i would have found it ever and then it takes you to the entire flow experience in power bi that is so neat so beautifully integrated now i already have the two flows created but obviously i'm going to show you how to create them so let's start from the beginning i'm going to reuse one of these templates and modify it it is a lot faster actually than started from scratch so here we have we're going to get rid of these updater row and to export to csp we're going to use compose data operation and then here on the input we're going to put power bi data right next step it is create csv csv table you see here for data operation and it is going to be composed of whatever data was added into this step okay and then the next step is going to actually create a file on sharepoint create a file in sharepoint so this is on my i'm going to choose youtube choose the folder vaccine tracker file the file name is going to be utc now now i i wasn't able to do this in here as you can see it doesn't really pick i don't know if it's going to work let's try it csv you need to add the extension of the file in order for this to work this is supposed to give you the timestamp now as you can see it doesn't recognize it maybe there is a limitation for this embedded experience for now so so the output is from the csv save it is saving you can see here and then you have to go back and you have to apply it make sure you apply it and then you see this you successfully apply bump onto your button otherwise if you go out you will see that you don't have a button you still have this list experience so go back here is our beautiful button let's modify it a little bit you have the formatting options that i would expect uh export to csv we're going to put a little bit bigger and then we will have a green feel green pastures there you have it so this is our thing so how do you trigger this on party desktop as always control click and one of the things that you need to be aware of that it says that it was triggered it doesn't mean that it was successful you need to go and check if it was successful this is a little bit of a pity there are ways around it as always so let me show you so this is the one that we created you can click on it and then you see here oh it failed probably because of the utc exactly exactly yeah okay so let me show you let's forget for now about the name or maybe i should show you in flow let me show you flow so i'm now in flow instead right and this is the yeah the same flow that we were trying to create you will see the difference now with the utc thing it is on create file you see that this is the one that doesn't come up so utc now okay so that's what we need and then sales dot csv save and then we go here automatically we run the failed one test you cross your fingers and let's see if this works this will work now so you see it was successful it was a utc thing and if we look in here it is 43 a few seconds ago this is the one and here you have the data so now every time you click on it it will dump your data okay so really really neat now let's do the uh and obviously your flow make sure that you share your flow whoever needs it don't forget that and if you go here to the flow it is available here so you can reuse it for anything right so let's go back and we're going to do a export to excel so that was a little bit more complex to be honest so we're going to the new button same drill here sales month we put sales in here we go to edit and now you might be tempted i definitely was tempted to do this to pick the is for creating the excel i thought it was the first one it should be export to excel no this is not this will update existing data and that's not what we want what i want is just to dump data in there okay so we're going to delete this one and i'm going here to put at least add the rows i think it's called other role into a table same thing you put your sharepoint you put your library which is vaccine tracker you put your file here's the thing in order for this excel export to excel work first you need to have the excel file already created so what i did it was that dumped the visualization that i want to get the data wrong to csv convert it to a excel file then the data needs to be in a table so you need to put it on a table so here you have the data csv week which was a dump and here you see the table you need to have the data into a table so you can grab it okay and once you grab it then you just bind bi sales in sales and then the the thing is is creating a loop so it's going to do it for every new row so it's not going to just update one row but multiple rows rvi sales i don't have to put power away in front of everything to be honest your mouth okay it's nice if they get rid of this power bi thingy save and then again remember you have to go back and you have to apply in order for it to work and then you go back and then you'll get a beautiful button you see it here and then you do export to excel and we want to have greener than green how about that one a little bit bigger and now to be able to trigger it you control click again it says that it got triggered but it's not saying that it was successful to know if it was successful you have to go to the actual flow and check i'll show you so this is the second one this is the one to excel and then you'll see here it's still running but here you will see if it actually fail or not one of the things that you can do in order to check that this actually went through is you could say send me an email right or send me a notification on the mobile like clean it got but i don't like that kind of solutions because i already have enough notifications i don't anymore so uh yeah not too happy about that i think the flow should be like maybe like pop green to say it was okay just for a second like bl you know blink green or bling red so let's look at our excel file and see if it got updated so the excel file is in here and there you go so now one thing that you need to be aware of these are the rows to tables you will literally add rows to tables so for example here you can see that there were blanks it will add to it at the end and anytime you click on something it will add more rows so it's not dumping and rewriting you need to change either the trigger or the flow you can you can for example delete the contents of the excel before you enter new data so there are ways to do it but just that specific flow will add rooks just so you're aware of if you wanna if you have a base table if you wanna update use the update one so another thing that you wanna be makes you aware of which is actually quite cool is that you can filter the data so now i'm filtering by this product this is getting filtered by the product if i click on export to excel only the filter rows will be added to the file so that is actually quite neat also if the thing is running obviously you won't be able to run it more than once so you need to be aware of that um so maybe you should have like a run in i'm running don't click on me again so it will be nice to get a little bit feedback more feedback from the button of what is going on in the background so you don't get confused by it so for example this of clicking and not be able to do anything is is not a very intuitive if you have access to the flow you're good to go but otherwise it can be a little bit daunting okay so what type of laws are you planning to build this is so so cool i will do more tutorials as i start building flows for customers and really really looking forward to this this is so so neat the flow team does such a marvelous job when creating you know user interfaces is just wonderful so i will see you again on friday with a cool tax rate this video i think so until then as always take care bye

Channel: Curbal

Views: 182,717

Keywords: Power bi, powerbi, Curbal, power bi desktop, power bi tutorial, power bi training, power bi for beginners, ruth pozuelo, How to create a Export to Excel / CSV button in Power BI, export to excel power bi, export to csv power bi flow, flow visual in power bi

Id: BnTipbooeP0

Length: 14min 19sec (859 seconds)

Published: Wed Apr 21 2021