How to stop PowerBI from automatically removing duplicates.

Power BI's table and pivot visuals collapse rows that look identical across the fields placed in the visual, so adding an Index column to the underlying model alone does not change anything.

The Index has to be a real column, not a measure. A measure like Index Column * 1 gets aggregated on export so it cannot disambiguate rows. Make sure UniqueID is a calculated column or a Power Query column, then drag it onto the table visual itself and set it to Don't summarize (right-click the field in the visual). Each row will then appear as a separate line and will export that way.

If you are using Analyze in Excel, do the same thing in the PivotTable by putting UniqueID on Rows. Otherwise, from the visual menu use Export data > Underlying data, which skips summarization entirely.

If this helped, please give a thumbs up and mark it as the accepted solution.

Thanks,
Shai Karmani

Let's connect in LinkedIn

FrankByl  Are you using that unique ID column in the visualization? A measure will not work in this scenario. Also, can you confirm there are no aggregations applied?

Here is a simple example:

Table 1:

If you add only the Name column to a visual, Power BI will display John once because duplicate values are grouped together.

Now, if the table has a unique column:

and you add both the ID and Name columns to the visual, Power BI will display two rows because each row is now uniquely identified.

Can you share more details on how you are using the column in your visual?

FrankByl 

More often, the issue is related to how the export is being performed and whether the field is actually included in the dataset being exported.

A few things to check: 1. Are you exporting "Summarized data" or "Underlying data"?

When exporting from a visual, Power BI typically offers:

• Summarized data → rows are aggregated by the fields in the visual. Identical rows can collapse into one.
• Underlying data → exports the detail rows from the model.

Please check if UniqueID is hidden from client tools, or the Excel connection is using an older metadata definition:

1. Things to check in Power BI: In Model View:

• Select UniqueID
• Check the Properties pane
• Verify Is Hidden = False

If it is hidden, Excel Pivot Tables will not see it.

2. Was UniqueID added after the Excel workbook was created?

If the workbook was connected before the column existed:

1. Close Excel.
2. Refresh the Power BI dataset.
3. Reopen the workbook.
4. Refresh all connections.

Sometimes Excel caches the model metadata.

3. Is UniqueID a calculated column or a measure?

From the screenshot it appears to be a column, which is good.

However, if it was recently created:

• Republish the dataset.
• Refresh the semantic model.
• Reconnect Excel.

Quick test

In Power BI Desktop create a simple Table visual containing:

• UniqueID
• PropertyID
• Date

Then export the visual to Excel.

If the exported Excel file contains UniqueID, then the issue is with the Pivot/Analyze in Excel connection.

If the exported file still omits UniqueID, then the column is probably hidden or not exposed in the semantic model.

Thanks for the insight Shai.  I haven't tried it yet since I don't fully understand.  You mentioned "Make sure UniqueID is a calculated column or a Power Query column, then drag it onto the table visual itself".  We used the Add Column option and chose the "Index Column".  Your saying it should be a calculated column or a Power Query column.  I don't understand.  Also, since we are not using any visual I don't know how I should be connecting this to the data.  

I feel like you are on the right track but I just don't fully grasp the how to do it.  I like the tip that I can right click on the column to say don't Summarize.  I think that might be the ticket right there.  

Any clarifications would help.

Frank

Hi FrankByl Power BI visuals display only distinct values of a dimension unless the column is configured to be sorted by another column that contains different values for otherwise identical entries, or the visual is grouped by another column. Adding an index column uniquely identifies each row in the table, but it does not automatically distinguish identical entries. If Power BI considers the entries identical, they will not be displayed separately unless a column is introduced that differentiates each occurrence, such as a timestamp, transaction ID, or another unique value.

Hi FrankByl ,

If you get a chance, please review the responses shared by danextian . They have correctly pointed out the key points, so kindly check and let us know if you need any additional details.

Thank you all for your valuable support danextian .

Regards,

Yugandhar.

Hi FrankByl, as others have suggested here, you must have a unique column in the visual itself.

1. Selecting "don't summarize" with a right-click on the column name will not help.
2. You don't need the "Sort by another column" feature, it won't help as each text value must have a corresponding single index (not your case)
3. So, what you can do is to place both the value you need and the index on the table, then turn off the text wrap for column headers and manually make the index column invisible (note it will still be available when downloading Excel, but not visible in the Power BI report)

See the example attached, and good luck with your project!

FrankByl by now you already learned that you need to create a unique row. To add a unique value aka index column, following these steps:

- transform data -> add columns -> index column. It will add a new column called Index, rename it whatever you feel like.

As you drop this column on the visual, to hide it,  use the new feature to set the custom width of the index column to zero. Read more about this here Matrix visual format settings in Power BI - Power BI | Microsoft Learn

Hi FrankByl ,

We haven't received any response from your end yet. Please let us know if you need any additional details or clarifications from our side.

Thanks.