I'll show you five different ways to export Power BI I data to excel. But also
talk about when you should use which approach. I know you're gonna like number
one, because we would show you how you can export millions of rows even
hundreds of millions of rows to excel. Along the way, we would cover some
bonuses show you how you can stop others from exporting your Power BI data and
a bonus hack which is actually not supported by Microsoft but works really
well, when connecting Excel and Power BI. Now in the description below you would
find time stamp list of topics so you can jump around to a specific topic if
you need. Hey, I'm Avi Singh Microsoft MVP and best-selling Power BI author and if
you want to become a Power BI pro, make sure to subscribe and click that Bell, so
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
how it works in power bi desktop so let's take a look so again the same file
but now it's been published on power bi comm so here we are inside a browser
window and again we'll do the same thing we'll hover over the visual and click on
the ellipsis click export data but the similarity stops there because at this
point it's gonna ask you this question now it is basically there are two
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
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
this video I would love to hear from you so leave a comment like subscribe all
the good stuff while we're on my friends