How to Get Data from Power BI Dataset

You’ve done the hard work of building and modeling your data into a powerful Power BI dataset. But now you need to get some of that curated data back out - perhaps for another report in Excel, a handoff to another team, or a quick ad-hoc analysis. Fortunately, Power BI provides several ways to do this, ranging from simple clicks to powerful, developer-focused connections. This article will walk you through the five primary methods for getting data from a Power BI dataset, so you can choose the best one for your needs.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Quickest Export: Pulling Data Directly from Visuals

The most straightforward method for getting data out of Power BI is by exporting it directly from a report visual. This is perfect when you just need the specific data shown in a chart or table to do some quick follow-up analysis in a spreadsheet.

Step-by-Step Instructions

1. Open your Power BI report in either Power BI Desktop or the Power BI Service.
2. Hover over the visual you want to extract data from.
3. Click the ellipsis icon (...) in the top-right corner of the visual.
4. Select Export data from the dropdown menu.

Once you click "Export data," you'll usually get a few options:

• Data with current layout: This option, typically only available for tables and matrix visuals, exports the data to an .xlsx file while preserving the formatting and structure you see on the screen. It is limited to 150,000 rows.
• Summarized data: This exports the aggregated data used to create the visual. If your bar chart shows total sales by country, you'll get a table with two columns: Country and Total Sales. You can export as an .xlsx file (up to 150,000 rows) or a .csv file (up to 30,000 rows).
• Underlying data: This option gives you the raw, unsummarized data rows that are being aggregated in your visual. This is incredibly useful for seeing the details behind a summary number. The export limits still apply, and your Power BI admin or the report author must have this feature enabled.

This method is fantastic for its speed and simplicity, but the row limitations mean it’s not suitable for exporting large tables or entire datasets.

For the Spreadsheet Gurus: Analyze in Excel

If you live and breathe Excel, the "Analyze in Excel" feature is a game-changer. Instead of exporting a static snapshot of your data, this feature creates a live connection from an Excel PivotTable directly to your Power BI dataset. This lets you slice, dice, and explore your Power BI data model using the familiar interface of an Excel PivotTable.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Get Started

1. In the Power BI Service, navigate to the workspace containing your dataset.
2. Find your dataset in the list (look for the "Dataset" type).
3. Click the ellipsis (...) next to the dataset name and select Analyze in Excel.
4. An .odc (Office Data Collection) file will download to your computer.
5. Open this file. You may need to enable content or sign in with your Power BI credentials for security.

Excel will open with a blank PivotTable. On the right, the "PivotTable Fields" pane will be populated with all the tables, columns, and measures from your Power BI dataset. You can now drag and drop fields to build reports, create charts, and explore the data, all while staying connected to the single source of truth in the Power BI service. Any changes to the dataset will be reflected in your Excel workbook upon refreshing.

This method doesn't export raw rows into cells, it provides an interactive analytical connection, which is often much more powerful.

For Bulk Data Extraction: Using DAX Studio

When you need to export an entire table or tens of thousands of rows that exceed standard export limits, you need to bring in a specialized tool. DAX Studio is a free, powerful community tool designed for querying and working with Power BI and Analysis Services data models.

Getting Data with DAX Studio

DAX Studio offers two main ways to connect to your data:

1. From a Local Power BI Desktop File (.pbix)

• Open your Power BI report in Power BI Desktop.
• With the report open, launch DAX Studio.
• The DAX Studio connection window will automatically detect and show your open .pbix file. Select it and click Connect.

2. From the Power BI Service (Requires Power BI Premium or PPU)

• The connection to shared datasets in the Power BI Service uses something called the XMLA endpoint. You’ll need a Power BI Premium Per User or Premium capacity workspace for this.
• In the Power BI service, go to your workspace settings, select Premium, and copy the Workspace Connection URL. This is your XMLA endpoint.
• In DAX Studio, select the Tabular Server option and paste the URL.
• Proceed to log in with your credentials.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Writing a Query to Extract Data

Once connected, you have full access to your data model. To extract an entire table, you write a simple DAX query using the EVALUATE statement.

For example, to get every row from a table named 'Sales', the query is simple:

EVALUATE 'Sales'

You can also create more complex queries to get specific columns or filtered rows. After writing your query, go to the "Output" tab in DAX Studio and select "File." This tells DAX Studio to stream the query results directly to a file instead of trying to load it all into memory first. You can choose to save as a CSV or a SQL table data file. Now, click "Run" and millions of rows will be efficiently exported without crashing your machine.

Enterprise-Grade Access: The XMLA Endpoint

The XMLA endpoint isn't just for DAX Studio, it acts as a universal gateway to your Power BI Premium dataset. It effectively turns your dataset into a first-class enterprise database that other data tools can connect to and query directly.

This is the most advanced and flexible method, intended for scenarios where you need to integrate your Power BI dataset with other backend processes or enterprise tools, such as SQL Server Management Studio (SSMS).

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Connecting with SQL Server Management Studio (SSMS)

1. Copy your workspace's XMLA endpoint URL from the Power BI Service settings.
2. Open SSMS. In the "Connect to Server" dialog, select Analysis Services as the server type.
3. Paste the URL into the "Server name" field.
4. For authentication, choose Azure Active Directory - Universal with MFA and enter your Power BI email.
5. Click Connect and complete the authentication process.

Once connected, you can browse your dataset's tables, columns, and measures in the SSMS Object Explorer just as you would with a traditional database. You can open an MDX or DAX query window and run scripts directly against the model, providing powerful programmatic control over data retrieval.

For Pixel-Perfect Reports: Power BI Report Builder

Sometimes you don't want raw data, but a highly formatted, printable report - like an invoice, a packing list, or a formal financial statement. This is where Power BI Paginated Reports come in, and a tool called Power BI Report Builder is used to create them.

How it Works

1. Download and install Power BI Report Builder (it's a free, separate application).
2. Inside Report Builder, create a new data source and select Power BI Dataset as the source type.
3. Connect to your Power BI workspace and select the dataset you want to use.
4. This connects the builder to your model. Next, you can use the built-in query designer or write a custom DAX query to specify exactly which data you need for your report.
5. Design your report by dragging fields onto a canvas to create precise tables, matrices, and charts.
6. Once your report is designed, you can run it and export the results to various formats like PDF, Word, Excel, PowerPoint, and more, without any row limitations.

Final Thoughts

As you can see, Power BI offers a variety of methods to extract data, covering everything from a quick data export for a curious analyst to a formal, enterprise-level connection for a data engineer. Choosing the right method depends entirely on your goal, your access level, and the tool you're most comfortable with.

Navigating different tools, writing DAX queries, and managing export limits can be time-consuming when all you need is a quick answer. At Graphed, we streamline this entire process. We allow you to connect all your data sources, like Google Analytics and ads platforms, into a single place. Then, instead of jumping through hoops to extract data, you can simply ask questions in plain English - like "what were my top-performing ad campaigns by revenue last month?" - and get back interactive charts and dashboards instantly.