Forum Discussion
Index column in power query based on date sorting
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
Hi Anonymous ,
Assuming your another table is like that.
Then we can merge tables and add a custom column in the merged one.
if [Index] = 1 then [Table.new date] else [Date]
M code for your reference.
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", 1, 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"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Index"}, Table, {"index"}, "Table", JoinKind.LeftOuter),
#"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"new date"}, {"Table.new date"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Table", "Custom", each if [Index] = 1 then [Table.new date] else [Date]),
#"Sorted Rows1" = Table.Sort(#"Added Custom1",{{"ID", Order.Descending}, {"Index", Order.Ascending}})
in
#"Sorted Rows1"
Hi!
The "new date" table looks like this:
| ID | New Date |
| ABC | 2020-01-01 |
| GHJ | 2020-05-01 |
As soon as i merge another table, the index order gets "flipped" (do not follow the date sorting).
Is there another way to create index column than the one suggested above?
Thanks so much in advance!
can you share the M code for the transformations that you make? You can copy it from Advanced Editor, in the Query Editor Home tab
