Forum Discussion
Power Query - Custom Column tracking when the value of another column changes
Hello all!
I have a small question.
Is there a way I can create a custom column that will numerically add 1 each time the value of another column changes?
| A |
| A |
| A |
| B |
| B |
| C |
| A |
| A |
| C |
| C |
Above is the original table. The desired custom column would look like:
| A | 1 |
| A | 1 |
| A | 1 |
| B | 2 |
| B | 2 |
| C | 3 |
| A | 4 |
| A | 4 |
| C | 5 |
| C | 5 |
Notice that the categories in the first columns repeat themselves, but the custom column is only to tally up when the value changes.
Been looking at this for a while, and I'm learning a lot, but I'm not sure how to approach this one.
Thank you!
You can first sort like you have it, then Table.Buffer, then Group By Column1 and add an All Rows aggregation--but then go back to that step and add the optional parameter GroupKind.Local.
From there, you can add an index column starting at 1. Then Remove the columns except the table column and the index column. Now expand the table column, and you are all set.
--Nate
2 Replies
You can first sort like you have it, then Table.Buffer, then Group By Column1 and add an All Rows aggregation--but then go back to that step and add the optional parameter GroupKind.Local.
From there, you can add an index column starting at 1. Then Remove the columns except the table column and the index column. Now expand the table column, and you are all set.
--Nate
This worked! To clarify for others, I did not sort the list at the start but kept it at the same order (The wording confused me a bit). I will study this further to understand it better, but following the steps you provided pulled it off perfectly!
Thank you so much!
