Row Count Line Idenification

[Anonymous]

This is an example of of 1 invoice with 1 line item that was split into 5, I want to remove all the Duplicates and just have 1 line. 

Document ID	Invoice Number	Line Number	Line Amount	Invoice Total	What I want the Results to be
12345678	4010	1	10	95	First
12345678	4010	1	10	95	Duplicate
12345678	4010	1	15	95	Duplicate
12345678	4010	1	20	95	Duplicate
12345678	4010	1	40	95	Duplicate

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Use below M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1M7dQ0lEyMTA0AFKGIAxiWJoqxepQoMKUkAojgmaYwFTEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document ID" = _t, #"Invoice Number" = _t, #"Line Number" = _t, #"Line Amount" = _t, #"Invoice Total" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document ID", Int64.Type}, {"Invoice Number", Int64.Type}, {"Line Number", Int64.Type}, {"Line Amount", Int64.Type}, {"Invoice Total", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Table.Min(Table.SelectRows(#"Added Index",each [Document ID]=[Document ID] and [Invoice Number]=[Invoice Number] and [Line Number]=[Line Number] and [Line Amount]=[Line Amount] and [Invoice Total]=[Invoice Total]),"Index")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Index"}, {"Custom.Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Index]=[Custom.Index] then "First" else "Duplicated"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom.Index", "Index"})
in
    #"Removed Columns"

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

In the formula bar:= Table.RemoveDuplicates(NameOfPriorStep,{"DocumentID"})

--Nate

[Anonymous]

Instead of remove the Duplicates I want to Identify the Duplicate.