Update 2021 March:
You can now export the data direct from Power BI Desktop using my tool, Power BI Exporter. Read more here.
In some of my old posts, which are the most popular ones, I explained how to Export data Power BI Desktop or Power BI Service data to different destinations like CSV, Excel and SQL Server. In this quick tip I explain a very easy way to export the model data as a whole to either CSV or SQL Server with DAX Studio.
Daniil from XXL BI well explained this method, but I’d rather quickly explain how it works and add some more information.
After release 2.8 of DAX Studio, you can now quickly export the whole model to CSV and SQL Server in one shot.
Enabling Export All Data in DAX Studio
- Open DAX Studio
- Click “File”
- Click “Options”
- Click “Advanced”
- Tick “Show Export All Data button”
Export Power BI Model Data to CSV
- Connect to your Power BI Desktop or connect to your Power BI Premium capacity datasets in Power BI Service
- Click “Export Data” from “Advanced” tab
- Select “CSV” as destination
- Enter the output folder path
- Click “Export”
Export Power BI Model Data to SQL Server
Follow the steps explained above, but this time select “SQL Server” as destination. For the “Connection String” follow the below steps to get it right straight away.
Generate Connection String with a UDL File
- Create a text file on your machine, you can simply right click in any desired folder then “New” then click “Text Document”
- Rename the file and swap .txt extension with .udl
- Open the UDL file (double click)
- From “Providers” page, select SQL Server OLE DB Provider for SQL Server then click Next
- Enter Server Name
- Type in your SQL Server User Name and Password
- Tick “Allow saving password”
- Select the destination database
- Click “Test Connection” button to make sure the connection works then click OK
- Now open the UDL file in Notepad
User Connection String in DAX Studio
Now that we’ve generated the connection it is time to use it in DAX Studio.
- Open the UDL file in Notepad
- Copy the connection string starting from after the “Provider” section
- Paste it in DAX Studio in “Connection String” box
- Enter a schema name (it is dbo in my case)
Considerations
- When connecting to a Premium workspace you may face export failure due to query timeout
- When exporting data to SQL Server
- if you leave the “Schema Name” blank you’ll get an error that empty schema is not allowed
- if you enter an existing schema name the data will be exported to tables with exact same name as they have in your model
- if you enter a new schema name then DAX Studio creates a new schema then generate the tables in that schema then exports the data
- whether you tick the “Truncate Tables” or not the existing data will be synchronised with the source data in Power BI. (it doesn’t append data)
- As you probably guessed, this method also works perfectly for exporting SSAS Tabular model and Azure Analysis Services data
Thanks for the guide, using the Export button in the Advanced tab exported all reports in the Power BI file. Is there a way to export specific table / report?
Hi Freelensia,
Welcome to BIInsight.com.
You may find the below links helpful:
Exporting Power BI Data to SQL Server
Exporting Data from Power BI Desktop to Excel and CSV – Part 1: Copy & Paste and DAX Studio Methods
Exporting Data from Power BI Desktop to Excel and CSV – Part 2: Importing Power BI Data Directly to Excel
Cheers
Sir,
Any automate flow or schedule or can we send it through email or onedrive without human touch
Hi Rupesh,
Welcome to BIInsight.com.
Power BI Desktop generates a random port number whenever you open a file, which means if you close the file and reopen it, you’ll get a different port number that before.
Therefore, there is no acceptable way to automate this. That said, you can automate (sort of) finding the Power BI port number. Look at this post’s comments where Zach explains how to “automate the process of getting the port with batch scripts“.
But, again, it won’t be a sustainable design that must be maintained.
Cheers.
Hi, thanks for the guide. Very much appreciated. Is there a limit to the amount of data that can be exported or all the data will be exported to CSV and SQL?
Hi James,
Welcome no BIInsight.com.
I tried exporting models with couple of million rows (lately I exported more than 10M rows) without any problems.
Cheers
Hi Soheil,
Greetings!!
where to deploy this scripts i.e. which Application and section and how to add parameter like sessionname eq console
and finally can we refresh it in every 15 mins like i am doing in app.powerbi.com datasets option using flow
https://powerbi.microsoft.com/en-us/blog/refresh-your-power-bi-dataset-using-microsoft-flow/
“`
@ECHO OFF & SETLOCAL
FOR /F “tokens=2 delims=,” %%F IN (‘TASKLIST /NH /FI “IMAGENAME EQ msmdsrv.exe” /FI “sessionname eq console” /FO CSV’) DO (
SET pid=%%F
)
FOR /F “tokens=2 delims=:” %%F IN (‘NETSTAT /ANO ^| FINDSTR %pid% ^| FINDSTR “127.0.0.1”‘) DO (
SET ipport=%%F
)
FOR /F “tokens=1” %%F IN (“%ipport%”) DO (
SET port=%%F
)
ECHO Local Power BI instance running on port:
ECHO %port%
“`
RUpesh
Hi Rupesh,
Welcome back to BIInsight.com.
I’m not clear on what you’d like to achieve.
You’re referencing to a Microsoft blog about refreshing Power BI Service datasets with Power Automate.
Then you say you’d like to use the CMD scripts and use them in an application.
I’m not too sure either that the above scripts spit put the port number of an opened Power BI Desktop report.
Even if they do, what would like to do with the port number?
As per my reply in your other comment here, currently there is no sustainable solution to automate data refresh in a Power BI Desktop model like how we do in an SSAS Tabular model.
Let me explain.
Let’s say the above scripts give you the Power BI Desktop local port number. Sweet!
Then what if you have more than one Power BI Desktop file open?
The scenario with refreshing your datasets in Power BI Service with Power Automate is a completely different story.
Last but not least, about your comment on refreshing your data every 15 min:
If you have Power BI Pro licence then you only can refresh your dataset in Power BI service up to 8 times a day.
Therefore, 15 minutes data refresh is NOT an option.
Cheers.
Thanks for this, it was super helpful. What if I wanted to only export one table out of the multiple tables in my Power BI data model? How do I do that?
Hi Phillip,
Welcome to BIInsight.com.
Check the posts below out to find out more possibilities:
Cheers
This blog about Quick Tips: Export Power BI Desktop and Power
BI Service Model Data In One Shot with DAX Studio has helped me a lot, is very
well written.
I would like to be able to export data using this method from an old ODBC connection that is extremely slow. Once it is exported I could work on the export rather than the slow original source. Any ideas on how to export this to update rather than just creating new copies? I am fairly new to SQL so please forgive me if my questions simple.
Hi Marvin,
Welcome to BIInsight.com.
I’m not sure if I clearly understand what your question is.
If you already loaded you data from your data source through an ODBC connection into Power BI then this method should work absolutely fine.
If you would like to import data from your data source through an ODBC connection into Power BI but you don’t know how to do that then this post might be helpful.
If you’re after a way to export the data from your data source through ODBC into a SQL Server instance then you may find “SQL Server Import and Export Wizard” tool quite handy.
Hope that helps,
Cheers.
There is a way to publish a data set automatically to an sql server?
Hi Sebastian,
Welcome to BIInsight.com.
Depending on what you mean by dataset, the approach would be different.
If you are after to export the data from a Dataset from Power BI Service without Premium licensing then have a look at this post.
If you own a Premium license and you are also familiar with SSIS, then you may want to take the approach I explained in this post. But, you will require to connect to your Premium dataset on Power BI Service instead of connecting to your Power BI Desktop. Then you will require to schedule the process to automatically run the SSIS packages.
If you did not mean the Dataset in Power BI Service and you are actually referring to the Power BI Desktop data model, then unfortunately there is no sustainable solution at the moment.
Hopefully that helps.
Cheers.
Hi! When we export data form Power bi to SQL through DAX Studio, will the data in SQL update automatically whenever the power bi report refreshes.
Thanks !
Hi Soheil! How do I export Power BI table from query editor to an Oracle Database
Hi Thabo.
The data we see in the Query Editor (Power Query) are not loaded into the data model. Indeed, the query editor shows only a sample data top 1,000 rows). So I don’t think exporting data from the Query Editor is good idea unless you want to use the exported data for testing purposes only.
Having said that, we can currently copy the entire table (based on the transformation step we are at) then paste it into the destination.
Perhaps none of these are helpful for your needs, but you might want to paste the copied data into an Oracle table. This method has limitations as well. For instance, it does not work if you’re copying a large table.
I hope that helps.
Cheers