Others

How to Display All Fact Table Records in a Power BI Table Visual

Huda Qurayshi Oct 10, 2026 869 views

Question details

The user needs to force a Power BI table visual to display all underlying rows from a fact table instead of aggregating them into distinct values.

How to Display All Fact Table Records in a Power BI Table Visual

Product: Microsoft Power BI
Device & OS: not provided
Scenario: Creating a table visual or exporting data from Power BI where granular, row-level data is required without automatic summarization.
Observed behavior: The table visual aggregates data, showing fewer records than the source fact table by only returning distinct values.

Before you start

Before troubleshooting, verify the exact row count of your source fact table in the Data view and ensure no hidden page-level or visual-level filters are excluding your data.

Solution 1Recommended

Add an Index Column to Prevent Aggregation

Power BI automatically groups identical rows in a table visual. Adding a unique Index column ensures every single row is treated as distinct and displayed.

By default, the table visual in Power BI behaves like a standard SQL GROUP BY query. If multiple rows in your fact table contain the exact same data across all displayed columns, Power BI will collapse them into a single row. Introducing a column with a unique identifier breaks this grouping.

Open Power Query Editor

Click on 'Transform Data' on the Home ribbon in Power BI Desktop to open the Power Query Editor.

Add an Index Column

Select your fact table, go to the 'Add Column' tab, and click on 'Index Column' (choose 'From 1' or 'From 0').

Apply Changes

Click 'Close & Apply' on the Home tab to load the new Index column into your data model.

Update the Table Visual

Add the newly created Index column to your table visual. Ensure you click the drop-down arrow next to the field in the Visualizations pane and select 'Don't summarize'.

Tip: If you don't want the index number to distract users, you can minimize its column width in the visual so it is practically invisible, yet still forces row granularity.

Solution 2

Disable Default Summarization for Value Columns

Numeric columns might be automatically summed or counted, causing the visual to group records. Disabling this behavior reveals individual rows.

Solution 3

Consult the Microsoft Power BI Community

If relationships, cross-filtering, or data grouping behaviors persist beyond basic fixes, specialized analysis of your data model is required.

Free Microsoft Office alternative

Handle Your Data Preparation Seamlessly with WPS Office

While Power BI is excellent for advanced analytics and visualization, cleaning and preparing raw datasets is often much easier in a traditional spreadsheet. WPS Office provides a highly compatible, lightweight alternative to Microsoft Excel for inspecting and deduplicating your fact tables before importing them into Power BI.

1. Install WPS Office: Download and install the free WPS Office suite on your device.
2. Open Your Dataset: Launch WPS Spreadsheet and open your raw .csv or .xlsx fact table to inspect the row count.
3. Clean and Assign IDs: Use WPS Spreadsheet features to remove duplicates or add a unique primary key column, then save the file for Power BI ingestion.

Fully compatible with Microsoft Excel formats (.xlsx, .csv) for seamless Power BI imports.Lightweight architecture that opens large raw datasets quickly without lagging.Familiar spreadsheet interface requiring zero learning curve for Excel users.Free to use with built-in advanced data filtering and deduplication tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Power BI exported data have fewer rows than my original fact table?

Exported data is limited by Power BI's export row limits (e.g., 30,000 rows for CSV and 150,000 rows for Excel). Additionally, if the visual aggregates data, the export will only contain the aggregated distinct rows, not the granular underlying data.

Does Power BI always remove duplicate rows in a table visual?

Yes. By default, if multiple rows have identical values across all columns included in the visual, Power BI groups them into a single distinct row. To prevent this, you must include a column that guarantees uniqueness, such as an Index or Primary Key column.

How can I easily check if my raw fact table contains exact duplicates?

You can open the Power Query Editor, group by all columns, and count the rows. Alternatively, you can open the raw data file in a spreadsheet program like WPS Spreadsheet and use the 'Highlight Duplicates' feature to quickly identify repeating records before loading them into Power BI.

Office Quick Solutions