Sort data by date and exclude duplicate data

[KellieDaniel]

Hi,

I am using a DirectQuery connection to and SQL data base that has data uploaded from clients on a montly basis.

I need to exclude data that has been uploaded in duplicate for the same time period.

I feel I need to sort by month, then remove the duplicate data in that time period but I am not sure how to do this.

Should I use a dax expression?  The problem being if I create a new Column I am unsure how this will effect my relational data.

The data is comprised of over 100 different tables that are all related, half of them  directly back to the table I need to filter.

I can not include a screen shot as it is sensitive data.

Thanks in advance

[GilbertQ]

Hi @KellieDaniel I would suggest adding the Calculated Column and see what happens.

Also in your source system is there no way in the current process, to go through and exclude or delete the duplicate data?

[Seth_C_Bauer]

@KellieDaniel My opinion would be that you clean this up in the database side of things.. If for some reason that can't be done, and it's ok to have duplicate information in the table, I would use a view to replace the Power BI Query that currently looks at the table. You said you had over a 100 tables, and I don't know where/how the relationships are so I don't know if this is a single change or something you would have to do multiple times...

Here is my thinking. You could identify the duplicates using calculated columns, but you are just adding overhead to the model. You can't remove those duplicates in PBI because you are using direct query. So by handling the problem before the data gets to PBI, you are reducing the number of records that need to be passed, and not adding unecessary bloat to your model.

Off the top of my head this would be something like using a ROW_NUMBER() function to determine how many times the given criteria duplicate, and using that as a subquery have the outer query reference that function where it equals 1. This would return the distinct list for Power BI.