EXPORT DATA to EXCEL // Ways to Export Static/Live Data from Power BI Desktop or Power BI Service

in this video we're going to look at a couple different ways that you can export your parbi data into Excel we're going to have a look at each one of them and what kind of scenarios in which you'd use them 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 where we cover tips tricks and best practices when working with powerbi 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 you've created a dashboard you've shared it to your users and they now want to export the data that they see it might be to verify the values that they can see to what they know or maybe to combine it with the data that they already have nevertheless it's a really unavoidable situation to be in in most cases the typical platform of choice when it comes to exporting their data into powerbi is in Excel just because it's pretty flexible and it's easy to use So today we're going to have a look at a few different ways that you can do that from powerbi into Excel so here's a reports that I prepared for you today it's very very simple and it's only got two visuals so one is a table which is just a list of the different sales for every single month and then we have a matrix here which is just showing the different categories of products and how much were sold in the different years we're looking at this report currently in the powerbi service so I have this published in the powerbi service but I have a version of the same rep Port here in my powerbi desktop so the first main way that you can export data from powerbi is by using the ellipses or the more options button which is enabled to visuals by default unless they are disabled so if you go to more options here you will see this option export data which will basically just export your report or the view that you're seeing into a CSV file in your local machine now we can do this just hit save and that's your basically your data saved in your local machine let's do the same thing for the Matrix if we hit export again save the data as a CSV I'm just going to add data to there just so that we can distinguish between the two so I saved it like that just because I want to show you how or because they are kind of exporting a little bit differently than what you might expect so on the CSV if we open up the first one it just gives you the different months and the sales the similar way that you see it in powerbi desktop so if we just adjust it a little bit you can see that they are showing the same way now if we close that and we open up the second one which is the The Matrix what you can see is that it doesn't export the same way it actually exports in a flat table what do I mean by that so if you look at this table and you look at the view here it's not really showing this it's showing the same data but it's not showing it in the same way that you're seeing it here in the parb AR report now there is a way for you to get the summarized or the same kind of way that you're seeing it using the export button in the powerbi service so if I just close this preview here and let's go quickly to the parbi service here and again if you hover over the visuals and it's not disabled you will have this more options available if you go to export data you will have a different option here so you will have a data with a current layout or summarized data now we're going to go back to the summarize data in a little bit but for now let's go with data with current layout so if you hit export it's going to just ask us to save it so we're just going to save it as data 3 and let's open it and let's see how that looks like so here as you can see it's showing us in the right formats that you see here in RBI in your reports so let's go back to the powerbi report in the powerbi desktop because there are few more ways to you can export data from here so I'm just going to make some space for it here and let's go through a few different things that you can do to export data the first thing is that when you see data here like in this table for example you can just simply right click on any of the information that you see and you will have this option copy now there are a few different things that you can do here so you can copy value which just if you just copy value and just let's paste it on an Excel so you'll see it just simply copies the value that you right clicked in that Matrix and then just paste it as it is if we right click again and copy selection so if you make multiple selections that will copy those selections for you so for example if we selected multiple months here and if you are just going to hold control here to select multiple values right click copy copy selection and if we paste that in the Excel file here as you can see it copies both the the month column and the sales only for those that we have selected at the beginning the other copy that you have is copy visual but I'm not sure that will be useful for copying outside of into Excel because what it is is just it copies the visual so that you can paste it in powerbi within the powerbi desktop so if you copy Visual and then you paste it will basically just create a copy of that visual so there we go what's interesting with the copy selection is that if we go back to the Matrix and you copy selection on one of these elements so let's say let's just right click on this one copy and then copy selection if you paste it what you will see is that it copies not just the value but also the X and Y AIS in The Matrix so it gets the category name as well as which year that belongs to so that's pretty handy if you're copying data from a matrix and although we are doing all of this in parbi desktop if your tenant or your report developer has enabled this in parb service you can do all of this and a little bit more in the powerb service so let me just show you how that works so if you go back to this report here in the service if I right click on one of these values and then under copy you have copy value copy selection and you have these two options copy into an image or image with a caption now now this is useful if you want to share your visuals into an email to share to someone and let me just show you quickly how this works so what it does as you can see is it creates a caption or a snapshot of the visual as well as adds these captions at the bottom so that it adds a link to the parb report as well as a timestamp which is useful if you are someone who works with you know different people and you want them to be able to access the report and for them to know when you took that snapshot so if we hit copy and I'm just going to open Outlook here and if I paste whatever is copied in my clipboard as you can see it took a image copy of my matrix it's added a link which report it belongs to or which workspace it belongs to and the snapshot so the Tim stamp of when I took that so I think this feature is pretty new because I've never really used it before but I can see it being pretty useful especially like with my users so moving on going back to the parbi desktop view here we still have a few more options for you to export data believe it or not let's go to the table view which gives you a preview of your different tables available in your model if we go to the order or let's say just example just the calendar table here you can see the preview of the calendar and their table if you right click on your data or any of these uh column headers you can copy the column or copy the whole table which I found it pretty handy if I'm kind of debugging or trying to kind of understand how the data is composed outside of powerbi so I might want to do some extra manipulation this is typically how I do it especially in powerbi desktop another way that you can export data is from the power query editor so in powerbi desktop if you go to transform it will open up the power query editor here which is again the tables that you have imported into your model and then on every table you have this option on the top left hand side of each of the tables to copy the entire table so again I use this quite a lot especially when I am kind of verifying the data in my queries and it's especially handy because you can copy the state of the tables as the steps happen so you might want to copy the table before I remove the columns in this step so I just go one step up towards navigation and then copy that table and then Analyze That in Excel which is pretty handy so now let's look at something pretty cool that I found that you can do in Excel which I didn't know that you could do before and for some reason it's always been there I guess but I've never never really seen it before and this is this option which lets you insert data from a picture so here we are in Excel and if I just let's just create a new sheet here and under insert under data you have this option from a picture now you can basically import a picture and it will try to get the data from that picture and so let's have a look at how that works so let's open up the Snipping Tool I'm just going to snip oops so I'm just going to snip the category this Matrix that we have just we were looking at and and let's go back to the Excel here and then let's go to from picture get the picture from my clipboard so what it will do is will it will try to analyze the data that it can see on the picture and it will try to kind of fill it in by itself now it's not perfect but it's absolutely better than starting from scratch so I can see this totally being totally useful if I have to kind of recreate a table for example I don't have the option to copy the table but I want to copy it into a data form that I could use I can see that there's a lot of imperfections here that needs to be fixed however it's definitely better than starting from scratch so from here we just need to Simply insert the data there's a bunch of different errors here you can review and change them manually so for example this one needs to be 53,8 79.2 we're not going to go through all of these but uh let's just fix that one and then let's hit insert data insert anyway and there you go so you have have your table pretty much like primed so you can start working or doing your you know cleaning up from here or you can clean it from the previous window if you have build access to the data sets that you are pulling from in the parba service you have a few different options for connecting to that data set and creating a live connection I typically use live connections from Excel into powerbi when have to create a reports that rely on making sure that the output of it is the same or linked to the most recent data or updates in powerbi so let me show you a few ways that you can create a live connection from powerbi so the first thing which I mentioned that we're going to go back to is that export button so if I go back to one of these so let's go to this table for example let's go to export data and then under summarized data you will have a few different options here of how you want to export the data but the default is an Excel file with live connection so let's hit export there and let's preview how this would look like so I'm just going to call this live let's open that file so as you can see what is done it's showing us the data from that table in this sheet called exports and uh what's different about this is that under the data tab uh the ribbon up here under queries and connction s you will see that there is a connection to the powerbi Azure so that means that there is a live connection with this Excel to the parb data set so if the data set on the parb service updates all you need to do from this Excel is basically hit the refresh all button and it will update this table that you're previewing here to what you can see in the powerbi service another option that you can use is the analyze in Excel feature which you can do or you can get from powerbi service so the same concept so let's go back to the powerbi service here so we have the report here if you go to export you have this option analyze in Excel and what it will do is it will generate another file so just going to call this Live 2 and it will be the exact same setup except that it exposes the model to you and it automatically kind of lets use pivot table so let's just enable the content here just so that I can show you easier what I mean so here we go so as you can see you have full access to the model and all of the details in that model so all of the tables The Columns even the measures so if you want to and if you're used to seeing your data in pivot tables in Excel but not really recreating the data that's already been created in the service this is another option for you and that's really it for my list for today as you can see there are a bunch of different ways that you can export your dat data from powerbi into Excel so I'm pretty sure I have missed out a few different ways that you can do so if you're looking at some more options of how you can export data into Excel I have created a few videos but using power automate that gets your data from those data sets into exported into a SharePoint site or folder so if you are interested in that kind of solution go check those videos out thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't s 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-bye

Channel: Solutions Abroad

Views: 1,909

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, 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 2023, power bi export, power bi excel, power bi export data, power bi export data excel, power bi analyze with excel

Id: hzyTBj8kKzw

Length: 14min 45sec (885 seconds)

Published: Mon Dec 11 2023