Forum Discussion
workaround show all rows include duplicates in a table visualisation without adding more columns
Hi I wonder if there are some workarounds to have all rows incl duplicates in a table visuals,
this is how my tables looks, i just want to have the test colum only and when i removed yearmonth column it only display distinct rows:
are the any workarounds to display this:
the reason i cant add any columns is i need to export data and cant have more additional columns. Thanks for supporting
HI fittore_709394 ,
This is the way Power BI works in the table visual. I would guess a couple ways around it would be to put in a "hidden" column. Maybe a column taht is titled "." or seomthing and then you can hide it. That way all the variables will show, including the duplicates.
Another trick might be to create an Index Row and hide it. That may work because now every row (including duplicates) has a unique Index number and should be considered "available"...If for Exporting purposes, i'd suggest using Paginated Reports. It will preserve duplicates as long as your dataset query does not aggregate and they are fundamentally row-based, not semantic aggregation visuals.
You can use the additional Index column trick with hiding this colunm on visual by tweaking the visuals but table Export will still include the column.
To force Power BI to display every row (including duplicates), you need to include a column that uniquely identifies each record:
-
Add an Index column in Power Query.
-
Include that column in the table visual.
Example (Power Query):
-
Add Column → Index Column
This makes each row unique, so the table visual will show all rows.
If you don’t want users to see the column:
-
Keep the index column in the visual
-
Reduce its width to almost zero or rename it to something unobtrusive.
-
7 Replies
- collinq
Super User
HI fittore_709394 ,
This is the way Power BI works in the table visual. I would guess a couple ways around it would be to put in a "hidden" column. Maybe a column taht is titled "." or seomthing and then you can hide it. That way all the variables will show, including the duplicates.
Another trick might be to create an Index Row and hide it. That may work because now every row (including duplicates) has a unique Index number and should be considered "available"... - MasonMA
If for Exporting purposes, i'd suggest using Paginated Reports. It will preserve duplicates as long as your dataset query does not aggregate and they are fundamentally row-based, not semantic aggregation visuals.
You can use the additional Index column trick with hiding this colunm on visual by tweaking the visuals but table Export will still include the column.
- pcoley
fittore_709394 Your issue is related to the context within you are evaluating the data inside the matrix viz.
Powerbi viz doesn't have a way to recognise the difference between test2 test3 test4 if there is not an explicit value that filter the correct context (a field that includes the monthyears value for that record).
i suggest trying manipulating the field "test" including all the data needed at the correct level you need it in each register (monthyear_ test: 202501 test1; 202501 test2 ; 202501 test3; 202501 test4; 202502 test1....)
I hope this helps. if so please mark it as a solution. kudos are welcome. - danextian
You can make use of a column's group by attribute.
https://www.sqlbi.com/articles/understanding-group-by-columns-in-power-bi/
You can also add an index column and forced sort the test column using index with Tabular Editor as Power BI Desktop's gui doesn't allow multiple sort values.
- cengizhanarslan
To force Power BI to display every row (including duplicates), you need to include a column that uniquely identifies each record:
-
Add an Index column in Power Query.
-
Include that column in the table visual.
Example (Power Query):
-
Add Column → Index Column
This makes each row unique, so the table visual will show all rows.
If you don’t want users to see the column:
-
Keep the index column in the visual
-
Reduce its width to almost zero or rename it to something unobtrusive.
-
Hi fittore_709394,
I would also take a moment to thank cengizhanarslan , cengizhanarslan, MasonMA, pcoley for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Regards,
Community Support Team.Hi fittore_709394,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Regards,
Community Support Team.
