Index Groups

[lager1001]

I may be overthinking this but my brain is tired today. I need to Index like values based on account number like the below. I've done Numbered Rows by Grouping and it's giving me the row counts for each account which I don't need. I need to Group/Index each account as shown below. What am I doing wrong here.

Account	Account Index
ABCD	1
ABCD	1
ABCD	1
ABCD	1
ACBD	1
EDFHG	2
EDFHG	2
EDFHG	2
EDFHG	2
EDFHG	2
IJKLM	3
IJKLM	3
IJKLM	3
IJKLM	3
IJKLM	3
NOPQ	4
NOPQ	4
NOPQ	4
NOPQ	4
RSTU	5
RSTU	5
RSTU	5
RSTU	5
RSTU	5
VWXYZ	6
VWXYZ	6
VWXYZ	6
VWXYZ	6

 

[GlennUK]

Have a look at this:

Book3 (version 1).xlsb
	A	B
1	Account	Account Index
2	ABCD	1
3	ABCD	1
4	ABCD	1
5	ABCD	1
6	ABCD	1
7	EDFHG	2
8	EDFHG	2
9	EDFHG	2
10	EDFHG	2
11	EDFHG	2
12	IJKLM	3
13	IJKLM	3
14	IJKLM	3
15	IJKLM	3
16	IJKLM	3
17	NOPQ	4
18	NOPQ	4
19	NOPQ	4
20	NOPQ	4
21	RSTU	5
22	RSTU	5
23	RSTU	5
24	RSTU	5
25	RSTU	5
26	VWXYZ	6
27	VWXYZ	6
28	VWXYZ	6
29	VWXYZ	6
Sheet11

Cell Formulas
Range	Formula
B2:B29	B2	=SUM(1/COUNTIF($A$2:A2,$A$2:A2))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[lager1001]

Have a look at this:

Book3 (version 1).xlsb
	A	B
1	Account	Account Index
2	ABCD	1
3	ABCD	1
4	ABCD	1
5	ABCD	1
6	ABCD	1
7	EDFHG	2
8	EDFHG	2
9	EDFHG	2
10	EDFHG	2
11	EDFHG	2
12	IJKLM	3
13	IJKLM	3
14	IJKLM	3
15	IJKLM	3
16	IJKLM	3
17	NOPQ	4
18	NOPQ	4
19	NOPQ	4
20	NOPQ	4
21	RSTU	5
22	RSTU	5
23	RSTU	5
24	RSTU	5
25	RSTU	5
26	VWXYZ	6
27	VWXYZ	6
28	VWXYZ	6
29	VWXYZ	6
Sheet11

Cell Formulas
Range	Formula
B2:B29	B2	=SUM(1/COUNTIF($A$2:A2,$A$2:A2))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Sorry, I should have been more specific. I am trying to accomplish this in the power query, the m-code. I thought it would be a rather simple group by step but it's proving more difficult than I thought.

 

[GlennUK]

I didn't see the group the question was in ( I just viewed all unanswered ) ... does this link help: How to group row values for distinct count

 

[I_cant_read]

Try the ff.
1.) Load it into a connection. Query 1
2.) Transform > Group by account> use any of the available aggregations/computation. like count distinct row. > then add column > index column
3.) Load your data again into another query. Query 2.
4.) Merge Query 2 and Query 1. Expand the table.

Cheers.