Forum Discussion
Custom column Index or Ranking by other column
I'm looking to add an index column, but have it increase according to a certain column value. Let me give an example; let's say my data is:
| Group | Date |
| A | 18-Apr |
| A | 19-Apr |
| A | 23-Apr |
| A | 1-May |
| B | 21-Apr |
| B | 21-Apr |
| B | 30-Apr |
| B | 4-May |
And I would like to have the indices show like this:
| Group | Date | Index |
| A | 18-Apr | 1 |
| A | 19-Apr | 2 |
| A | 23-Apr | 3 |
| A | 1-May | 4 |
| B | 21-Apr | 1 |
| B | 21-Apr | 2 |
| B | 30-Apr | 3 |
| B | 4-May | 4 |
How can I perform this dynamically?
Thats like an index on a table partition. You can create that by using grouping on the column and returning "_" - which means that all column of the table (but only for the specific value in the column) will be return. You then nest your Index-command in:
let Source = Table1, Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"}) in #"Expanded Partition"
53 Replies
Thats like an index on a table partition. You can create that by using grouping on the column and returning "_" - which means that all column of the table (but only for the specific value in the column) will be return. You then nest your Index-command in:
let Source = Table1, Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"}) in #"Expanded Partition"Imke
nice? Is this hand coded? If so, can you explain it? Is is like a partition over clause in TSQL?
Hi Matt,
good point – it’s handwritten. Actually in this case we wouldn’t have to nest it in but could also have added a new column with a handwritten Table.AddIndexColumn-command instead. Or a separate function. Might be a matter of taste at the end. (But I’d recommend to delete the other column before expanding).
let Source = Table1, Partition = Table.Group(Source, {"Group"}, {{"Partition", each _, type table}}), #"Added Custom" = Table.AddColumn(Partition, "Custom", each Table.AddIndexColumn([Partition], "Index", 1,1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Partition"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "Index"}, {"Date", "Index"}) in #"Expanded Custom"What many people don’t know about is the syntax sugar around “each” and that the last argument of Table.AddColumn takes in a function. So no need to write “() =>”, just take “each”.
I’ve given this technique the name partition, because it returns the same like the PARTITION OVER and I prefer catchy names (but of course this could also lead to confusion). But if applied to a SQL-source it will be executed as GROUP BY (if it comes with a statement that would fold, like SUM or AVERAGE – in our example here with an Index it needs to return all row, so no folding would take place on the server).
But still: This technique is a performance saver when it comes to iterative operations like running totals (don't Table.SelectColumns (equivalent to WHERE) - because they would always iterate over the whole table!!). But if you are querying SQL-sources a native PARTITION OVER SQL-query would be even faster.
I have used this procedure succefully to obtian rankings based on groups, however, when mergeing with another table to gather detailed information, upon expanding the Table Column, the resulting values get scrambled. Only those generated using the Group and Index procedure... Could it be a Power Query Bug?
It might help if you buffer the group-step (Table.Buffer).
Otherwise I would need more detailled information of how the scramble looks like/what exactly is the problem.
Is there any chance to implement your beautiful solution in direct query model?
Unfortunately this doesn't work in Direct Query mode and I can also not think of a workaround unfortunately.
Hi Anonymous ,
did you check out this video already?:
(1) NestedIndex in PowerBI - YouTubeI have used this procedure succefully to obtian rankings based on groups, however, when mergeing with another table to gather detailed information, upon expanding the Table Column, the resulting values get scrambled. Only those generated using the Group and Index procedure... Could it be a Power Query Bug?
Hi. I'm trying to do something different about the cases exposed.
I have a table that need an increase index when I have some change between three columns. I mean:
Worker Fecha Valor Index A 16/04/2018 TRUE 1 A 16/04/2018 TRUE 1 A 16/04/2018 FALSE 2 A 16/04/2018 TRUE 3 B 15/04/2018 TRUE 4 B 16/04/2018 FALSE 5 B 16/04/2018 TRUE 6 B 16/04/2018 TRUE 6 Starting by 1 in each change of first 3 columns I need to increase the index. I'm trying with all the answer from this post but I can't find the solution.
Other possibility is to save the last combination and the last index in a parameter or another table, but I can't find how to save it in a parameter or table.
Hi. I'm trying to do something different about the cases exposed.
I have a table that need an increase index when I have some change between three columns. I mean:
Worker Fecha Valor Index A 16/04/2018 TRUE 1 A 16/04/2018 TRUE 1 A 16/04/2018 FALSE 2 A 16/04/2018 TRUE 3 B 15/04/2018 TRUE 4 B 16/04/2018 FALSE 5 B 16/04/2018 TRUE 6 B 16/04/2018 TRUE 6 Starting by 1 in each change of first 3 columns I need to increase the index. I'm trying with all the answer from this post but I can't find the solution.
Other possibility is to save the last combination and the last index in a parameter or another table, but I can't find how to save it in a parameter or table.
In Excel is very easy because yo can calculate from the information in the last cell, but here I can't.
The excel formula could be:
=SI(A2=A1;SI(C2="FALSE";SI(C1="FALSE";E1;E1+1);SI(C1="FALSE";E1+1;E1));E1+1)
Yes, that's possible. No use for the grouping in this case, as your index shall continue to run. The only thing you need here is a reference to your previous row. For performance reasons, I recommend this method:
