Forum Discussion
Simple EARLIER function fail. What the heck am I missing?
I have a simple column of text labeled Key_AR in the table 'Bill1_AR' (tough table name to read, I know but I'm not changing it to Table1 in case that has something to do with the problem). I am able to count the number of times Bill1_AR[Key_AR] is duplicated***. What I am struggling to do, however, is use a simple EARLIER function to give each duplicate a sequential number.
I must be missing something basic but I'm not seeing it. What I am doing is simply:
1. Creating an Index in Power Query to give me something to count against for EARLIER
2. Add Column:
| Key_AR | Count Dupes | Index | Column |
| INV7695~consumption tax - other~336.83 | 3 | 832 | 1 |
| INV7695~consumption tax - other~336.83 | 3 | 833 | 2 |
| INV7695~consumption tax - other~336.83 | 3 | 834 | 3 |
| INV7695~custom testing~4210.38 | 3 | 836 | 1 |
| INV7695~custom testing~4210.38 | 3 | 837 | 2 |
| INV7695~custom testing~4210.38 | 3 | 838 | 3 |
| INV9374~custom testing~11250 | 3 | 1063 | 1 |
| INV9374~custom testing~11250 | 3 | 1064 | 2 |
| INV9374~custom testing~11250 | 3 | 1065 | 3 |
| INV7694~other~0 | 2 | 1804 | 1 |
| INV7694~other~0 | 2 | 1805 | 2 |
| INV7694~consumption tax - other~0 | 2 | 1806 | 1 |
| INV7694~consumption tax - other~0 | 2 | 1807 | 2 |
| INV5586~custom testing~5836.95 | 6 | 2571 | 1 |
| INV5586~custom testing~5836.95 | 6 | 2572 | 2 |
| INV5586~custom testing~5836.95 | 6 | 2573 | 3 |
| INV5586~custom testing~5836.95 | 6 | 2574 | 4 |
| INV5586~custom testing~5836.95 | 6 | 2575 | 5 |
| INV5586~custom testing~5836.95 | 6 | 2576 | 6 |
| INV5586~custom testing~7004.34 | 3 | 2577 | 1 |
| INV5586~custom testing~7004.34 | 3 | 2578 | 2 |
| INV5586~custom testing~7004.34 | 3 | 2579 | 3 |
| INV6345~custom testing~1416.47 | 5 | 2659 | 1 |
| INV6345~custom testing~1416.47 | 5 | 2660 | 2 |
| INV6345~custom testing~1416.47 | 5 | 2661 | 3 |
| INV6345~custom testing~1416.47 | 5 | 2662 | 4 |
| INV6345~custom testing~1416.47 | 5 | 2663 | 5 |
| INV7273~reprints~136.5 | 4 | 2760 | 1 |
What am I missing? I tried COUNTA.
Vexed!
Thank you for your suggestions
** code used:
Count Dupes =
Var CheckCountARKey_AR = 'Bill1_AR'[Key_AR]
RETURN
CALCULATE(
COUNTROWS('Bill1_AR'),
all('Bill1_AR'),
'Bill1_AR'[Key_AR]= CheckCountARKey_AR
)
Hello charleshale
You are getting locked in the row context of the table. Give this a try.
Column 2 = VAR _Index = Bill1_AR[Index] RETURN CALCULATE( COUNTROWS( Bill1_AR), ALLEXCEPT( Bill1_AR,Bill1_AR[Key_AR] ), Bill1_AR[Index] < _Index ) +1I use a VAR instead of EARLIER, I think it makes it easier to read.
3 Replies
Hello charleshale
You are getting locked in the row context of the table. Give this a try.
Column 2 = VAR _Index = Bill1_AR[Index] RETURN CALCULATE( COUNTROWS( Bill1_AR), ALLEXCEPT( Bill1_AR,Bill1_AR[Key_AR] ), Bill1_AR[Index] < _Index ) +1I use a VAR instead of EARLIER, I think it makes it easier to read.
jdbuchanan71 - you have unlocked me from the row context. Thank you. This works great and is much more elegant.
charleshale try this, add as a column
Column 2 = CALCULATE( COUNTROWS( 'Table' ), ALLEXCEPT( 'Table', 'Table'[Key_AR] ), 'Table'[Index] <= EARLIER( 'Table'[Index] ) )
