Export from Power BI to Excel 12 ways - NEW in 2023

in this video I'm going to show you 12 ways of how you can export data from Power bi to Excel and some of these include brand new methods for example you can get a table and also just some of my favorites that involve you just quickly taking a screenshot of it in power bi and then turning that picture into Data in Excel brand brand new feature as well so my name is David I'm gonna have tons of videos on Excel PowerPoint power bi Google Sheets Zoom teams if using Tech of the workplace I'm covering on my channel I love talking about the new stuff and I love talking about the rarely new features so let's get going brand new or you can do with power bi is you can click on this kind of table you can choose export data and then you can choose if you click summarize data you can have a live connection so I click export it does create the file and I'm going to open it and it doesn't look like it's done much it looks like this with just column one but if I see here it says click refresh to update so if I go to the data Tab and I choose refresh all I need to press OK and here it is as well and it is actually going to stay up to date now if I want more control I can go to the data Tab and I can choose creating connections and this is actually the connection that's there so I can double click it and I can see this and these tabs and in definition you have here the command text and this is actually the Dax query that is being used so you can actually edit this if you want to for example let's say that I don't like having the underscores under total kg sold I can then edit it there and then press ok and there you go now it's added today here and it's it's worked out so some Nifty features and if you're into that kind of code you can copy and you can paste it and edit it and see it as you want to see it now if you have a data like this in a matrix with rows and columns you want to export data and you have data with current layout or summarize data now if you choose summarize data then you have the option for that live connection but you will get it just in rows and columns whereas if I want to get it as a matrix I need to choose this so just to prove that click here and then let's do the other one so I'm now going to do just data with current layout export so 4 is current layout which is going to look exactly like this there you go exactly like that and if I go to the other one three is going to switch it to now just be in rows like this and if I choose refreshold then I get it showing like that but notice that it's unpivoted it from the original format another way and I think one of the coolest things that you've been able to do for a while is if you are connected to this data set you can say something like Ross Rachel Monica I have spelled it wrongly but that's okay um and then I can go to the data Tab and I can do stuff and these are linked data types so it has actually picked up Monika and there is all the information about Monica how cool is that if I want to extract it I can click on all of them and I can choose plus and I can choose for example costs or whatever aspect I want from this even the image I can get the image to show up and make that a little bit bigger like that so yeah pretty nice pretty cool and you can get this I love this feature because it means that anyone anywhere in your company can look up information about employees about stock quantities if you had product codes there's so much potential in this so here I'm on power bi desktop and I have here the Dynasty and the monarchs the English monarchs and how long they've reigned and that comes from this data set over here with also the gender and Dynasty so what I want to do is bring this into Excel so what I'm going to do is I need to go into the model view and go into that table and then I need to click on the table and I need to choose a feature table on then give it a description so it's like the row label on this usually should be a unique column and then these should be the same it's very rather than not and from my experience it gives you errors if they are not it causes issues so I've done that and then I need to publish it so I'm going to click to open on power bi online so here I'm in power bi online and this is done to a workspace and I'm going to click on the three dots and I'm going to say for your data set and in the data set I'm going to see endorsement is blank I need to give it a certified endorsement so go to file and settings and then here I'm going to go with endorsement and Discovery and I'm going to go with certified you need to do this in order to make it a data type in Excel so click apply and then it should be findable on Excel so here I'm on any Excel file that could be made by anyone in the organization that has access to it so in the data tab you have these data types and although you have the defaults which are come with every Excel file I also have these ones from my organization and English monox is one of them so if I write for example laser birth Second and Charles third and Victoria then I can select them and I can go here and I can choose an English monarchs and there it's done it and I can go to the plus and I can look up their Dynasty or I can even say equals this one dot and then give any of these a range from press enter and then Ctrl d to fill that down so it works better if you're in tables in Excel but that is a pretty cool thing that you can do now if you're not seeing those those options then I need to show you later on this video some settings that your admin has to do in power bi online so other ways that you can get data from Power bi online uh there's two ways to get to the same thing in the data tab you have get data from Power Platform and you can choose here from Power bi Excel Consulting is my company name so that's why it's showing me like that so after I've clicked that I can see the data sets coming up here and I can click that to open it I can click that to open to the data Hub as well and this is the tables using it and the number of reports using it and I can click to go to any of those if I want to now I'm going to insert pivot table from this though this whole thing might take a little bit of time to load so I have my measures over here and I have my things over here so what I can do is I can just use it like a regular pivot table so if I want extras and Concepts I can click on that or I can even choose this numerical field and it can put it in there but you can drag a numerical field from Road into values if you have the Insiders version although that's coming to everyone else in Microsoft 365 so these are some kind of things you can do and you also have dates and the dates will recognize them as dates in the newer version there you go so it's recognizing them as dates so really really nice and this is a great way that you can connect to Excel data you can also get to the same experience from inside pivot table and from Power bi this will also load up this and you can also do this in Excel for the web so here I'm in Excel for the web and in the data tab I also have these things like English monox works the same as I showed you earlier and in insert I have pivot table from Power bi and this will also load up the same experience great so that is some really cool ways that you can get it showing Live Connections but sometimes you just want to copy and paste something from Power bi just to quickly analyze it in Excel and that is a really really important thing I'm going to show you because my preferred method is again guarantee you're not going to be the method that you regularly use so let's look at that so over here what I can do is if I want this kind of table let me drag that up here and then what I can do is I can click on there and I can choose export data this is a very very inefficient method because you have to choose a name for it and then you have to navigate to it I'm going to do that outside of video so here it is notice it's a CSV file and I have also on pivoted it so that might not be what I want but it does get you the results this way it's just very very slow and I don't like it I wish you could just copy it but there are things that you can copy but that's not one of them click on say a column I can right click and I can choose to copy and I can copy selection or copy value I'm going to copy selection and this is quite interesting when you paste what it does because it actually gives you something that's very different to what you had before which is name of all of the columns unpivoted and then just that column there for that one so it's done that just for potatoes it hasn't done it for the rest because that was my selection so it's not incredibly convenient but it is the way you do it of course you can right click and you can choose copy value as well and copy value will control V just take the value it won't give you the headers so the way that I like to do it and this is really really just unconventional but I think this works pretty great is I take a screenshot window shift s and draw around it much better than a print screen because it takes only that then the data tab you have this thing called picture and picture from clipboard it will give you the area on the side pane here and I'm going to insert data and insert the data like that and it has just given me the data like this now it's not got it perfect it's combined these inside one cell and these inside one cell even though the numbers are in the right places so it hasn't worked perfectly I'm going to show you how to get around it and it's not that difficult you just go to your table inside power bi and I'm going to go to this one and I'm going to search for grid and I'm just going to turn both of these on and make them black so it's super obvious where the separators are also try and avoid having wrapped text because it doesn't work as well but Windows shift s here I'm going to just draw around it make sure you collect it and from here I'm going to go from picture and picture from clipboard and here we go uh often it gives you things to review you can actually edit these one by one if you find an issue but insert data when you've got those spaces works perfectly well usually with power bi so it is a really really good feature and actually quite encourage you to use this uh maybe zoom in as well so if you zoom in then you get more likely to be accurate and it just is so so much faster than any other method so other things that you can do from Power bi that is not to do with these tables in the visuals but to do with the tables over here is you can actually go to the table and you can choose these dots and you can choose copy table then you can go back in here and you can just paste and it will paste all of that which is nice you can also do it from Power query so if you go to power query editor you can right click a cell and copy it you can right click a column and copy it or I just right click the table and copy entire table then you go to Excel and you paste and you get that showing up like this the uh there is a limit on power query which is lower than the limits on power bi online now other things you can do because this is a query you can right click and you can choose to copy a query and then in Excel you can choose queries and Connections in queries and you can choose paste and now it's pasted that note that if you had any precedent queries it would paste all of the president queries including parameters so for example here I have one that comes from a staging query and I'm going to copy it and then in Excel I'm going to paste it and I get the staging companies which is not loading but the companies is loading you can't edit them then as regular queries make sure you have access to the right things otherwise it won't be as slick so a couple of things that you need to be aware of settings let me show you those so from Power bi online you can go to settings and admin portal and this will take you to this only the admin is able to do this though but you need to do a couple of things so you need to have modern workspaces choose this one export to excel you need to have certification turned on as well and you need to have also featured tables turned on for the one that I showed you earlier so this needs to be on for the whole organization so on Excel you do need to have access to Office 365 although any version will do and then you need to go to file and then options and then you need to have the trust Center and Trust Center settings and then privacy options and privacy settings and then click on this turn on optional connected experiences so press ok to that and just to say those admin settings you only need to do them for experiences that you get from Excel so for example the data types on the data Tab and the insert pivot table on the insert Tab and if you like that video then my name is Dave and I'm gonna have tons of videos on Excel PowerPoint power bi Google Sheets Zoom teams if you're using technical workplace now I'm covering on my channel I love talking about the new stuff especially the kind of stuff that is a rarely use feature thanks for watching

Channel: David Benaim

Views: 14,459

Keywords:

Id: Cq52Zv1hIxM

Length: 13min 27sec (807 seconds)

Published: Wed Feb 08 2023