Duplicate Value Count

[Anonymous]

Dear All ,

I want to get the count of the duplicate values as shown below. Below shows my table.

Predecessr		FollOn Doc
1030172720		1200261222
1030172740		1200261228
1030172740		1200261441
1030172740		1200261853
1030172740		1200262095
1030172740		1200524255
1030172749		1200262308
1030172751		1200262310
1030172756		1200261689
1030172758		1200261711
1030172759

What I need is below output. It shows in Count column.

Predecessr		FollOn Doc		Count
1030172720		1200261222		1
1030172740		1200261228		1
1030172740		1200261441		2
1030172740		1200261853		3
1030172740		1200262095		4
1030172740		1200524255		5
1030172749		1200262308		1
1030172751		1200262310		1
1030172756		1200261689		1
1030172758		1200261711		1
1030172759				1

But I got the below record as my output. How can I get the above results. Can someone help me please. 

Count = CALCULATE( COUNT('Count of Delivery (Sheet1)'[Predecessr]), FILTER('Count of Delivery (Sheet1)','Count of Delivery (Sheet1)'[Predecessr] = EARLIER('Count of Delivery (Sheet1)'[Predecessr])))

Count
1
5
5
5
5
5
1
1
1
1
1

[Ahmedx]

pls try this

[lbendlin · Accepted solution]

ALLSELECTED has no meaning in a calculated colum.  You can also drop the OrderBy.

Count2 = ROWNUMBER(SUMMARIZE('Table',[Predecessr],[FollOn Doc]),PARTITIONBY([Predecessr]))

[Anonymous]

Thank you. This worked for me. 

[Ahmedx]

that's right, I write everything out of habit

[Ahmedx]

I wrote a video for you, look how easy it is

https://1drv.ms/v/s!AiUZ0Ws7G26RiwdotVHIwqn4muwQ?e=jZ54gz

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Predecessr"}, {{"All", each Table.AddIndexColumn(_,"Count",1,1), type table [Predecessr=number, FollOn Doc=any, Count=number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"FollOn Doc", "Count"}, {"FollOn Doc", "Count"})
in
    #"Expanded All"

Hope this helps.

[lbendlin]

Power BI has no concept of row numbers. You need to bring your own index.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc+5DcAwDAPAVQLVLiha8jOL4f3XCFIpCuL6QJBcSxQV2tkJKaIE2JSk7BJmycbRzPRow+vJiOn/5jT6x2bkKvIW15cpsrXY0sbMNsK65g/+9F2y9w0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Predecessr = _t, #"FollOn Doc" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Count", (k)=> Table.RowCount(Table.SelectRows(#"Added Index",each [Index]<=k[Index] and [Predecessr]=k[Predecessr])))
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".