Forum Discussion
Prevent Power BI table widget from hiding duplicate rows
I have a Power BI Service dashboard that is based on a personal MS List. The dashboard has a table widget that displays a list of incidents from the list.
The columns shown are:
- Date [no time]
- Department
- Type
- Etc.
Problem:
I noticed the table widget automatically hides rows that it thinks are duplicates. For example, there are two incidents that happened on the same day that have the same attributes. But only one of the incidents is shown, which isn't what I want; I want all rows to be shown, regardless of whether they appear to be duplicates.
I can force the widget to display the so-called duplicate rows by including the ID column from the list, which is an auto-generated system column in all lists that contains unique integer IDs. But my preference would be not to include that column, since it provides no value to the dashboard user. It's not necessary to include any other unique information, like the time of the incident. There's no guarantee the time is unique anyway; the dates are manually entered into the list, not a precise system time.
Is there a different way to force the table widget to include all rows, other than adding a unique ID column?
Hi Blue88,
Adding to the explanation above: since a unique key in the visual is unavoidable, the practical question is how to include it without the user seeing it, or how to turn it into something useful. Three options, from quickest to cleanest:
1. Keep the ID but hide it visually (no model changes):
add the ID column to the table -> right-click it -> Rename for this visual -> " " (a space) Format > Specific column -> ID -> font color = background color drag the column edge to the minimum width (or put it as the last column) -> all rows show, the column is effectively invisible2. Show a meaningful number instead of the raw ID. Add a per-day sequence number in Power Query, so duplicates read as "1, 2" for that day, which users actually understand:
// after your existing steps, keyed on the columns shown in the table Grouped = Table.Group(Source, {"Date", "Department", "Type"}, {{"Rows", each Table.AddIndexColumn(_, "#", 1, 1, Int64.Type), type table}}), Expanded = Table.Combine(Grouped[Rows])Put "#" in the table as the first column.
3. Show the duplicates as a count. If one row per combination is fine as long as nobody misses an incident, add a measure. Two incidents then show as one row with Incidents = 2:
Incidents = COUNTROWS ( 'Incident List' )Tip: if the table is pinned to a dashboard from a report, make the change in the report and re-pin the tile. Dashboard tiles don't pick up formatting edits on their own.
So my overall approach would be:
need every row, no extra info -> ID with blank header + hidden font need every row, readable -> per-day "#" sequence from Power Query okay with grouping -> Incidents = COUNTROWS(...) measureAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
3 Replies
- ShivekMaharaj
Community Champion
Hi Blue88,
What you are seeing is expected behaviour for a Power BI table visual.
The visual is evaluated at the grain of the fields you place in it. If two MS List records have exactly the same values for Date, Department, Type, and the other displayed columns, Power BI has no field in the visual that distinguishes those two incidents, so they can appear as a single row.
The fact that both rows appear as soon as you add the List ID confirms that the issue is the row grain rather than the MS List itself.
Microsoft explains how table visuals work with the fields included in the visual, and Power BI can return fewer visible rows when records evaluate as identical across the values being displayed.
If the requirement is truly one row per incident, I would keep a unique incident-level key such as the MS List ID as part of the visual/model grain. There is not a standard table setting that forces Power BI to display multiple physically separate source rows when every field used by the visual is identical.
If you do not want users to focus on the ID, you can keep it as a narrow supporting column, or use another meaningful unique incident field if one exists.
So in this case I would treat the ID as part of the row identity, even if it has little business meaning for the report consumer.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
jubinsoniSkilled Sharer
Hi Blue88,
Adding to the explanation above: since a unique key in the visual is unavoidable, the practical question is how to include it without the user seeing it, or how to turn it into something useful. Three options, from quickest to cleanest:
1. Keep the ID but hide it visually (no model changes):
add the ID column to the table -> right-click it -> Rename for this visual -> " " (a space) Format > Specific column -> ID -> font color = background color drag the column edge to the minimum width (or put it as the last column) -> all rows show, the column is effectively invisible2. Show a meaningful number instead of the raw ID. Add a per-day sequence number in Power Query, so duplicates read as "1, 2" for that day, which users actually understand:
// after your existing steps, keyed on the columns shown in the table Grouped = Table.Group(Source, {"Date", "Department", "Type"}, {{"Rows", each Table.AddIndexColumn(_, "#", 1, 1, Int64.Type), type table}}), Expanded = Table.Combine(Grouped[Rows])Put "#" in the table as the first column.
3. Show the duplicates as a count. If one row per combination is fine as long as nobody misses an incident, add a measure. Two incidents then show as one row with Incidents = 2:
Incidents = COUNTROWS ( 'Incident List' )Tip: if the table is pinned to a dashboard from a report, make the change in the report and re-pin the tile. Dashboard tiles don't pick up formatting edits on their own.
So my overall approach would be:
need every row, no extra info -> ID with blank header + hidden font need every row, readable -> per-day "#" sequence from Power Query okay with grouping -> Incidents = COUNTROWS(...) measureAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
v-sathmakuriCommunity Support
Hi Blue88 ,
Could you check the suggestion provided above and let us know if you have any additional queries?
Thanks!!
