Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fedpar
Employee
Employee

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
A18-Apr
A19-Apr
A23-Apr
A1-May
B21-Apr
B21-Apr
B30-Apr
B4-May

 

And I would like to have the indices show like this:

Group       Date            Index
A18-Apr1
A19-Apr2
A23-Apr3
A1-May4
B21-Apr1
B21-Apr2
B30-Apr3
B4-May4

How can I perform this dynamically?

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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 Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

52 REPLIES 52
Anonymous
Not applicable

Someone might be able to make the code a bit tidier

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:

 

qsong_0-1597324484996.png

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:

 

qsong_1-1597324755145.png

Thank you, I appreciate any ideas to fix it.

 

Best, Qianru

Anonymous
Not applicable

saving this jewel to favorites!

nsrshkh
Frequent Visitor

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

mbuick
Frequent Visitor

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.

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

mbuick
Frequent Visitor

Thanks for the video! This worked a treat.

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

remig
Frequent Visitor

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. 

Hi ImkeF,

 

There is no video there.  Can you please check.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
dong
Regular Visitor

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
A18-Apr1
A18-Apr1
A23-Apr2
A1-May3
B21-Apr1
B21-Apr1
B30-Apr2
B30-Apr2

 

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

 

table.jpg

Need:

table2.jpg

Hi @Anthony007,

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.