cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fedpar
Microsoft
Microsoft

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

49 REPLIES 49
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

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
Regular Visitor

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"

 

pi.png

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

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors