Exporting Data from Power BI Desktop to Excel and CSV – Part 1: Copy & Paste and DAX Studio Methods

AAEAAQAAAAAAAAz4AAAAJGQ5ZTk4ZGUxLTI4YjQtNDc4ZC05NTQ4LWRjNDk3OTBlYTE5OQ

Update 2021 March:

You can now export the data direct from Power BI Desktop using my tool, Power BI Exporter. Read more here.

Update 2019 April:

If you’re interested in exporting the data model from either Power BI Desktop or Power BI Service to CSV or SQL Server check this out. The method explained here is only applicable for Power BI Premium or Embedded capacities with XMLA endpoints connectivity.

One of the features that is asked a lot on Power BI community is how to export Power BI desktop data to Excel OR CSV.

Note: I’d like to make it clear that in this article we are NOT interested in exporting data from a visual in a report to CSV.

The first question lots of you might have is “How on earth someone wants to export data from a Power BI Desktop model to Excel OR CSV?”.

Power BI brings Power Query, Power Pivot, Power View and Power Map together in one piece of software. So why not using Excel at the first place to load data from the source? There might be lots of other questions about the reasons that someone wants to export data from Power BI Desktop model to Excel (or any other destinations). The reason could be one of the following that someone wants to export data from Power BI Desktop model to other destinations like Excel, CSV, SQL Server etc.

  • For some reason you have just a Power BI Desktop file (PBIX) and you don’t have access to the data sources and you need to provide the data to someone who is not familiar with Power BI
  • You Power BI Desktop consolidates lots of different sources in a single model and it would be very hard to get the same output as you get in Power BI Desktop model in Excel. So an export feature can be super handy
  • You might have done lots of complex transformations in Power BI Query Editor and replication the same logic on the source system could be much more complex and time consuming, so again exporting data from a current Power BI Desktop model makes sense
  • You have a bunch of calculated columns created in DAX and you don’t want to go back and redo all the hard works you have already done in Power BI in another environment like Excel
  • You might want to use the current Power BI data in Cortana Analytics
  • You are just curious to see if it is possible
  • None of the above!

But, the reality is that regardless of the reason, lots of people still want to export data from Power BI Desktop to different destinations. So let’s have a look at different workarounds until this feature is not available in Power BI. I’ll explain different ways to export Power BI Desktop data in a series of articles. In this post you learn how to copy Power BI Desktop data to a destination file like Excel or CSV without any third-party software involved. I also explain how easy you can export Power BI Desktop data to CSV using DAX Studio.

Copy Data from Data View in Power BI Desktop and Paste it to Destination

The easiest workaround is simply copy/paste data from Data view in Power BI Desktop.

  • Open your Power BI Desktop model
  • Switch to Data view by clicking on Data tab
2017-03-02 15_12_09-AW, Internet Sales - Power BI Desktop.png

You have now 3 options to copy data:

  • Right click on a desired table and click “Copy Table”
2017-03-02 15_18_54-Untitled - Power BI Desktop.png
  • Click a desired table then from the ribbon, click “Copy”
2017-03-02 15_22_39-Exporting data from Power BI to Excel - Microsoft Power BI Community.png
  • Click a desired table, right click on data area then click “Copy Table”
2017-03-02 15_24_43-Untitled - Power BI Desktop.png
  • Now open a new Excel file and paste the copied data
Excel.png

Pros:

  • You can copy the entire table, including all DAX calculated columns, very easily and paste it on a destination like Excel or a text editor and save it as TXT or CSV files
  • You copy transformed and probably cleansed data in case you have done any data transformation in Query Editor
  • No third party tool is needed/involved

Cons:

  • If for any reason you want to paste the data in SQL Server, this might not be the best way to get the job done
  • This way is good for small volume of data. If you want to copy a larger amount of data than some thousands rows, say even 64,000 rows, then the copy process might take a long time
  • There is always a risk of missing data as we are copying data into Windows clipboard

Copy Data from Query Editor in Power BI Desktop and Paste it to Destination

Another easy workaround is to copy data from Query editor:

  • On Power BI Desktop click “Edit Queries” to open Query Editor
  • Select a desired query

Now you have 2 options:

  • Click on the tiny table button located on the top left of the table and click “Copy Entire Table”
2017-03-02 16_17_48-Untitled - Query Editor
  • Press Ctrl+A to select all columns, then press Ctrl+C to copy data or right click on a column header and click “Copy”. You can select multiple columns by pressing Ctrl and clicking on column headers in case you don’t want to copy the entire table.
2017-03-03 09_16_56-Untitled - Query Editor

You can also select different levels of data transformation to be copied.

2017-03-03 09_42_58-Untitled - Query Editor
  • Now you can paste the data to Excel

Pros:

  • You can copy the entire table, very easily and paste it on a destination like Excel or a text editor and save it as TXT or CSV files
  • You can copy multiple selected columns rather than copying the entire table
  • Not only can you copy transformed data in case you have done any data transformation in Query Editor, but also you can decide which level of transformed data should be copied
  • No third party tool is needed/involved

Cons:

  • DAX calculated columns are NOT included
  • If you use Ctrl+A, you select all columns to be copied which includes complex columns automatically added to the table. For a table like DimDate which potentially has lots of relationships with other tables, you probably don’t like it.
2017-03-03 09_52_35-Untitled - Query Editor
  • It’s not that easy to export data in other destinations like SQL Server
  • Poor performance in copying large amount of data
  • Risk of missing data as we are copying data into Windows clipboard

Export Data from Power BI Desktop to CSV or TXT Using DAX Studio

Update: If you’d like to export the model data as a whole, check this out.

Exporting data from Power BI Desktop to CSV using DAX Studio is super easy. An advantage of using DAX Studio is that it works great regardless of the amount of data you want to export. The other advantage is that you can literally export a query output to CSV which can be very helpful if you don’t want to only export the entire table, but a specific query.

Here is how to get the job done in DAX Studio:

  • Open a desired Power BI Desktop model
  • Open DAX Studio
  • Click “PBI / SSDT Model” then select your Power BI model from the list then click “Connect”
2017-03-06 14_09_49-Export Power BI Model Data - Power BI Desktop
  • Write a desired DAX query, I’m not going to explain DAX query language in this article, but you can simply write “EVALUATE TABLE_NAME” which “TABLE_NAME” is the name of the table you want to export. So in our case it is “FactInternetSales”
2017-03-06 14_13_56-Export Power BI Model Data - Power BI Desktop
  • Run, or press F5, to execute the query
  • Click “Results” tab and make sure you get the expected results
2017-03-06 14_15_40-Export Power BI Model Data - Power BI Desktop
  • Click “Output” from the ribbon and select “File”
2017-03-06 14_18_05-Export Power BI Model Data - Power BI Desktop
  • Run the query
  • Select a folder and enter file name to save the results in TXT or CSV then click “Save”
2017-03-06 14_23_11-Export Power BI Model Data - Power BI Desktop
  • Now DAX Studio start writing data into the output file
2017-03-06 14_24_04-Export Power BI Model Data - Power BI Desktop
  • After DAX Studio finished writing data you may open the file
2017-03-06 14_27_12-

Bottom line: You can easily copy/paste your Power BI Desktop data to a destination like Excel or notepad from Power BI Desktop as explained above. You can also export Power BI data using DAX Studio to CSV very easily. However, the first approach it is useful only when you want to copy small amount of data. DAX Studio is super easy and very powerful tool to export data from Power BI Desktop to CSV or TXT files. But, what if you need to load more data to Excel or even SQL Server?

In my next post I’ll explain how to directly import Power BI Desktop data to Excel, so stay tuned. Smile

24 thoughts on “Exporting Data from Power BI Desktop to Excel and CSV – Part 1: Copy & Paste and DAX Studio Methods

  1. Thank you – this was very helpful. Just one problem I’m having. I exported the table from my .pbix table to a .csv file, about 3.5m rows. All data adds up to the penny except one column – it’s way off (low) and I can’t figure out why. I’ve gone back to the source data and compared with my .pbix model and the figures all match up. When the table arrives in the .csv via Dax Studio, the one column doesn’t match up. What could be happening here?

  2. Thanks that’s proved really useful. Is there a DaxStudio expression to export specific columns from different tables in a single CSV. I’ve tried Groupby (Table1ColA, Table1ColB, Table2Col1) but no joy, and neither with SummarizeColumns(). Do I have to create a table expression in PowerBI desktop first? I prefer not to as some of my tables have millions or records

    1. Hi Paul,

      Welcome to BIInsight.com.
      To anser your question I have to say it really depends on your data model.
      It can be a really easy task to write DAX quekries if your relationships are quite clear and your model is well designed.
      On the contrary, if the model is not quite well designed, writing DAX queries can turn to a nightmare.
      The other way that can probably easier, but may not perfect, is to put all needed columns in a table visual, then click ellipsis button on the top right of the visual and export the data to CSV, or Excel if runiing from Power BI Service.
      Hope that helps.
      Cheers

  3. Hi,
    how to export data from app.powerbi.com datasets option or the published workspace option in power bi..any suggestion
    shree

  4. I just can’t export more than a million rows to csv, is there any configuration I should tweak?

  5. Hi Soheil, one of my users want me to exclude a particular column when doing a data export to excel. On Power Bi service workspace she has a report and exports to Excel . She wants all the fields but one on Excel. I know its easy to just delete the column from Excel but I want to check if we any option on Power Bi side. Thanks

    1. Hi Aliya,

      Welcome to BIInsight.com.
      In this post I explained 3 different methods to export data from Power BI Desktop to Excel.
      I also explained some other methods in this post and this one.
      Which method are you using to export your data?
      Please elaborate.

      Thanks.

  6. Hi Soheil,
    I wish to export query output from Power BI Desktop to a MS Access model. I believe it they can’t be linked directly so I need to first export query output into excel and then import the excel file into MS Access. However, when I copy the table and paste it into excel, it does not copy the formatting which is crucial for what I need to do in Access.

    I have close to 200 columns and formatting them again would be time consuming. Could you please let me know whether getting an output from DAX studio maintains the formatting of my table?

    Thank you

    1. Hi Kushal,

      Welcome to BIInsight.com.
      I am afraid DAX Studio cannot keep the formatting as it only takes care of the data migration.
      This is indeed the case for any other data migration tools.
      Column/cell formatting is only for decoration, it doesn’t affect the datatypes.
      So, I’m afraid DAX Studio is not the right choice for your use case.

      Cheers.

  7. I have a PBi file with 12m rows, I have tried DAX Studio but I got errors, does the volume of the file cause the error? or DAX studuo don’t have limitation?
    Thank you

  8. Can you pls share your thoughts on if we can export the matrix visual as-is, right now we are just getting the raw data behind the visual.
    TIA

    1. Hi Pavan,

      Welcome to Biinsight.com.
      That’s a good question indeed and I’m afraid the answer is no we cannot export the data as-is from a matrix visual.
      Power BI is a Tabular data model, which means it sees the data in tables, while a matrix is indeed a pivot table with multiple dimensions.

      I hope that helps you to understand the why…

      Cheers.

  9. Excellent description of the process! I haven’t used DAX Studio often so this got my feet wet AND got my 500K rows of data into Excel from PBI. Looking forward to part 2 as I have a 6M row data set I’d like to try move into Excel’s data model.

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.