Flagging duplicates but Serial wise

[Shaikh Aziz]

Hi there,

I want to identify duplicates but serial wise e.g. in normal excel I would do =COUNTIF($A$2:A2,A2),
How do I apply same countif condition in Power Query ? Please suggest or help any easy way to complete the task.
I am attaching the result normal excel image.

 

[jdellasala]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Product Name	Business Type	Age	Sl Band	Policy count	Column1	Prm		Product Name	Business Type	Age	Sl Band	Policy count	Column1	Prm	Count
2	1234567	Fresh	0-5	3 lacs		1	384		1234567	Fresh	0-5	3 lacs		1	384	1
3	6785390	Renewal	20-May	6 Iacs		3	164		6785390	Renewal	45066	6 Iacs		3	164	3
4	5678903	Rollover	21-25	9 Iacs		1	215		6785390	Rollover	0-5	3 Iacs		2	487	3
5	875938	Fresh	25-30	12 Iacs		2	192		6785390	Renewal	21-25	9 Iacs		1	424	3
6	5678903	Renewal	30-35	15 Iacs		3	393		5678903	Rollover	21-25	9 Iacs		1	215	2
7	6785390	Rollover	0-5	3 Iacs		2	487		5678903	Renewal	30-35	15 Iacs		3	393	2
8	5765282	Fresh	20-May	6 Iacs		2	491		875938	Fresh	25-30	12 Iacs		2	192	1
9	6785390	Renewal	21-25	9 Iacs		1	424		5765282	Fresh	45066	6 Iacs		2	491	1
10	903876	Rollover	25-30	12 Iacs		3	312		903876	Rollover	25-30	12 Iacs		3	312	3
11	6755493	Fresh	30-35	15 Iacs		2	186		6755493	Fresh	30-35	15 Iacs		2	186	1
12	6547382	Renewal	0-5	3 Iacs		3	171		6547382	Renewal	0-5	3 Iacs		3	171	1
13	345267	Rollover	20-May	6 Iacs		1	481		345267	Rollover	45066	6 Iacs		1	481	1
14	903876	Renewal	21-25	9 Iacs		3	268		903876	Renewal	21-25	9 Iacs		3	268	3
15	567290	Rollover	25-30	12 Iacs		1	173		567290	Rollover	25-30	12 Iacs		1	173	1
16	903876	Renewal	30-35	15 Iacs		2	459		903876	Renewal	30-35	15 Iacs		2	459	3
Sheet1

Using Group By to get the count:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ProdCount =  Table.Group(Source, {"Product Name"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
    MergedQueries = Table.NestedJoin(Source, {"Product Name"}, ProdCount, {"Product Name"}, "ProdCount", JoinKind.LeftOuter),
    ExpandedProdCount = Table.ExpandTableColumn(MergedQueries, "ProdCount", {"Count"}, {"Count"})
in
    ExpandedProdCount

Hope that helps.

 

[Shaikh Aziz]

Thanks for revert,
By adding above mentioned query I am getting same count of each values, e.g., if you will look product name "6785390" for each product name it's providing the same results as "3" counts. I want it in serial wise, e.g., product name wise serial count.
Your understanding is greatly appreciated.

 

[jdellasala]

Thanks for revert,
By adding above mentioned query I am getting same count of each values, e.g., if you will look product name "6785390" for each product name it's providing the same results as "3" counts. I want it in serial wise, e.g., product name wise serial count.
Your understanding is greatly appreciated.

It is very difficult to work with a tiny screenshot, and I wasn't paying attention to the CountIf column since what I thought you were saying was that you just wanted a count. What you meant was you want a Running Total, and had you used XL2BB it might have been more apparent.
Anyway, this video shows how to do a running total. I think I have it now:

Book1
	I	J	K	L	M	N	O	P
1	Product Name	Business Type	Age	Sl Band	Policy count	Prm	Countif	RT
2	1234567	Fresh	0-5	3 lacs	1	384	1	1
3	6785390	Renewal	5-20	6 Iacs	3	164	1	1
4	5678903	Rollover	21-25	9 Iacs	1	215	1	1
5	875938	Fresh	25-30	12 Iacs	2	192	1	1
6	5678903	Renewal	30-35	15 Iacs	3	393	2	2
7	6785390	Rollover	0-5	3 Iacs	2	487	2	2
8	5765282	Fresh	5-20	6 Iacs	2	491	1	1
9	6785390	Renewal	21-25	9 Iacs	1	424	3	3
10	903876	Rollover	25-30	12 Iacs	3	312	1	1
11	6755493	Fresh	30-35	15 Iacs	2	186	1	1
12	6547382	Renewal	0-5	3 Iacs	3	171	1	1
13	345267	Rollover	5-20	6 Iacs	1	481	1	1
14	903876	Renewal	21-25	9 Iacs	3	268	2	2
15	567290	Rollover	25-30	12 Iacs	1	173	1	1
16	903876	Renewal	30-35	15 Iacs	2	459	3	3
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Product Name", Int64.Type}, {"Business Type", type text}, {"Age", type text}, {"Sl Band", type text}, {"Policy count", Int64.Type}, {"Prm", Int64.Type}, {"Countif", Int64.Type}}),
    AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 1, 1, Int64.Type),
    BufferedTable = Table.Buffer(AddedIndex),
    #"Added Custom" = Table.AddColumn(BufferedTable, "RT", (OT) => Table.RowCount( Table.SelectRows( BufferedTable, (IT) => IT[Product Name] = OT[Product Name] and IT[Index] <= OT[Index] ) ), Int64.Type  ),
    RemovedIndex = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    RemovedIndex

I think that's what you're looking for. I hope so!

 

[Shaikh Aziz]

It is very difficult to work with a tiny screenshot, and I wasn't paying attention to the CountIf column since what I thought you were saying was that you just wanted a count. What you meant was you want a Running Total, and had you used XL2BB it might have been more apparent.
Anyway, this video shows how to do a running total. I think I have it now:

Book1
	I	J	K	L	M	N	O	P
1	Product Name	Business Type	Age	Sl Band	Policy count	Prm	Countif	RT
2	1234567	Fresh	0-5	3 lacs	1	384	1	1
3	6785390	Renewal	5-20	6 Iacs	3	164	1	1
4	5678903	Rollover	21-25	9 Iacs	1	215	1	1
5	875938	Fresh	25-30	12 Iacs	2	192	1	1
6	5678903	Renewal	30-35	15 Iacs	3	393	2	2
7	6785390	Rollover	0-5	3 Iacs	2	487	2	2
8	5765282	Fresh	5-20	6 Iacs	2	491	1	1
9	6785390	Renewal	21-25	9 Iacs	1	424	3	3
10	903876	Rollover	25-30	12 Iacs	3	312	1	1
11	6755493	Fresh	30-35	15 Iacs	2	186	1	1
12	6547382	Renewal	0-5	3 Iacs	3	171	1	1
13	345267	Rollover	5-20	6 Iacs	1	481	1	1
14	903876	Renewal	21-25	9 Iacs	3	268	2	2
15	567290	Rollover	25-30	12 Iacs	1	173	1	1
16	903876	Renewal	30-35	15 Iacs	2	459	3	3
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Product Name", Int64.Type}, {"Business Type", type text}, {"Age", type text}, {"Sl Band", type text}, {"Policy count", Int64.Type}, {"Prm", Int64.Type}, {"Countif", Int64.Type}}),
    AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 1, 1, Int64.Type),
    BufferedTable = Table.Buffer(AddedIndex),
    #"Added Custom" = Table.AddColumn(BufferedTable, "RT", (OT) => Table.RowCount( Table.SelectRows( BufferedTable, (IT) => IT[Product Name] = OT[Product Name] and IT[Index] <= OT[Index] ) ), Int64.Type  ),
    RemovedIndex = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    RemovedIndex

I think that's what you're looking for. I hope so!

Thank you so much !! That's what I wanted