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
davidestg
Helper I
Helper I

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: 

 

WorkerFechaValorIndex
A16/04/2018TRUE1
A16/04/2018TRUE1
A16/04/2018FALSE2
A16/04/2018TRUE3
B15/04/2018TRUE4
B16/04/2018FALSE5
B16/04/2018TRUE6
B16/04/2018TRUE6

 

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)

 

 

 

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

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

davidestg
Helper I
Helper I

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: 

 

WorkerFechaValorIndex
A16/04/2018TRUE1
A16/04/2018TRUE1
A16/04/2018FALSE2
A16/04/2018TRUE3
B15/04/2018TRUE4
B16/04/2018FALSE5
B16/04/2018TRUE6
B16/04/2018TRUE6

 

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
New Member

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

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

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

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

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?

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.

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

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

Imke

 

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

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

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"

 

You're welcome. Please check out this video:

 

 

 

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

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

Leroy
Frequent Visitor

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

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.

 

 WEIGHTHEIGHTRANKCORRECT RANK
A651.723
B651.832
C701.7511

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

 

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.

 

 

Leroy_0-1597272060954.png

 

 

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’

Leroy_1-1597272060958.png

 

 

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

 

Leroy_2-1597272082435.png

 

I hope this helps

 

Leroy
Frequent Visitor

Someone might be able to make the code a bit tidier

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:

 

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!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!