Filter out Duplicate records and retain the latest record

[laiksayyed]

Working on a power BI report, with SQL datasource in DirectQuery Mode.

I have a location_docs Table with unique DocID, many LocationID and many DocumentTypes.

I am struggling to filter out duplicate records and generate report with these unique records.

Unique records means – there should be one DocType for each LocationID and the unique records should include the last uploaded Document, with the highest/Maximum DocID No.

Following is a snapshot from my current data and expected resultant data. Please remember it is in DirectQuery mode.

DocID	Count	LocID	DocType	ExpiryDate
1828	1	30	2	31/05/24
1072	1	30	2	20/07/22
684	1	30	2	16/06/22
39	1	30	2	03/09/24
40	1	30	3	11/12/24
1790	1	30	4	20/08/24
38	1	30	5	27/11/23
17	1	30	5	01/06/22
18	1	30	6	12/07/24
44	1	30	8	31/05/24
42	1	30	8	28/11/22
41	1	31	2	13/05/22
1827	1	31	2	09/05/24
46	1	31	3	11/12/24
1791	1	31	4	20/08/24
1792	1	31	5	13/06/23
1793	1	31	6	10/10/23
45	1	31	8	31/12/21
47	1	32	2	25/03/24
1073	1	32	3	11/12/24
1081	1	32	4	22/12/24
1794	1	32	5	21/07/23
48	1	32	6	31/08/23
49	1	32	8	31/10/22
50	1	33	2	08/11/22
2488	1	33	2	16/10/24
1074	1	33	3	11/12/24
1075	1	33	4	12/12/24
1795	1	33	5	29/11/22
1796	1	33	6	29/11/22
1797	1	33	8	29/12/22
51	1	34	2	01/11/24
1076	1	34	3	11/12/24
1077	1	34	4	11/12/24
1798	1	34	5	30/12/21
1799	1	34	6	28/03/24
2208	1	34	8
1801	1	35	2	28/12/22
1078	1	35	4	11/12/24
1803	1	35	5	23/11/21
2576	1	35	5	12/11/23
1800	1	35	6	14/03/21
1804	1	35	8	20/07/22
20	1	36	2	27/11/22
1805	1	36	4	20/12/23
19	1	36	5	27/11/22
1808	1	36	5	16/11/22
2040	1	36	5	27/11/22
2575	1	36	5	12/11/23
1807	1	36	6	31/01/25
43	1	36	8	30/11/22
1809	1	36	8	31/12/22
2577	1	36	8	28/11/23

Expected Result:

DocID	Count	LocID	DocType	ExpiryDate
1828	1	30	2	31/05/24
40	1	30	3	11/12/24
1790	1	30	4	20/08/24
38	1	30	5	27/11/23
18	1	30	6	12/07/24
44	1	30	8	31/05/24
41	1	31	2	13/05/22
46	1	31	3	11/12/24
1791	1	31	4	20/08/24
1792	1	31	5	13/06/23
1793	1	31	6	10/10/23
45	1	31	8	31/12/21
47	1	32	2	25/03/24
1073	1	32	3	11/12/24
1081	1	32	4	22/12/24
1794	1	32	5	21/07/23
48	1	32	6	31/08/23
49	1	32	8	31/10/22
2488	1	33	2	16/10/24
1074	1	33	3	11/12/24
1075	1	33	4	12/12/24
1795	1	33	5	29/11/22
1796	1	33	6	29/11/22
1797	1	33	8	29/12/22
51	1	34	2	01/11/24
1076	1	34	3	11/12/24
1077	1	34	4	11/12/24
1798	1	34	5	30/12/21
1799	1	34	6	28/03/24
2208	1	34	8
1801	1	35	2	28/12/22
1078	1	35	4	11/12/24
2576	1	35	5	12/11/23
1800	1	35	6	14/03/21
1804	1	35	8	20/07/22
20	1	36	2	27/11/22
1805	1	36	4	20/12/23
2575	1	36	5	12/11/23
1807	1	36	6	31/01/25
2577	1	36	8	28/11/23

Thank you for your support.

Laik

[v-yueyunzh-msft · Accepted solution]

Hi , @laiksayyed 

According to your desciption, you want to filter the duplicate data in your table . Right?

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We need to add an index column in Power Query Editor:

(3)Then we apply the data to Desktop and we need to click "New Column" to create three calculated columns:

Flag1 = var _curDoc = [DocType]
var _index = [Index]
var _pre_Doc =MAXX( FILTER( 'Table', 'Table'[Index] = _index -1) , [DocType])
var _flag = IF(_curDoc = _pre_Doc , 1, 0)
return
_flag

Flag2 = var _flag1 = [Flag1]
var _index= [Index]
var _after =MAXX(FILTER('Table','Table'[Index]=_index+1) , [Flag1])
var _flag2 = IF( _after =1 ,1,_flag1)
return
_flag2

Max_DocID = var _flag2 = [Flag2]
var _index = [Index]
var _min_index = MAXX( FILTER('Table','Table'[Index]<= _index && 'Table'[Flag2] = 0 ) , [Index])
var _min_value = IF( _min_index =BLANK() ,1,_min_index+1)
var _max_index =MINX( FILTER( 'Table','Table'[Index]>= _index && 'Table'[Flag2] = 0) , [Index])
var _max_value = IF(_max_index=BLANK() , MAX('Table'[Index]) , _max_index-1)
var _max_docID =MAXX( FILTER( 'Table' , 'Table'[Index] >=_min_value && 'Table'[Index] <= _max_value) , [DocID])
return
IF(_flag2=0, [DocID] ,IF(_max_docID=[DocID],[DocID],BLANK()))

Then we can get this:

(4)Then we can put the fields on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Anonymous]

Thanks Ibendlin for your reply... Measure or calculated column either will do. 

As mentioned, my concern is this report has to be in DirectQuery mode, not mixed.

[lbendlin]

That means a measure is your only option.  

Can you confirm that you only want to show the max DocID for each LocID and DocType combination?

So for the first group that would be DocID 1828 ?

Measure Show = 
var d = selectedvalue(Table_1[DocID])
var m = CALCULATE(max(Table_1[DocID]),REMOVEFILTERS(Table_1[DocID]))
return if(d=m,1,0)

[lbendlin]

Needs more details. Are you looking for a measure. or are you looking for a calculated column after you converted the direct query to mixed mode?