Identify duplicate rows

[Stelles3000]

Hello togehter,

Table1

Cost Center	GL Account	DocNo	Amount
2525001	5455506	DE452K	450.11
2525001	5455506	DE477B	450.11
2525040	5456000	DE333C	83.45

I want to identify dublicate rows with the following rule:

Cost Center EQUALS Cost Center

GL Account EQUALS GL Account

Amount EQUALS Amount

DocNo UNEQUALS DocNo

The result should be:

Cost Center	GL Account	DocNo	Amount
2525001	5455506	DE452K	450.11
2525001	5455506	DE477B	450.11

[Ahmedx]

Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26Rhi9asmGJpALi9KEC?e=CJ8lH0

[MFelix]

Hi @Stelles3000 ,

This depends on how you want to get this but I see 2 different options using DAX or Power Query, but then also depends if you want to have a new table or a column you can do the following:;

Power Query:

• Create a referenced table and do a group by the  3 columns and add a count of the rows and add a row of all values:

• Filter the values different from 1 and expand~the doc number:

• Delete the column count

Option 2 is to create the same steps but within the table you have and then do a merge to get the count and flag those value has repetead:

Code below (but have some steps that can be changed to be more efficient)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcqxDYAwDATAXVxH0Tv2J9RAKkaIvP8aWNBCd8WtJY2NgEoROkn01Dmd7Uo4UVUlyu8bY/94jvd1AM8zsyOxWXVKxA0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cost Center" = _t, #"GL Account" = _t, DocNo = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cost Center", Int64.Type}, {"GL Account", Int64.Type}, {"DocNo", type text}, {"Amount", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Cost Center", "GL Account", "Amount"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Values", each _, type table [Cost Center=nullable number, GL Account=nullable number, DocNo=nullable text, Amount=nullable number]}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] = 2)),
    #"Expanded Values" = Table.ExpandTableColumn(#"Filtered Rows", "Values", {"DocNo"}, {"DocNo"}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Cost Center", "GL Account", "Amount", "DocNo"}, #"Expanded Values", {"Cost Center", "GL Account", "Amount", "DocNo"}, "Expanded Values", JoinKind.LeftOuter),
    #"Expanded Expanded Values" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Values", {"Count"}, {"Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Expanded Values",{{"Count", "Flag"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Flag", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1", each [Flag] , each if [Flag] <> null then "Duplicated" else null,Replacer.ReplaceValue,{"Flag"})
in
    #"Replaced Value"

DAX:

New Table use the code below:

Duplicated Values Table =
VAR temptable =
    SUMMARIZE (
        'Table',
        'Table'[GL Account],
        'Table'[Cost Center],
        'Table'[Amount],
        "Duplicated", IF ( COUNTROWS ( 'Table' ) > 1, "Duplicated" )
    )
RETURN
    FILTER ( NATURALLEFTOUTERJOIN ( 'Table', temptable ), [Duplicated] <> BLANK () )

Column:

Flag =
IF (
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Cost Center] = EARLIER ( 'Table'[Cost Center] )
                && 'Table'[GL Account] = EARLIER ( 'Table'[GL Account] )
                && 'Table'[Amount] = EARLIER ( 'Table'[Amount] )
        )
    ) > 1,
    "Duplicated"
)

If you just want to present this on a visualization best option is to create a measure similar to this:

Flag Metric = 
    COUNTROWS (
        FILTER (
            ALLSELECTED('Table'[Cost Center], 'Table'[GL Account], 'Table'[Amount], 'Table'[DocNo]),
            'Table'[Cost Center] = SELECTEDVALUE( 'Table'[Cost Center] )
                && 'Table'[GL Account] = SELECTEDVALUE(  'Table'[GL Account] )
                && 'Table'[Amount] = SELECTEDVALUE(  'Table'[Amount] )
        )
    ) 

Now use it has a filter on your table visualization.

[Stelles3000]

Thx for your reply. The part DocNo is unequal DocNo is important. This should not be the same.

[MFelix]

Hi @Stelles3000

I identify with duplicate but the doc number is different in all the codes if you see I'm only comparing gl account, cost center and amount. The result is giving the same has you present on the post two lines and different doc numbers.

Isn't that the result you want? 

[Stelles3000]

Yes, but the DocNo must be different, so it should not equal. There are examples with identical values, I dont want these. 

[MFelix]

@Stelles3000 ,

Sorry for the additional questions, so when you have a docnum equal you don't want to count the repetead ones?

Can you send an example of data where you have that type of information (duplicated doc numbers) and expected result? On the first example you only had 3 lines so it's diffficult to duplicate your data and get accurate results.

Thank you in advance.

[Stelles3000]

There could be more than 5000 rows in different order. 

Cost Center	GL Account	DocNo	Amount	Flag
2525001	5455506	DE452K	450.11	Dublicated
2525040	5456000	DE333C	83.45
2525001	5455506	DE477B	450.11	Dublicated
2525002	5455506	DFG56B	36.9
2525003	5455506	DFKB90	50.01
2525004	5455506	DFKB90	35.04
2525002	5455506	DFG56B	36.9

This Column "Flag" should be the result. I am looking for a pattern with specific rules.  

[Ahmedx]

For this cinaria you need like this
https://1drv.ms/u/s!AiUZ0Ws7G26RhjCoMtuTrBnmXanZ?e=z5Kwd7

[Stelles3000]

But what If the Cost Center or the GL Account is not a number. It is a text field. 

[Ahmedx · Accepted solution]

But, you can convert a text inside  in measure to a number using the VALUE function