Distinct Group by Index

[lg1551]

Hey all,

I'm trying to accomplish a distinct Group-By index, by account number as pictured below and I want this to happen in the Power Query M-Code. I've done the numbered by grouping approach and it is just indexing the rows by each account. What I am looking to accomplish is depicted below. Any ideas? Trying to keep it as clean as possible without having to copy the table, remove dupes, and merge it back on itself.

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

 

[Ken Puls]

Hi there, and welcome to the forum.

The steps to do this are:

• Select the Account column of your data -> Transform -> Group By
• Configure the aggregation to be called "Data" and use the All Rows aggregation
• Sort your data as needed
• Go to Add Column -> Index -> From 1
• Right click the new column -> Remove other columns
• Expand the new column

At that point you should be good to go