Thursday, September 29, 2022
HomeBusiness IntelligenceExporting Information from Energy BI Desktop to Excel and CSV – Half...

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


AAEAAQAAAAAAAAz4AAAAJGQ5ZTk4ZGUxLTI4YjQtNDc4ZC05NTQ4LWRjNDk3OTBlYTE5OQ

Replace 2021 March:

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

Replace 2019 April:

When you’re fascinated with exporting the information mannequin from both Energy BI Desktop or Energy BI Service to CSV or SQL Server test this out. The tactic defined right here is simply relevant for Energy BI Premium or Embedded capacities with XMLA endpoints connectivity.

One of many options that’s requested rather a lot on Energy BI neighborhood is the way to export Energy BI desktop knowledge to Excel OR CSV.

Notice: I’d prefer to make it clear that on this article we’re NOT fascinated with exporting knowledge from a visible in a report back to CSV.

The primary query a number 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 is perhaps a number of different questions in regards to 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 many others.

  • For some cause you’ve gotten only a Energy BI Desktop file (PBIX) and also you don’t have entry to the information sources and you have to present the information to somebody who will not be acquainted with Energy BI
  • You Energy BI Desktop consolidates a number of totally 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 function could be tremendous useful
  • You might need carried out a number 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’ve a bunch of calculated columns created in DAX and also you don’t need to return and redo all of the laborious works you’ve gotten already carried out in Energy BI in one other setting like Excel
  • You would possibly need to use the present Energy BI knowledge in Cortana Analytics
  • You might be simply curious to see whether it is attainable
  • Not one of the above!

However, the fact is that whatever the cause, a number of folks nonetheless need to export knowledge from Energy BI Desktop to totally different locations. So let’s take a look at totally different workarounds till this function will not be out there in Energy BI. I’ll clarify other ways to export Energy BI Desktop knowledge in a collection of articles. On this submit 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’ll be able to 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
2017-03-02 15_12_09-AW, Internet Sales - Power BI Desktop.png

You’ve now 3 choices to repeat knowledge:

  • Proper click on on a desired desk and click on “Copy Desk”
2017-03-02 15_18_54-Untitled - Power BI Desktop.png
  • Click on a desired desk then from the ribbon, click on “Copy”
2017-03-02 15_22_39-Exporting data from Power BI to Excel - Microsoft Power BI Community.png
  • Click on a desired desk, proper click on on knowledge space then click on “Copy Desk”
2017-03-02 15_24_43-Untitled - Power BI Desktop.png
  • Now open a brand new Excel file and paste the copied knowledge
Excel.png

Professionals:

  • You may copy the whole 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 recordsdata
  • You copy reworked and doubtless cleansed knowledge in case you’ve gotten carried out any knowledge transformation in Question Editor
  • No third social gathering device is required/concerned

Cons:

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

  • Click on on the tiny desk button positioned on the highest left of the desk and click on “Copy Whole Desk”
2017-03-02 16_17_48-Untitled - Query Editor
  • 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 need to copy the whole desk.
2017-03-03 09_16_56-Untitled - Query Editor

You can even choose totally different ranges of knowledge transformation to be copied.

2017-03-03 09_42_58-Untitled - Query Editor
  • Now you’ll be able to paste the information to Excel

Professionals:

  • You may copy the whole 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 recordsdata
  • You may copy a number of chosen columns quite than copying the whole desk
  • Not solely are you able to copy reworked knowledge in case you’ve gotten carried out any knowledge transformation in Question Editor, but additionally you’ll be able to determine which degree of reworked knowledge needs to be copied
  • No third social gathering device is required/concerned

Cons:

  • DAX calculated columns are NOT included
  • When you use Ctrl+A, you choose all columns to be copied which incorporates advanced columns robotically added to the desk. For a desk like DimDate which doubtlessly has a number of relationships with different tables, you most likely don’t prefer it.
2017-03-03 09_52_35-Untitled - Query Editor
  • It’s not that simple to export knowledge in different locations like SQL Server
  • Poor efficiency in copying great amount of knowledge
  • Threat 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: When you’d prefer to export the mannequin knowledge as a complete, 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 knowledge you need to export. The opposite benefit is that you could actually export a question output to CSV which could be very useful in the event you don’t need to solely export the whole desk, however a particular question.

Right here is the way 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 listing then click on “Join”
2017-03-06 14_09_49-Export Power BI Model Data - Power BI Desktop
  • Write a desired DAX question, I’m not going to clarify DAX question language on this article, however you’ll be able to merely write “EVALUATE TABLE_NAME” which “TABLE_NAME” is the identify of the desk you need to export. So in our case it’s “FactInternetSales”
2017-03-06 14_13_56-Export Power BI Model Data - Power BI Desktop
  • Run, or press F5, to execute the question
  • Click on “Outcomes” tab and be sure to get the anticipated outcomes
2017-03-06 14_15_40-Export Power BI Model Data - Power BI Desktop
  • Click on “Output” from the ribbon and choose “File”
2017-03-06 14_18_05-Export Power BI Model Data - Power BI Desktop
  • Run the question
  • Choose a folder and enter file identify to save lots of the ends in TXT or CSV then click on “Save”
2017-03-06 14_23_11-Export Power BI Model Data - Power BI Desktop
  • Now DAX Studio begin writing knowledge into the output file
2017-03-06 14_24_04-Export Power BI Model Data - Power BI Desktop
  • After DAX Studio completed writing knowledge you could open the file
2017-03-06 14_27_12-

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 even export Energy BI knowledge utilizing DAX Studio to CSV very simply. Nevertheless, the primary method it’s helpful solely once you need to copy small quantity of knowledge. DAX Studio is tremendous simple and really highly effective device to export knowledge from Energy BI Desktop to CSV or TXT recordsdata. However, what if you have to load extra knowledge to Excel and even SQL Server?

In my subsequent submit I’ll clarify the way to straight import Energy BI Desktop knowledge to Excel, so keep tuned. Smile

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments