Custom column Index or Ranking by other column

[fedpar]

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?

[willfray]

Many thanks @ImkeF this is fantastic. However I am trying to use this in DirectQuery mode and it is stumbling at the last step: 'Table.ExpandTableColumn' - I get an error message saying 'This step results in a query that is not supported in DirectQuery mode'.

Might you be able to help?

Kind regards,

Will

[dong]

Hi ImkeF,

Thank you for your reply. But I didn't get it working...So I was trying the same sample data as below, and getting results as the image shows. Can you please help to check what could I missed? Thanks.

(Note: Column1 =Group column, Column2  = Date column)

let
Source = Excel.Workbook(File.Contents("C:\Users\dongl\Desktop\IndexTest.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
Partition = Table.Group(Sheet1_Sheet, {"Column1", "Column2"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Column2", Order.Ascending}}), "Index",1,1), type table}}),Lookup = Table.NestedJoin(Sheet1_Sheet,{"Column1", "Column2"},Partition,{"Column1", "Column2"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(Lookup, "NewColumn", {"Partition"}, {"NewColumn.Partition"}),
#"Expanded NewColumn.Partition" = Table.ExpandTableColumn(#"Expanded NewColumn", "NewColumn.Partition", {"Index"}, {"NewColumn.Partition.Index"})
in
#"Expanded NewColumn.Partition"

[ImkeF]

Sorry, this couldn't work.

Here is the new code (tested 😉 😞

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK00DMw0TMyMDRXitXBKWRkjCFkYKhnYIoQcgKpMkRRhUPI2ACvUCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    RemoveDups = Table.Distinct(#"Changed Type", {"Group", "Date"}),
    Partition = Table.Group(RemoveDups, {"Group"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Date", Order.Ascending}}), "Index",1,1), type table}}),
    LookupTable = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"}),
    Lookup = Table.NestedJoin(#"Changed Type",{"Group", "Date"},LookupTable,{"Group", "Date"},"NewColumn",JoinKind.LeftOuter),
    ExpandedIndex = Table.ExpandTableColumn(Lookup, "NewColumn", {"Index"}, {"Index"})
in
    ExpandedIndex

[dong]

Hi ImkeF,

Your solution works great! You are so smart! Thank you!

[runski]

Excellent solution - and thanks for uploading the video.

Just one question, how can I choose which column to rank on?

Runar Wigestrand

[ImkeF]

Not sure if I understood you correctly, but I think you mean the field/column to group on? In this example it was also called Group:

Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),

[nsrshkh]

You are a genious!!!

[runski]

Hi, Imke.

Thanks for the quick reply. I was thinking more about if you have duplicates and want to keep only the newest record based on date. Then I want to group on one column and rank on another column ( for example the date column). 

Something like this:

Group on SerialNum, rank on date column

Thanks.

Runar

[ImkeF]

Oh, I see: You need to fold in a Table.Sort-operation that will be executed before the Index is added. Sth like this:

Partition = Table.Group(Source, {"SerialNum"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Date_Updated", Order.Ascending}}), "Index",1,1), type table}}),

Does that work for you? 

[runski]

Hi, Imke.

Perfect! 

Thank you!

Runar Wigestrand

[sebbyp]

YES!!! Imke you are an absolute star 🙂

I have been stuck on this for weeks and weeks.  The solution you have provided works perfectly.  Thank you so much!  What a great way to start the day...