Identify duplicate rows

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.