How to Get Underlying Data in Power BI

Ever look at a polished bar chart in a Power BI report and think, "I trust the big number, but I want to see the actual rows of data that make it up?" This instinct to validate and dig deeper is what separates a casual report viewer from a true data analyst. Understanding the raw transactions behind a summary visual is crucial for building confidence in your data, troubleshooting anomalies, and uncovering hidden insights. This guide will walk you through the primary methods for accessing the underlying data within any Power BI visual.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Data Validation Is a Superpower

Before jumping into the "how," it's helpful to know the "why." Viewing the records behind a summary KPI or chart isn't just about satisfying curiosity. It’s a core part of the data analysis workflow and helps you:

• Validate Your Data: Seeing the individual rows is the best way to confirm that the filters, measures, and relationships in your model are working as expected. If the total sales figure for "California" seems off, looking at the underlying transactions will quickly reveal if an incorrect date filter is the culprit.
• Perform Deeper Analysis: A summary chart might show you that overall revenue is up, but the underlying data can tell you which specific products or customer segments are driving that growth. You can often spot trends in the details that are invisible at the aggregate level.
• Troubleshoot Discrepancies: When a number in your Power BI report doesn't match an external system (like your accounting software), the first step is always to examine the underlying records. This allows you to compare apples-to-apples and find the source of the disagreement.

Method 1: The Quick Spot-Check with "Show Data Point as a Table"

This is the fastest and most common way to investigate a specific part of a visual. It allows you to isolate a single data point - like one bar in a bar chart or one slice of a pie chart - and see only the rows that contribute to it.

Imagine you have a column chart showing your total sales by product category, and the "Electronics" category looks surprisingly high. Here’s how you’d use this feature to investigate:

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step-by-Step Guide:

1. Open your Power BI report and navigate to the visual in question.
2. Right-click the specific data element you want to inspect. In our example, you would right-click directly on the "Electronics" bar.
3. From the context menu that appears, select "Show data point as a table."

Power BI will then take you to a new display. The top half will still show your original chart, but the bottom half will now feature a table containing only the rows related to the 'Electronics' category. You can scroll through this table to examine every single transaction that rolled up into that one bar.

To return to your full report, simply click the "Back to report" button in the upper-left corner.

This feature is perfect for quick, focused investigations without having to leave the context of your dashboard.

Method 2: Exporting All the Data Behind a Visual

Sometimes, you don't just want to see the data for a single point, you want the entire dataset that populates the visual so you can analyze it further in another tool like Excel. Maybe you need to perform calculations that aren’t built into the report or merge the data with another source.

Power BI's "Export data" option, found in the visual's ellipsis (...) menu, is your go-to for this.

Let's stick with our sales by product category example.

Step-by-Step Guide:

1. Hover your mouse over the visual you want to export data from. You'll see a small menu appear in the top-right corner.
2. Click the ellipsis (...) icon, which stands for "More options."
3. In the dropdown menu, click on "Export data."

This will bring up a dialog box that presents you with a critical choice, and understanding the difference is key.

Understanding Your Export Options

The dialog box will ask you how you want your data formatted, giving you two main choices:

• Summarized data: This option exports the data exactly as you see it displayed in the visual. If your chart shows sales for five product categories, this export will give you a file with just those five rows of high-level, aggregated data. It’s useful for quickly grabbing the summary numbers to paste into a presentation or email.
• Underlying data: This is the one we're here for. This option exports the raw, detailed, row-level data that Power BI uses behind the scenes to calculate the summary. Instead of five rows, this export might give you thousands of individual sales transaction records. This is the dataset you need for true deep-dive analysis and validation.

You’ll typically have the option to export as an XLSX (Excel) file or a CSV file. Choose the appropriate format, and the file will be downloaded for you to use.

Heads Up: The ability to export underlying data is a permission set by the report's creator. If you don't see this option or it's grayed out, it’s not an error. It means the report administrator has intentionally disabled it, which we'll cover in more detail below.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Method 3: The Power User Move with Performance Analyzer

For those who are more technical or need to understand exactly what Power BI is doing under the hood, there’s an even more powerful method: the Performance Analyzer. This tool is designed to help report developers optimize their visuals, but it has a fantastic side effect - it shows you the exact DAX query that Power BI generates to pull the data for any given visual.

This is the ultimate method for validation because you’re not just seeing the data, you’re seeing the code that retrieves the data. It's perfect for debugging complex measures and confirming business logic.

Step-by-Step Guide:

1. With your report open in Power BI Desktop, go to the "View" tab in the top ribbon.
2. In the "Show panes" section, click on "Performance Analyzer." A new pane will appear on the right side of your screen.
3. Click the "Start recording" button within the Performance Analyzer pane.
4. Now click "Refresh visuals" right next to the start button. Power BI will reload all the visuals on the page and record how long each step takes.
5. You’ll see each visual listed in the pane. You can expand a specific visual to see how long its "DAX query" took.
6. Just below the query duration, you'll see a link that says "Copy query." Click it!

You now have the exact DAX query on your clipboard. You can paste this into a tool like DAX Studio to run and review the full table of results, or you can even go to the "Modeling" tab in Power BI, click "New Table," and paste the query there to create a physical table in your model based on that visual's data. This gives you complete transparency into the data pipeline of your report.

Common Roadblocks and What They Mean

Sometimes, getting to the underlying data isn't straightforward. There are a few common hurdles you might encounter, but they are almost always by design rather than a bug.

You Can't See the "Underlying Data" Option

If the option to export underlying data is missing or grayed out, it means the report administrator has disabled it. There are several good reasons for this:

• Data Sensitivity: The underlying data may contain PII (Personally Identifiable Information) like customer names or emails, which should not be widely distributed via Excel exports.
• Performance: Exporting millions of rows of data can put a heavy strain on the Power BI service or your data source. Admins might disable it to ensure the report remains performant for all users.
• Controlling Data Flow: Organizations often want their official BI report to be the single source of truth and discourage the proliferation of stale, offline copies of the data in spreadsheets.

If you have a legitimate business need for this data, you'll need to reach out to the report owner or your Power BI administrator to request access.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Your Export is Slow or Fails

Power BI imposes limits on export size to maintain system performance. In the Power BI service, you can typically export a maximum of 150,000 rows to .xlsx and 30,000 rows to .csv. If your underlying dataset is larger than these limits, the export may time out or fail. If you're working in Power BI Desktop, the limits are much higher. If you need a larger dataset, your best bet is to apply more filters to the visual before exporting to reduce the row count.

Final Thoughts

Knowing how to see the underlying data in Power BI is all about building trust and moving beyond surface-level insights. Whether you're making a quick spot-check with a right-click, exporting a full dataset for extended analysis, or grabbing the source DAX query via the Performance Analyzer, these techniques empower you to validate numbers and dig deeper into the real story your data is telling.

At Graphed, we admire the power of tools like Power BI but wanted to make the process of getting answers from your data feel less like navigating a complex piece of software. Instead of digging through menus and wrestling with permissions, our platform allows you to connect all your marketing and sales data sources in one place and simply ask questions conversationally. You can ask things like, "Break down my Shopify sales by product for last month," and get an instant report without ever leaving a simple chat interface. It streamlines analysis by cutting out the busywork and letting you focus on the insights, not the clicks.