Forum Discussion

Microsoft Employee

10 years ago

Solved

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 A 18-Apr A 19-Apr A 23-Apr...

ImkeF
10 years ago
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"

sebbyp

Helper III

10 years ago

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

Community Champion

10 years ago

You're welcome. Please check out this video:

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

sebbyp
Helper III
10 years ago
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...
runski
Frequent Visitor
9 years ago
Excellent solution - and thanks for uploading the video.

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

Runar Wigestrand
- ImkeF
  Community Champion
  9 years ago
  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}}),
  - runski
    Frequent Visitor
    9 years ago
    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
    
    Thanks.
    
    Runar
dong
Regular Visitor
9 years ago
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
- ImkeF
  Community Champion
  9 years ago
  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.
  - dong
    Regular Visitor
    9 years ago
    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"
Ashish_Mathur
Super User
9 years ago
Hi ImkeF,

There is no video there. Can you please check.
- ImkeF
  Community Champion
  9 years ago
  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
  - Ashish_Mathur
    Super User
    9 years ago
    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.
remig
Frequent Visitor
9 years ago
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.
mwaltercpa
Advocate III
8 years ago
Short blurry video doesn't matter, best 1 minute on youtube all week, thanks!! Mark
mbuick
Frequent Visitor
8 years ago
Thanks for the video! This worked a treat.

mbuick

Frequent Visitor

8 years ago

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.

ImkeF

Community Champion

8 years ago

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

nsrshkh
Frequent Visitor
8 years ago
You are a genious!!! Saved lots of time . Thanks for the soultion
Anonymous
Not applicable
8 years ago
saving this jewel to favorites!

Forum Discussion

Custom column Index or Ranking by other column

Related content

Duplicate ranking

Ranking KPI card

Rank with conditions

Dynamic Ranking

Custom Column

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