Reply
Regular Visitor
Posts: 17
Registered: ‎05-04-2016
Accepted Solution

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?


Accepted Solutions
Highlighted
Super User
Posts: 1,620
Registered: ‎09-06-2015

Re: Custom column Index or Ranking by other column

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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post


All Replies
Highlighted
Super User
Posts: 1,620
Registered: ‎09-06-2015

Re: Custom column Index or Ranking by other column

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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Posts: 1,627
Registered: ‎07-03-2015

Re: Custom column Index or Ranking by other column

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.
Super User
Posts: 1,620
Registered: ‎09-06-2015

Re: Custom column Index or Ranking by other column

[ Edited ]

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Member
Posts: 71
Registered: ‎02-04-2016

Re: Custom column Index or Ranking by other column

[ Edited ]

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"

 

Super User
Posts: 1,620
Registered: ‎09-06-2015

Re: Custom column Index or Ranking by other column

[ Edited ]

You're welcome. Please check out this video:

 

 

 

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Member
Posts: 71
Registered: ‎02-04-2016

Re: Custom column Index or Ranking by other column

YES!!! Imke you are an absolute star Smiley Happy

 

I have been stuck on this for weeks and weeks.  The solution you have provided works perfectly.  Thank you so much!  What a great way to start the day...

 

 

Frequent Visitor
Posts: 9
Registered: ‎11-06-2015

Re: Custom column Index or Ranking by other column

Excellent solution - and thanks for uploading the video.

 

Just one question, how can I choose which column to rank on?

 

Runar Wigestrand

Super User
Posts: 1,620
Registered: ‎09-06-2015

Re: Custom column Index or Ranking by other column

Not sure if I understood you correctly, but I think you mean the field/column to group on? In this example it was also called Group:

 

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Frequent Visitor
Posts: 9
Registered: ‎11-06-2015

Re: Custom column Index or Ranking by other column

[ Edited ]

Hi, Imke.

 

Thanks for the quick reply. I was thinking more about if you have duplicates and want to keep only the newest record based on date. Then I want to group on one column and rank on another column ( for example the date column). 

 

Something like this:

 

Group on SerialNum, rank on date column

 

Snap_2017.02.14_09h33m50s_007_.jpg

 

 

 

 

 

 

 

 

 

 

Thanks.

 

Runar