Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 )
Solved! Go to Solution.
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 ) +1
I use a VAR instead of EARLIER, I think it makes it easier to read.
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 ) +1
I 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] ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |