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?

[davidestg]

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)

[ImkeF]

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:

https://www.youtube.com/watch?v=xN2IRXQ2CvI

[davidestg]

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. 

[javix72]

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?

[ImkeF · Accepted solution]

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"

[pandakillsalot]

Is there any chance to implement your beautiful solution in direct query model?

[ImkeF]

Unfortunately this doesn't work in Direct Query mode and I can also not think of a workaround unfortunately.

[javix72]

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?

[ImkeF]

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.

[javix72]

Thank you so much! It worked fine. After using the Table.Buffer it worked perectly...!

[MattAllington]

Imke

nice?  Is this hand coded?  If so, can you explain it?  Is is like a partition over clause in TSQL?

[ImkeF]

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.

[sebbyp]

I want to use this code that you have kindly provided in this thread to solve the same issue I am experiencing.  However, I am new to power bi and i cannot get it to work.  This is what i have done already and now i want to slot your code in to add this indexed column.  The only difference is "Group" would be "Ticker" in my case.  Can you help please? 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Exceldata.xlsx"), null, true),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Expanded Column2" = Table.ExpandTableColumn(#"Promoted Headers", "Column2", {"Column1", "Column10", "Column11", "Column12", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column2.Column1", "Column2.Column10", "Column2.Column11", "Column2.Column12", "Column2.Column2", "Column2.Column3", "Column2.Column4", "Column2.Column5", "Column2.Column6", "Column2.Column7", "Column2.Column8", "Column2.Column9"}),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Expanded Column2"),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers1", each ([Date] <> "Date")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"GDP CQOQ Index", "Ticker"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"GDP CQOQ Index_1", "Column16", "Sheet"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Removed Columns","#N/A N/A"," ",Replacer.ReplaceValue,{"Ticker", "Date", "BN_SURVEY_HIGH", "BN_SURVEY_NUMBER_OBSERVATIONS", "BN_SURVEY_LOW", "BN_SURVEY_AVERAGE", "BN_SURVEY_MEDIAN", "FIRST_REVISION_DATE", "FIRST_REVISION", "ACTUAL_RELEASE", "ECO_RELEASE_DT", "ECO_FUTURE_RELEASE_DATE", "PX_LAST"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","#N/A Field Not Applicable"," ",Replacer.ReplaceValue,{"Ticker", "Date", "BN_SURVEY_HIGH", "BN_SURVEY_NUMBER_OBSERVATIONS", "BN_SURVEY_LOW", "BN_SURVEY_AVERAGE", "BN_SURVEY_MEDIAN", "FIRST_REVISION_DATE", "FIRST_REVISION", "ACTUAL_RELEASE", "ECO_RELEASE_DT", "ECO_FUTURE_RELEASE_DATE", "PX_LAST"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value2",{{"Date", type date}, {"PX_LAST", type number}, {"ACTUAL_RELEASE", type number}, {"FIRST_REVISION", type number}, {"BN_SURVEY_LOW", type number}, {"BN_SURVEY_AVERAGE", type number}, {"BN_SURVEY_MEDIAN", type number}, {"BN_SURVEY_HIGH", type number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Ticker", "Date", "PX_LAST", "BN_SURVEY_NUMBER_OBSERVATIONS", "BN_SURVEY_HIGH", "BN_SURVEY_LOW", "BN_SURVEY_AVERAGE", "BN_SURVEY_MEDIAN", "FIRST_REVISION_DATE", "FIRST_REVISION", "ECO_RELEASE_DT", "ACTUAL_RELEASE", "ECO_FUTURE_RELEASE_DATE"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "BN_Surevy Range", each [BN_SURVEY_HIGH]-[BN_SURVEY_LOW]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"BN_Surevy Range", type number}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"Ticker", "Date", "PX_LAST", "BN_SURVEY_NUMBER_OBSERVATIONS", "BN_SURVEY_HIGH", "BN_SURVEY_LOW", "BN_SURVEY_AVERAGE", "BN_SURVEY_MEDIAN", "BN_Surevy Range", "FIRST_REVISION_DATE", "FIRST_REVISION", "ECO_RELEASE_DT", "ACTUAL_RELEASE", "ECO_FUTURE_RELEASE_DATE"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"BN_Surevy Range", "BN_Survey Range"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Actual Vs Consensus", each [ACTUAL_RELEASE]-[BN_SURVEY_MEDIAN]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"ECO_RELEASE_DT", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Custom", each Date.FromText([ECO_RELEASE_DT])),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom", type date}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type3",{{"Custom", "Eco Release Date"}}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns2",{{"ECO_FUTURE_RELEASE_DATE", type text}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type4", "Eco Future Release Date", each Date.FromText([ECO_FUTURE_RELEASE_DATE])),
    #"Changed Type5" = Table.TransformColumnTypes(#"Added Custom3",{{"Eco Future Release Date", type date}}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Changed Type5",{{"FIRST_REVISION_DATE", type text}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type6", "First Revision Date", each Date.FromText([FIRST_REVISION_DATE])),
    #"Changed Type7" = Table.TransformColumnTypes(#"Added Custom4",{{"First Revision Date", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type7",{"FIRST_REVISION_DATE", "ECO_RELEASE_DT", "ECO_FUTURE_RELEASE_DATE"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each true)
in
    #"Filtered Rows1"

[ImkeF]

You're welcome. Please check out this video:

and let me know if you need further help on this.

[Leroy]

Awesome. Great video. The video definitely helped explain things. Thanks

[qsong]

Hi @ImkeF 

I experimented your method to rank my data with two criterion. However, when I took a look at the data carefully, I found some data which is not ranked properly.

	WEIGHT	HEIGHT	RANK	CORRECT RANK
A	65	1.7	2	3
B	65	1.8	3	2
C	70	1.75	1	1

A,B,and C three people are ranked firstly by weight, if some of them have the same weight then we will rank them by height. I got the rank column which is not correct. Here I also show you the correct ranking.

So I tried to the buffer table query to conserve the sort, unfortunately, the issue has not gone so far. 

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

This is your query. The difference is that I rank based on two values - weight and height. Also, I consider merge later, as I have data which has the same weight and height, so I want them to have the same ranking. I used buffer table after Partition to fix the issue, the issue is still there.

I suspect that in the Partition, they sort weight and height as any type of numbers, because later when I expand the table (in the LookupTable step), the column weight and height come with any data type.  

How do you view this case?

I appreciate any help.

Thank you.

Best, Qianru

[Leroy]

This site may help. Even though it is power query the same techniques that use M code/DAX applies to Power BI. Look around the 20min mark in the video. It shows how to rank the same if the same.

23:03 / 30:42

MSPTDA 08: Power Query Group By feature & Table.Group Function

https://www.youtube.com/watch?v=hs21s0TWT14

I also recreated your data and added a few in case the second sort, by height also had a ‘match’

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Weight", Int64.Type}, {"Height", type number}}),

    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Weight", Order.Descending}}),

    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "IndexWeight", 1, 1),

    #"Sorted Rows1" = Table.Sort(#"Added Index",{{"Height", Order.Descending}}),

    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows1", "IndexHeight", 1, 1),

    #"Grouped Rows" = Table.Group(#"Added Index1", {"Weight"}, {{"All Rows", each _, type table [Person=text, Weight=number, Height=number, IndexWeight=number, IndexHeight=number]}, {"Rank Weight", each List.Min([IndexWeight]), type number}}),

    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Person", "Weight", "Height", "IndexHeight"}, {"All Rows.Person", "All Rows.Weight", "All Rows.Height", "All Rows.IndexHeight"}),

    #"Grouped Rows1" = Table.Group(#"Expanded All Rows", {"All Rows.Height"}, {{"Height", each _, type table [Weight=number, All Rows.Person=text, All Rows.Weight=number, All Rows.Height=number, All Rows.IndexHeight=number, Rank Weight=number]}, {"Rank Height", each List.Min([All Rows.IndexHeight]), type number}}),

    #"Expanded Height" = Table.ExpandTableColumn(#"Grouped Rows1", "Height", {"Weight", "All Rows.Person", "All Rows.Weight", "All Rows.Height", "All Rows.IndexHeight", "Rank Weight"}, {"Height.Weight", "Height.All Rows.Person", "Height.All Rows.Weight", "Height.All Rows.Height", "Height.All Rows.IndexHeight", "Height.Rank Weight"}),

    #"Removed Columns" = Table.RemoveColumns(#"Expanded Height",{"Height.All Rows.Weight", "Height.All Rows.IndexHeight", "Height.All Rows.Height"}),

    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Height.Rank Weight", "Rank Weight"}}),

    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Height.All Rows.Person", "All Rows.Height", "Height.Weight", "Rank Weight", "Rank Height"}),

    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"All Rows.Height", "Height"}, {"Height.Weight", "Weight"}}),

    #"Sorted Rows2" = Table.Sort(#"Renamed Columns1",{{"Rank Weight", Order.Ascending}, {"Rank Height",Order.Ascending}}),

    #"Reordered Columns1" = Table.ReorderColumns(#"Sorted Rows2",{"Height.All Rows.Person", "Weight", "Height", "Rank Weight", "Rank Height"})

in

    #"Reordered Columns1"

Result as below

I hope this helps

[Leroy]

Someone might be able to make the code a bit tidier

[qsong]

Hi @Leroy 

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!