You're notified whenever I go live to answer your Power BI questions. Before we dive in I would like to hear from you whether you think export to excel is a a good idea or not now. I know it can possibly lead to chaos and everyone doing their own thing. But we all in the home that is extremely popular so let me know what you think good or bad just leave a comment below. Let's start with.
Number five export data from Power BI desktop. So for this, we're gonna go to Power BI desktop which is the authoring tool in Power BI. Now if you're authoring a model inside Power BI and you want to quickly export something maybe check the data or check something else then all you need to do is to hover over the visual and then focus on the three ellipses the three dots and if you click.
On that then the option to export data should be right there and what this is gonna give you is a CSV file which you can of course open inside Excel now here you see I'm exporting a graph a visual align chart but you can of course export you know tables and matrices as well just one note that I want you to watch out for so in this case we have a matrix visual with items.
Laid out on rows as well as columns it would not look the same when you export it in fact when you do export this data you would find that the months which were in columns are now in but apart from that the data is there and it is exported to excel number four is similar but we have the export data option in power bi comm and hardly enough this works quite differently than.
Choices here that you can make the first one is and not that interesting you can choose the file format Excel or CSV the other one is really interesting though because it lets the user choose summarize or underlying data now again remember once you have published this model online you could have shared it with tens or hundreds of users and this could be any one of them using this.
Option to export the data so let's see how these two options would look like the first one is summarized data and this one is is pretty what you would expect so remember our chart was basically sales and budget by month so if you choose summarize data it's gonna look exactly like that so sales in budget by month but let's look at the other options there's the underlying.
Data now if you choose this option then it's gonna go and extract all the underlying data that comprises this graph now this might be totally unexpected for you in fact as you can notice here there might be feels and and measures and variables and all of that sort of stuff all that data which is nowhere on that visual so again it's not the summarized data it's not the data.
That's being shown in the visual it's the underlying data coming from your data tables now this might be unexpected and that's why I wanna I'm gonna add a bonus here and and tell you about others from exporting power bi data using the option that I just showed you so if you do want to stop others from doing this then before you publish it to power bi com what you're going to do is inside.
Your authoring tool power bi desktop as the model author you're going to go to file options and settings options and they're near the bottom you have report settings and in the report settings there is a section for export data now the first option does allow them to export summarized data but does not allow them to export underlying data so maybe this works for you or this one.
Posts Related:
- Power BI Relationships Not Working Four Steps to Debug for the Power BI Beginner
- Planilha Fluxo de Caixa Grátis em Agradecimento aos 3000 Inscritos
- Unir Planilhas Com Macros - Planilha Login excel VBA 1 Parte
Is the default which allows the end users to export both summarize and
Underlying data or of course you can not allow either of those options or export data would be disabled for this report once it's published on par we are calm now before we move on I also wanted to call out one other thing for both exporting data from power bi desktop and power bi com they both come with some.Limitations two of those are for one custom visuals at least as now are not supported and there's also a limitation on number of rows now of course in a later part of this video we're gonna look at how you can export millions or even hundreds and billions of rows so keep watching for that number three is actually my favorite way of connecting Excel to power bi so here we'll be.
Connecting Excel to power bi com so once you have published your model on power bi comm you would be able to see it under datasets when you go to your workspace or whichever workspace it's being published under and there once you find the data set or the model I want you to click on the ellipsis and then choose the analyze in excel option now you may have to go through some dialog.
Boxes just open click save next whatever it asks you to do but in the end what you're gonna get is this beautiful pristine amazing B the table connected back to the power bi model and my my friends this is just mind-blowing now this is not this is not a copy of the model this actually doesn't have any data if you save this file it's probably gonna be just kilobytes and even when.
You add measures and build something here even then this file would be really light because all you're using excel here is basically as an advanced visualization tool so all of the capabilities all the tricks of trade that you've picked up over Excel you can use that to build a report but the beauty is it is connected back to the Power BI.
As a single source of truth now if you want to learn more about using power bi as a single source of truth where you're connecting not just Excel reports but you're connecting other power bi reports to it you're connecting SSR as heck even maybe connecting tableau to it and it's all coming from the single source of truth if you want to learn more about that then that is well covered inside my.
Power bi tutorial and you can access that using the link in the corner or in the description below number two maybe slightly advanced this is export using Dax studio now this is a pretty phenomenal tool which and it's use goes far beyond what we're gonna talk about here but still so the starting point is make sure that you have your power bi desktop file open and you are gonna need.
To download and install Dax studio and you can do that by just going to the links shown here now once you have that installed and you open that tool it's gonna ask you to connect to connect to a model and you're gonna have a few option I want you to select the middle one BBI which stands for power bi and just select the power bi desktop file which you had opened previously now once you.
Connect to that then you're able to type in essentially a Dax query so we're not gonna go into too much detail about the Dax query here but basically you can you can type in a query and here I'm showing you a very simple query evaluate sales with you which basically returns all the rows in that table but then of course the question is we're talking about how do we export this to excel well for that.
You're gonna change the output setting to file which when you run a query is
Gonna send that output to a file now with this one you can export large data but it still can be problematic if you're trying to export hundreds and millions of rows oh my gosh who wants to deal with that CSV file it's gonna be a humongous file so let's talk about number one exporting millions or even.Hundreds of millions of rows from power bi to excel how is that even possible but before we even go there we gotta cover our bonus hack again and this is something which is not supported by Microsoft but is it really cool trick so I know you didn't like it now hey if you're still with me in this video and finding it valuable make sure to subscribe and hit the like button and.
Support our Channel so remember the part where I talked about connecting Excel to power bi comm I showed the data set I talked about the single source of truth and analyze in Excel well what if we wanted to do all that but we did not have power bi com we were not publishing our model there due to some reason right so maybe you're not.
Doing that and instead could we simply you know do the same thing the same analyze and Excel magic a pivot table magically connected to our model but instead of bar we're calm can we connect it directly to a power bi desktop file that we open let's take a look so again first thing you're gonna make sure that the power bi desktop is open and then you're gonna open DAC studio and really.
You don't need to write any query do not need to write any query all you need to do is once you connect to the model then look at the bottom corner and look for this magic address it should always look like this localhost colon followed by some number and the number would change so pay attention to that note that down and now open up a blank or new excel file and just go to get data database.
From analysis services and in the next box I want you to type in that magic address and then click next finish ok whatever you need to do and that I'm gonna give you essentially the same pivot table we saw that was we got from analyzing excel connected back to power bi calm but in this case there's no power bi comm all we have is a power bi desktop file but now you have an Excel.
Pivot table which is connected to your desktop model now one word of caution here the magic address that I talked about that does change each time you restart power bi desktop so I won't say that mostly I use this option to quickly debug or or build a prototype and Excel report so this isn't a permanent solution for that I would still.
Recommend the analyzing excel option that we saw earlier with that we are ready for number one exporting millions of rows hundreds of millions of rows from power bi to excel now the the secret here is revealed on this slide which is excel cannot really hold you know more than a million or hundreds of millions of rows so we're gonna use the magic built-in.
Inside Excel and I always say the power bi essentially is already in there inside Excel in terms of these tools power pivot and power query which are the core components of power bi now if you want to learn more about how these all fit together the power query power pivot Excel and power bi how they fit together in ecosystem then I'm gonna link to a video in the corner or down in.
The description below so let's keep moving with our idea of exporting millions of rows to excel so in inside excel once you have launched power pivot then inside power pivot go to database and then select analysis services or power pivot now at this point you're gonna type in the magic address but in this case you also need to click and select the database name there should be.
Only one listed so it shouldn't be a problem and and at this point you would have to write a Dax query similar to how you would write in Dax to do but then once you click finish it's gonna it's gonna export all of those rows from power bi to excel and and again since it since it's you got the strength of power pivot this can hold hundreds of millions of rows if not more now I showed you how.
To do this using power pivot directly load the data into power pivot connecting to power bi but you can also go through similar steps by using power query now of course my quiz question to you my friend is that which approach would be better leave a comment and let me know so that was our top five list my friends don't forget to join me live on my talk power bi show right here on.
YouTube every Friday where I'll be answering all of your power bi questions just hit subscribe and click that bell so you can join me live for that call and if you're new to power bi make sure to check out my complete power bi tutorial until next time power on my friend hey keep watching more videos and keep learning power bi but if you did enjoy.
