Forum Discussion
Duplicate Value Count
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 |
ALLSELECTED has no meaning in a calculated colum. You can also drop the OrderBy.
Count2 = ROWNUMBER(SUMMARIZE('Table',[Predecessr],[FollOn Doc]),PARTITIONBY([Predecessr]))
7 Replies
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".
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.
I wrote a video for you, look how easy it is
pls try this
