Forum Discussion
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"
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.
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.
YES!!! Imke you are an absolute star :)
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...
Excellent solution - and thanks for uploading the video.
Just one question, how can I choose which column to rank on?
Runar Wigestrand
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}}),
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 A 18-Apr 1 A 18-Apr 1 A 23-Apr 2 A 1-May 3 B 21-Apr 1 B 21-Apr 1 B 30-Apr 2 B 30-Apr 2 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.
