I have a dataset that has 'marketing funnel steps' that are recorded for each user's interaction with the experience. Some of the steps recorded are for tracking purposes only and in order for me to make several calculations that are needed, such as how much time users spend in each 'step', then I have to filter out the tracking steps, thus leaving me with gaps in my numbering per each ID group. I need the numbers to be incremental: 1,2,3,4,5, etc by 'ID' to enable me to aggregate by each step across the data. Does anyone know of a way to renumber (re-index) by group in this situation? I have also included a picture of current state and the column that I need to produce. For the types of operations that I need to perform, it would be ideal to be able to do this in both M query editor (first priority for me) as well as in a calculated DAX column (to allow for real time renumbering based on what is filtered).
Thank you @v-yuta-msft, this works very well as a calculated column.
Do you, or anyone, know the equivalent of RANKX in Query Editor M language? My end goal after this step is to perform an equivalent of CONCATENATEX in Query Editor...but this has to be preceded by a re-ranking by category first in Query Editor.
Thank you @ChrisMendoza! This almost works but seems to only ReRank correctly if the rows are initially in the right order (in the raw data). Unfortunately, in my case that will never be the case and I should have showed that in my sample data. When I change the order of my sample data, I get the below. Do you know how in the Custom Column - Table.AddIndexColumn([GroupByID],"ReRank",1,1) - it could ReRank by the order of existing Steps within each ID group?
For some reason I think I remember reading an article one time stating some risks of using one large simple rank function, can't remember the details, will have to search for it - I think it said that method works most of the times but not all the times.
Do you know if it is possible to do in the Custom Column though? It would need to be the equivalen of the following DAX but in Query Editor: RANKX(ALL(Sheet1), Sheet1[Step]), , DESC, Dense. And possibly ranking within each group individually would have better performance than ranking everything together?