Export report data with current layout as is and not remove duplicates

[bslintx]

I have a report that contains data created by a vulnerability database application. For the most part it does not contain duplicate records but there are some in the raw output and this needs to be intact. I have a card that shows the correct number of records but when the data is exported with "current layout" option the export does not get all the records and seems to remove what it finds to be duplicate rows. Basically it is acting like a union query but I need all records. I added an index key (auto id) but it still does not export all.

It seems this has been asked before (in researching)  but the index field that fixed those does not fix mine. 

I am very new so this is very confusing and frustraing as I don't understand why it would remove records????

The table trying to export is linked by an IP Address to a parent table with unique IP Address ----- one-to-many relationship

Please advise if you need more info - thanks for the help!

Edit:

The export is accuate ...so, it is the vulnerability "table" that is removing records. There is a details table that is linked to vulnerabilities that contains a measurement. This "total" measurement is correct but the number of records on certain ip addresses that contains "duplicate" data in the roes is removed. So if the total is 15 ..... the vulnerabilties view shows 12 ....because there are 3 that accross the row are identical. So need to determine how to stop this - thanks

details measurement:

Total = 


IF (
    CALCULATE ( COUNT(Vulnerabilities[Department]) ) = BLANK (),
    0,
    CALCULATE ( COUNT(Vulnerabilities[Department]) )
)

Edit 2:

I found under "edit query" for vulnerabilty table > keep rows > keep duplicates would be the answer but when applied the recordset plummeted ...so, clearly i am not setting this up .....but posting to let those helping know what i have done so far

Edit 3:

Well...looks like adding the index is indeed the fix for preventing power bi to uinon duplicates.....i missed the fact that the index column needs to be set to Dont summarize!

Thanks

[v-hashadapu · Accepted solution]

Hi @bslintx , Thank you for reaching out to Microsoft Fabric Community Forum.

Power BI removes duplicate rows during exporting data if it thinks they are duplicates. Adding an index column (auto ID) should help prevent this, but you must set it to "Don't summarize" in your visual. This stops Power BI from grouping rows.

Check that your relationships between tables are set correctly. Your relationship between your tables should be "One-to-Many" and set to "Single" to have the right control over your data.

When exporting, try to export raw data from a table itself instead of trying to export it from a visual because that might cause aggregation. Whenever you use "Keep Duplicates" in Power Query, this causes the record count to drop. Other steps or filters may inadvertently remove your data. Check for any filters or transformations in the query that might be deleting records, such as filtering out nulls or joining with other tables. Make sure that your relationship between your tables is correct: the parent table should have unique IPs, and the child table (vulnerabilities) should have multiple entries for each IP. The "Don't summarize" setting prevents Power BI from merging rows that have the same index value. Right-click on the index field in your visual and select "Don't summarize" to include all records without aggregation.

It is my understanding from reading your 3rd edit that your problem is solved. Can you please confirm the same or if you still have any issues, please share them. 

If this post helps, then please consider to 'Accept as solution' to help the other members find it more quickly.

[bslintx]

Hey there!.....

Thanks for taking the time and posting a solution. You are correct >  Edit 3 was the fix action. I had an index column but the 2 important things to ensure:

1. Set to not summarize

2. The index must be included (checked) in the visual

Again, appreciate your assistance!

[danextian]

Hi @bslintx ,

Unless you add another column that distinguishes e record with the same name as the others, Power BI will merge them into one in the viz. Alternatively, you can use Tabular Editor to  force Power BI to allow a column with the same values to be sorted multiple times. For example, A-row1 and A-row2 will be sorted as 1 and 2.

[bslintx]

This is great to know and will help with future projects....thanks for taking the time and posting!

I ended up stumbling into a solution > Edit 3 above....so, this post may be closed