Quick Tips: Export Power BI Desktop and Power BI Service Model Data In One Shot with DAX Studio

Exporting Model Data to CSV 
or SQL Server in One Shot

In some of my old posts, which are 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”
DAX Studio Export Power BI Model Data Settings

Export Power BI Model Data to CSV

DAX Studio Export Power BI Model Data to CSV

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”
Create a TXT file
  • Rename the file and swap .txt extension with .udl
Rename TXT file to 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
Create Connection String with UDL
  • 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)
Export Power BI Model Data to SQL Server

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
Exporting SSAS Tabular Model data to SQL Server with DAX Studio

11 thoughts on “Quick Tips: Export Power BI Desktop and Power BI Service Model Data In One Shot with DAX Studio

  1. 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?

      1. Sir,
        Any automate flow or schedule or can we send it through email or onedrive without human touch

        1. 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.

  2. 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?

  3. 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

    1. 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.

  4. 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?

  5. 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.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.