Renumber by group - incremental index to fix number gaps

[Anonymous]

Hi Community,

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).

Happy to clarify further if needed, thank you!

 

[v-yuta-msft]

Hi tombrown406,

To achieve your requirement, create a calculate column using DAX below:

Rank = RANKX(FILTER(Table1, Table1[ID] = EARLIER(Table1[ID])), RANKX(ALL(Table1), Table1[Step]), , DESC, Dense)

Regards,

Jimmy Tao

[Anonymous]

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.

As an alternative option, possibly I should try combining RANKX with CONCATENATEX in a calculated measure?  Radacad's article on RANKX in a calculated measure made me start thinking about this option: http://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[ChrisMendoza]

@Anonymous,

This should work in similar fashion in Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjExUdJRCs7IL1dwyslPzgZyHIHYUClWBybpVFpSkp+nEFCUWlwM4gKxEW5pZyA2RZIOLM1MzlYISi3IqQTyXIDYDLdmkNUWYGlTU1N0dyEshkiimgyy1xinLMheE5yyIGvNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Event = _t, #"Event Value" = _t, Step = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Event", type text}, {"Event Value", type text}, {"Step", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"GroupByID", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "AddIndexColumn", each Table.AddIndexColumn([GroupByID],"Number column that I need",1,1)),
    #"Expanded AddIndexColumn" = Table.ExpandTableColumn(#"Added Custom", "AddIndexColumn", {"Event", "Event Value", "Step", "Number column that I need"}, {"Event", "Event Value", "Step", "Number column that I need"})
in
    #"Expanded AddIndexColumn"

Basically, what it's doing is grouping by [ID] -> Adding in Index Column within each of the tables of Group By (in this example 2) -> then expanding each of those tables.

Produces the below:

You can then remove the [GroupByID] column and then close and apply.

[Anonymous]

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?  

[ChrisMendoza]

@Anonymous,

Do you mean that in your data you need:

• Grouped by [ID]
• Sorted by [Step]

then assigned a new value that will replace [Step] value so it is contiguous?

If so, they what if you tried to sort [Step] ascending then the other steps mentioned above.

I started with:

then

which the group by shows as

[Anonymous]

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?