cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tombrown406 Frequent Visitor
Frequent Visitor

Renumber by group - incremental index to fix number gaps

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!

 

 Renumber Incremental column index.png

6 REPLIES 6
Community Support Team
Community Support Team

Re: Renumber by group - incremental index to fix number gaps

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)

Capture.PNG

 

Regards,

Jimmy Tao

Highlighted
tombrown406 Frequent Visitor
Frequent Visitor

Re: Renumber by group - incremental index to fix number gaps

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 Senior Member
Senior Member

Re: Renumber by group - incremental index to fix number gaps

@tombrown406,

 

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:

 

3.PNG

 

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

tombrown406 Frequent Visitor
Frequent Visitor

Re: Renumber by group - incremental index to fix number gaps

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?  

 

ReRank.JPG

ChrisMendoza Senior Member
Senior Member

Re: Renumber by group - incremental index to fix number gaps

@tombrown406,

 

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:

7.PNG

 

then

 

8.PNG

 

which the group by shows as

 

9.PNG

 

 

tombrown406 Frequent Visitor
Frequent Visitor

Re: Renumber by group - incremental index to fix number gaps

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?

 

Screenshot.JPG

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)