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,
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.
Sorry, this couldn't work.
Here is the new code (tested ;-) ):
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
ExpandedIndexHi 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"Hi ImkeF,
Your solution works great! You are so smart! Thank you!
Hi ImkeF! Can u help please with this example. We have a table with three columns. Need to build a rating (or index) given the week and the value
Need:
Hi Anthony007,
the formula should work for you as well. Have you tried it?
ImkeF, I tried all the examples and nothing happened.For each week it is necessary to make a rating of values.I did not receive such a result
Many thanks ImkeF this is fantastic. However I am trying to use this in DirectQuery mode and it is stumbling at the last step: 'Table.ExpandTableColumn' - I get an error message saying 'This step results in a query that is not supported in DirectQuery mode'.
Might you be able to help?
Kind regards,
Will
