Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Tips and Tricks
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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
	Expand

     

    Copy 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

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
6 years ago

try 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"