Exporting all data from a Power BI data model

The latest version of DAX Studio, 2.8.0, was released yesterday. It has several new features, one of which stands out for me: Export All Data. While you could write your queries to CSV files or Excel before, this feature allows you to export the whole data model at once to CSV files or — and this is why I really like it — SQL Server. In this blog post, I am showing how to do it.

Because Export All Data is a preview feature, you need to enable it before you can use it. To do so, click File > Options > Advanced and check Show Export All Data button:

This will add the Advanced ribbon with the Export Data button:

Clicking the button will open the Export Data dialog box.

Exporting to CSV files

The first option, CSV, allows you to choose a folder that will be populated with CSV exports of all tables from your data model.

Exporting to SQL Server

Alternatively, you can now write all your tables into a SQL Server database! For this, you need to specify a connection string and schema name:

  • The connection string should be in a format similar to the following:
    Server={server address};Initial Catalog={database name};User ID={your username};Password={your password};
  • The schema name can be either an existing one, or DAX Studio can create a new one for you.
  • You also have an option to truncate tables in case you already have them.

The best part is that DAX Studio respects data types, and you don’t have to worry about them — the process is fully automated.

Other considerations

Note that all the names will be exactly as they are in the data model, including spaces.

The new feature saves a lot of time compared to writing EVALUATE queries for each table one by one. Next month, I imagine the same feature will allow us to export data from Power BI service datasets in Premium capacity, once the XMLA protocol is supported by Power BI service.

  • D Gosbell

    We’d still like to improve the UI of this feature a bit more adding an option to cancel the operation (as it could run for a long time on a big model) and maybe improving the SQL connection experience. Note that if you are using windows auth you can export to SQL using the following style of connection string “Server={server address};Database={database name};Trusted_Connection=true”

  • Nice new feature, thanks! I have a couple quality-of-life suggestions. First, consider cleaning characters in tables that are forbidden in the Windows file system so that the process doesn’t fail because of characters such as colons. Second, it would be nice if the UI remembered the last used path.

  • David

    I am trying to connect to a SQL Server, specifically Azure SQL Data Warehouse, and DAX Studio apparently starts passing the first of many tables but after a couple of seconds nothing else happens. What am I doing wrong? thanks in advanced!

    • I can see you’ve already posted an issue on GitHub (#165) — that’s the right thing to do 🙂

      • David

        Solved! the new version 2.8.3 of DAX Studio is working better. Thanks anyway!

  • Kresimir Ledinski

    What an elegant and much waited solution! thank you for saving a lot of manual work! cheers!

  • UBG

    Hi Daniil Maslyuk,
    impressive feature. thank you so much sharing.
    i need small clarification, is this possible export into mysql database by using daxstudio?

    please help me on this…

    thanks,
    UBG

    • I don’t think you can do it directly, but I don’t see why you can’t export to CSVs first and load them to MySQL.

      • UBG

        I am doing the same way now but it is manual process. Every day I need to load data to mysql.i am thinking on automate the process or at least reduce some effort which normally taking in manual process .
        Please guide me if you have any idea.

        Thank you so much Daniil..🙂

        • You can set up an Azure SQL Database and export data from your Power BI files there, then use Azure Data Factory to copy from Azure SQL Database to MySQL 🙂 I have no knowledge of MySQL though, so I can’t be sure if this’ll definitely work.