Replace 2021 March:
Now you can export the info direct from Energy BI Desktop utilizing my software, Energy BI Exporter. Learn extra right here.
Replace 2019 April:
If you happen to’re curious about exporting the info mannequin from both Energy BI Desktop or Energy BI Service to CSV or SQL Server examine this out. The strategy defined right here is just relevant for Energy BI Premium or Embedded capacities with XMLA endpoints connectivity.
One of many options that’s requested lots on Energy BI neighborhood is the way to export Energy BI desktop knowledge to Excel OR CSV.
Observe: I’d wish to make it clear that on this article we’re NOT curious about exporting knowledge from a visible in a report back to CSV.
The primary query numerous you may need is “How on earth somebody desires to export knowledge from a Energy BI Desktop mannequin to Excel OR CSV?”.
Energy BI brings Energy Question, Energy Pivot, Energy View and Energy Map collectively in a single piece of software program. So why not utilizing Excel on the first place to load knowledge from the supply? There is likely to be numerous different questions concerning the causes that somebody desires to export knowledge from Energy BI Desktop mannequin to Excel (or some other locations). The rationale might be one of many following that somebody desires to export knowledge from Energy BI Desktop mannequin to different locations like Excel, CSV, SQL Server and so on.
- For some motive you’ve got only a Energy BI Desktop file (PBIX) and also you don’t have entry to the info sources and it’s essential to present the info to somebody who isn’t conversant in Energy BI
- You Energy BI Desktop consolidates numerous completely different sources in a single mannequin and it will be very laborious to get the identical output as you get in Energy BI Desktop mannequin in Excel. So an export characteristic may be tremendous helpful
- You may need finished numerous complicated transformations in Energy BI Question Editor and replication the identical logic on the supply system might be far more complicated and time consuming, so once more exporting knowledge from a present Energy BI Desktop mannequin is smart
- You’ve got a bunch of calculated columns created in DAX and also you don’t wish to return and redo all of the laborious works you’ve got already finished in Energy BI in one other atmosphere like Excel
- You would possibly wish to use the present Energy BI knowledge in Cortana Analytics
- You’re simply curious to see whether it is attainable
- Not one of the above!
However, the fact is that whatever the motive, numerous folks nonetheless wish to export knowledge from Energy BI Desktop to completely different locations. So let’s take a look at completely different workarounds till this characteristic isn’t out there in Energy BI. I’ll clarify other ways to export Energy BI Desktop knowledge in a sequence of articles. On this put up you learn to copy Energy BI Desktop knowledge to a vacation spot file like Excel or CSV with none third-party software program concerned. I additionally clarify how simple you may export Energy BI Desktop knowledge to CSV utilizing DAX Studio.
The best workaround is solely copy/paste knowledge from Information view in Energy BI Desktop.
- Open your Energy BI Desktop mannequin
- Change to Information view by clicking on Information tab
You’ve got now 3 choices to repeat knowledge:
- Proper click on on a desired desk and click on “Copy Desk”
- Click on a desired desk then from the ribbon, click on “Copy”
- Click on a desired desk, proper click on on knowledge space then click on “Copy Desk”
- Now open a brand new Excel file and paste the copied knowledge
Professionals:
- You may copy all the desk, together with all DAX calculated columns, very simply and paste it on a vacation spot like Excel or a textual content editor and put it aside as TXT or CSV information
- You copy remodeled and possibly cleansed knowledge in case you’ve got finished any knowledge transformation in Question Editor
- No third get together software is required/concerned
Cons:
- If for any motive you wish to paste the info in SQL Server, this may not be the easiest way to get the job finished
- This manner is nice for small quantity of information. If you wish to copy a bigger quantity of information than some hundreds rows, say even 64,000 rows, then the copy course of would possibly take a very long time
- There’s at all times a danger of lacking knowledge as we’re copying knowledge into Home windows clipboard
One other simple workaround is to repeat knowledge from Question editor:
- On Energy BI Desktop click on “Edit Queries” to open Question Editor
- Choose a desired question
Now you’ve got 2 choices:
- Click on on the tiny desk button situated on the highest left of the desk and click on “Copy Whole Desk”
- Press Ctrl+A to pick all columns, then press Ctrl+C to repeat knowledge or proper click on on a column header and click on “Copy”. You may choose a number of columns by urgent Ctrl and clicking on column headers in case you don’t wish to copy all the desk.
You may also choose completely different ranges of information transformation to be copied.
- Now you may paste the info to Excel
Professionals:
- You may copy all the desk, very simply and paste it on a vacation spot like Excel or a textual content editor and put it aside as TXT or CSV information
- You may copy a number of chosen columns fairly than copying all the desk
- Not solely are you able to copy remodeled knowledge in case you’ve got finished any knowledge transformation in Question Editor, but in addition you may determine which stage of remodeled knowledge needs to be copied
- No third get together software is required/concerned
Cons:
- DAX calculated columns are NOT included
- If you happen to use Ctrl+A, you choose all columns to be copied which incorporates complicated columns routinely added to the desk. For a desk like DimDate which doubtlessly has numerous relationships with different tables, you in all probability don’t prefer it.
- It’s not that simple to export knowledge in different locations like SQL Server
- Poor efficiency in copying great amount of information
- Danger of lacking knowledge as we’re copying knowledge into Home windows clipboard
Export Information from Energy BI Desktop to CSV or TXT Utilizing DAX Studio
Replace: If you happen to’d wish to export the mannequin knowledge as a complete, examine this out.
Exporting knowledge from Energy BI Desktop to CSV utilizing DAX Studio is tremendous simple. A bonus of utilizing DAX Studio is that it really works nice whatever the quantity of information you wish to export. The opposite benefit is which you can actually export a question output to CSV which may be very useful in the event you don’t wish to solely export all the desk, however a selected question.
Right here is the way to get the job finished in DAX Studio:
- Open a desired Energy BI Desktop mannequin
- Open DAX Studio
- Click on “PBI / SSDT Mannequin” then choose your Energy BI mannequin from the listing then click on “Join”
- Write a desired DAX question, I’m not going to clarify DAX question language on this article, however you may merely write “EVALUATE TABLE_NAME” which “TABLE_NAME” is the title of the desk you wish to export. So in our case it’s “FactInternetSales”
- Run, or press F5, to execute the question
- Click on “Outcomes” tab and be sure you get the anticipated outcomes
- Click on “Output” from the ribbon and choose “File”
- Run the question
- Choose a folder and enter file title to save lots of the ends in TXT or CSV then click on “Save”
- Now DAX Studio begin writing knowledge into the output file
- After DAX Studio completed writing knowledge you might open the file
Backside line: You may simply copy/paste your Energy BI Desktop knowledge to a vacation spot like Excel or notepad from Energy BI Desktop as defined above. You may also export Energy BI knowledge utilizing DAX Studio to CSV very simply. Nevertheless, the primary strategy it’s helpful solely whenever you wish to copy small quantity of information. DAX Studio is tremendous simple and really highly effective software to export knowledge from Energy BI Desktop to CSV or TXT information. However, what if it’s essential to load extra knowledge to Excel and even SQL Server?
In my subsequent put up I’ll clarify the way to straight import Energy BI Desktop knowledge to Excel, so keep tuned. 
