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?

[Anonymous]

Someone might be able to make the code a bit tidier

[qsong]

Hi @Anonymous 

Thank you for your answer, but I think I am not clear about my question. I want to evaluate both criterion and create one ranking. 

In the example, it should be like that:

Does it make sense now? @ImkeF provided us with a solution for it in the blog, my issue is that the ranking is not proper for some observations, so my result may look like that:

Thank you, I appreciate any ideas to fix it.

Best, Qianru

[Anonymous]

saving this jewel to favorites!

[nsrshkh]

You are a genious!!! Saved lots of time . Thanks for the soultion

[mbuick]

HI,

I've just found that after grouping and then expanding the group to continue using all fields in the table, the field types are all changed to alpha numeric, hence rendering all the graphs and charts unoperable. Is there a way to bulk return all the field types to their origin types or negate this effect in the grouping?

thanks.

[ImkeF]

Yes, thats a bit of a pain that I've just realized recently. You can avoid it by using Table.Combine instead of expanding the column like this:

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 with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "de-DE"),
    Partition = Table.Group(#"Changed Type with Locale", {"Group"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Date", Order.Ascending}}), "Index",1,1), type table}}),
    Table.Combine = Table.Combine(Partition[Partition])
in
    Table.Combine

[mbuick]

Thanks for the video! This worked a treat.

[mwaltercpa]

Short blurry video doesn't matter, best 1 minute on youtube all week, thanks!! Mark

[remig]

Thank you so much @ImkeF, I had lost myself for hours in DAX, to finally realise that I needed to create the index/rank in Query Editor. Your video was awesome, the solution is so simple! 

In my case, I had to insert a custom step Table.Sort before to get the right index values. 

[Ashish_Mathur]

Hi ImkeF,

There is no video there.  Can you please check.

[ImkeF]

Hi @Ashish_Mathur, I've checked & can see the video.

Pls try a different browser.

If that fails as well, pls post screenshot as a bug-report here: https://community.powerbi.com/t5/Issues/idb-p/Issues

[Ashish_Mathur]

Hi,

I've tried both Edge and Internet Explorer and the problem persists.  I will try it in some other browser as well.

Thank you.

[ImkeF]

That's strange.

You would help with a bug-report.

Here's the link to the video on youtube: https://www.youtube.com/watch?v=-3KFZaYImEY

[Ashish_Mathur]

Hi,

With data exactly as shown by fedpar in his original post dated 05-07-2016, the following code provided by you is not working

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"

When i paste that code in the New Query window of Power Query, i get an error message

Expression.Error: A cyclic reference was encountered during evaluation.

Please help.

[ImkeF]

Hi @Ashish_Mathur,

it works for me. You might not have applied the code correctly or have other queries in your current file with name conflicts that cause this message. Please have a look at this video - there I've worked with exactly the same data and used the query you've mentioned: https://youtu.be/S9xlq5KUZ60

[Ashish_Mathur]

Hi,

It is working absolutely fine now.  Thank you very much.

[dong]

Hi, 

The solution is really nice! I think this is pretty close to what I have been looking for, but what I need is the Index column ONLY increase when the Date column changes, please see example below. Is there anyway to do this? Thank you!

Group	Date	Index
A	18-Apr	1
A	18-Apr	1
A	23-Apr	2
A	1-May	3
B	21-Apr	1
B	21-Apr	1
B	30-Apr	2
B	30-Apr	2

[ImkeF]

Yes, this looks very similar, but we need to create an additional lookup/merge-step here:

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

Lookup = Table.NestedJoin(Source,{"Group", "Date"},Partition,{"Group", "Date"},"NewColumn",JoinKind.LeftOuter)

You can then expand the "Index" column from the newly created column holding the merged content.

[Anthony007]

Hi @ImkeF! Can u help please with this example. We have a table with three columns. Need to build a rating (or index) given the week and the value

Need:

[ImkeF]

Hi @Anthony007,

the formula should work for you as well. Have you tried it?

[Anthony007]

@ImkeF, I tried all the examples and nothing happened.For each week it is necessary to make a rating of values.I did not receive such a result