Exporting Knowledge from Energy BI Desktop to Excel and CSV – Half 1: Copy & Paste and DAX Studio Strategies

Replace 2021 March:

Now you can export the info direct from Energy BI Desktop utilizing my device, Energy BI Exporter. Learn extra right here.

Replace 2019 April:

For those who’re excited about exporting the info mannequin from both Energy BI Desktop or Energy BI Service to CSV or SQL Server test this out. The tactic defined right here is barely relevant for Energy BI Premium or Embedded capacities with XMLA endpoints connectivity.

One of many options that’s requested so much on Energy BI group is tips on how 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 excited about exporting knowledge from a visible in a report back to CSV.

The primary query a lot of you might 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 may be a lot of different questions concerning the causes that somebody desires to export knowledge from Energy BI Desktop mannequin to Excel (or another locations). The explanation 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 many others.

• For some purpose you’ve only a Energy BI Desktop file (PBIX) and also you don’t have entry to the info sources and you have to present the info to somebody who just isn’t accustomed to Energy BI
• You Energy BI Desktop consolidates a lot of totally different sources in a single mannequin and it might be very laborious to get the identical output as you get in Energy BI Desktop mannequin in Excel. So an export function may be tremendous useful
• You might need carried out a lot of advanced transformations in Energy BI Question Editor and replication the identical logic on the supply system might be far more advanced and time consuming, so once more exporting knowledge from a present Energy BI Desktop mannequin is smart
• You may have 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 already carried out in Energy BI in one other surroundings like Excel
• You may wish to use the present Energy BI knowledge in Cortana Analytics
• You’re simply curious to see whether it is doable
• Not one of the above!

However, the fact is that whatever the purpose, a lot of folks nonetheless wish to export knowledge from Energy BI Desktop to totally different locations. So let’s take a look at totally different workarounds till this function just isn’t obtainable in Energy BI. I’ll clarify other ways to export Energy BI Desktop knowledge in a collection 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 possibly can export Energy BI Desktop knowledge to CSV utilizing DAX Studio.

The best workaround is just copy/paste knowledge from Knowledge view in Energy BI Desktop.

• Open your Energy BI Desktop mannequin
• Change to Knowledge view by clicking on Knowledge tab

You may have 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

Execs:

• You may copy your entire 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 doubtless cleansed knowledge in case you’ve carried out any knowledge transformation in Question Editor
• No third occasion device is required/concerned

Cons:

• If for any purpose you wish to paste the info in SQL Server, this may not be one of the simplest ways to get the job carried out
• This manner is nice for small quantity of information. If you wish to copy a bigger quantity of information than some 1000’s rows, say even 64,000 rows, then the copy course of may take a very long time
• There may be 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 2 choices:

• Click on on the tiny desk button positioned on the highest left of the desk and click on “Copy Complete 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 your entire desk.

You can too choose totally different ranges of information transformation to be copied.

• Now you possibly can paste the info to Excel

Execs:

• You may copy your entire 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 somewhat than copying your entire desk
• Not solely are you able to copy remodeled knowledge in case you’ve carried out any knowledge transformation in Question Editor, but in addition you possibly can determine which degree of remodeled knowledge needs to be copied
• No third occasion device is required/concerned

Cons:

• DAX calculated columns are NOT included
• For those who use Ctrl+A, you choose all columns to be copied which incorporates advanced columns mechanically added to the desk. For a desk like DimDate which probably has a lot of relationships with different tables, you most likely 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 Knowledge from Energy BI Desktop to CSV or TXT Utilizing DAX Studio

Replace: For those who’d wish to export the mannequin knowledge as an entire, test 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 could actually export a question output to CSV which may be very useful if you happen to don’t wish to solely export your entire desk, however a particular question.

Right here is tips on how to get the job carried out 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 checklist then click on “Join”

• Write a desired DAX question, I’m not going to clarify DAX question language on this article, however you possibly can merely write “EVALUATE TABLE_NAME” which “TABLE_NAME” is the identify 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 identify to save lots of the leads to TXT or CSV then click on “Save”

• Now DAX Studio begin writing knowledge into the output file

• After DAX Studio completed writing knowledge chances are you’ll 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 can too export Energy BI knowledge utilizing DAX Studio to CSV very simply. Nevertheless, the primary strategy it’s helpful solely if you wish to copy small quantity of information. DAX Studio is tremendous simple and really highly effective device to export knowledge from Energy BI Desktop to CSV or TXT information. However, what if you have to load extra knowledge to Excel and even SQL Server?

In my subsequent put up I’ll clarify tips on how to straight import Energy BI Desktop knowledge to Excel, so keep tuned.