Counting Duplicate Values

[Buck2919]

Hi,

I'm pulling togther a number of files and need to know if this is the first time something has been on an insurer statement.

I've created a merge column to concatenate key indicators.

I want to add a column to count how many times the contact appears - Where the number is 1 this would indicate the first time and i want to add a conditional column to identify these.

How do i calculate the contact column?

Thanks
Michael

 

[sandy666]

Group by Contact?

 

[Buck2919]

Hi,

How do i keep my other columns?

Thanks

 

[sandy666]

here is an example

source			result
item	value		item	Count	value
item1	5		item1	3	5
item2	6		item2	1	6
item3	4		item3	2	4
item1	3		item1	3	3
item3	9		item3	2	9
item1	5		item1	3	5

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    Group = Table.Group(Index, {"item"}, {{"Count", each Table.RowCount(_), type number}, {"CAR", each _, type table}}),
    Expand = Table.ExpandTableColumn(Group, "CAR", {"value", "Index"}, {"value", "Index"}),
    Sort = Table.Sort(Expand,{{"Index", Order.Ascending}}),
    IndexOut = Table.RemoveColumns(Sort,{"Index"})
in
    IndexOut

you can expand all columns what you need