Forum Discussion
Index column in power query based on date sorting
Hi!
I need help creating an index column in Power Query.
I would like to index rows with same ID based on the Date in ascending order.
I would like it to look something like this:
| ID | Sub ID | Date | Index |
| ABC | ABC1 | 2020-01-01 | 1 |
| ABC | ABC2 | 2020-01-02 | 2 |
| ABC | ABC3 | 2020-01-03 | 3 |
| ABC | ABC4 | 2020-01-04 | 4 |
| DEF | DEF1 | 2020-01-02 | 1 |
| DEF | DEF2 | 2020-01-05 | 4 |
| DEF | DEF3 | 2020-01-04 | 3 |
| DEF | DEF4 | 2020-01-03 | 2 |
I have tried to do the following steps:
1 sort Date acending
2 Group by ID (all rows into "Rank")
3 create a index column with: Table.AddIndexColumn([Rank], "Index", 1, 1))
4 Expand to new rows.
The problem is that the first sorting step seems to be ignored. I'm new to this and can't figure out another way to solve it. I'd be so happy and thankful for your help!
Cheers!
Hello Anonymous
this code should do the trick
let Source = #table ( {"ID","Sub ID","Date"}, { {"ABC","ABC1","2020-01-01"}, {"ABC","ABC2","2020-01-02"}, {"ABC","ABC3","2020-01-03"}, {"ABC","ABC4","2020-01-04"}, {"DEF","DEF1","2020-01-02"}, {"DEF","DEF2","2020-01-05"}, {"DEF","DEF3","2020-01-04"}, {"DEF","DEF4","2020-01-03"} } ), ToDate = Table.TransformColumns ( Source, { { "Date", each Date.From(_,"en-US"), type date } } ), Group = Table.Group(ToDate, {"ID"}, {{"AllRows", (tableint) => let sort = Table.Sort(tableint,{"Date", Order.Ascending}), AddIndex = Table.AddIndexColumn(sort,"Index",1) in AddIndex}}), Expand = Table.ExpandTableColumn(Group, "AllRows", {"Sub ID", "Date", "Index"}, {"Sub ID", "Date", "Index"}) in ExpandCopy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
10 Replies
Hello Anonymous
this code should do the trick
let Source = #table ( {"ID","Sub ID","Date"}, { {"ABC","ABC1","2020-01-01"}, {"ABC","ABC2","2020-01-02"}, {"ABC","ABC3","2020-01-03"}, {"ABC","ABC4","2020-01-04"}, {"DEF","DEF1","2020-01-02"}, {"DEF","DEF2","2020-01-05"}, {"DEF","DEF3","2020-01-04"}, {"DEF","DEF4","2020-01-03"} } ), ToDate = Table.TransformColumns ( Source, { { "Date", each Date.From(_,"en-US"), type date } } ), Group = Table.Group(ToDate, {"ID"}, {{"AllRows", (tableint) => let sort = Table.Sort(tableint,{"Date", Order.Ascending}), AddIndex = Table.AddIndexColumn(sort,"Index",1) in AddIndex}}), Expand = Table.ExpandTableColumn(Group, "AllRows", {"Sub ID", "Date", "Index"}, {"Sub ID", "Date", "Index"}) in ExpandCopy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmytry this code, I sort by ID and Date, the order is preserved then
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtIBkYZAysjAyEDXwBCIlGJ1kOSMkOWMUOWMkeWMUeVMkOVMwHIurm5AQSBpiGkmXA7FPlNUOWM8ZpqguSUWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Sub ID" = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Sub ID", type text}, {"Date", type date}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"Date", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Rank", each _, type table [ID=text, Sub ID=text, Date=date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rank], "Index", 0, 1)), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"ID", "Sub ID", "Date", "Index"}, {"ID", "Sub ID", "Date", "Index"}) in #"Expanded Custom"Hi! Thank you so much for taking the time to respond! I did however try exactly those steps in your code. The sorting is correct at first. but when I continue to add steps (like merging tables and adding conditional columns) the sorting is lost and index 1 is no longer on the row with the first date. Is there anyway to get around this problem?
can you share what steps are you making further? is ti possible to add the index after you make these transformations?
