Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
@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.
Proud to be a Super User!
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?
@Anonymous,
Do you mean that in your data you need:
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
Proud to be a Super User!
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |