Forum Discussion
Need to create Sorting column based on two columns
Hi, Can any one please help me out the below senario. I want to sort the data based on two column and insert the one column for sorting order. Example: A B C Rank XX kapil ...
Just sort on column A, B and C. Next add an Index column.
let
Source = Table1,
#"Sorted Rows" = Table.Sort(Source,{{"A", Order.Ascending}, {"B", Order.Ascending}, {"C", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Sorting", 1, 1)
in
#"Added Index"
If this is not what you want, you'd better reformulate your requirements.
Thank you for your quick reponse Marcel.
Actually i dont need to sort the "A" & "B", based one A and B column i have the data for "C" column.
for that "C" column,i have to add the column give the sorting number.
Thanks,
kapil
I would be glad to help, but I really don't understand what you are looking for.
Maybe the Rank column is what you are looking for??
The query below adds the Rank column for each combination of values in columns A and B.
let Source = Table1, #"Added Index" = Table.AddIndexColumn(Source, "OriginalSort", 1, 1), #"Grouped Rows" = Table.Group(#"Added Index", {"A", "B"}, {{"AllData", each Table.AddIndexColumn(_,"Rank",1,1), type table}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"C", "OriginalSort", "Rank"}, {"C", "OriginalSort", "Rank"}), #"Sorted Rows" = Table.Sort(#"Expanded AllData",{{"OriginalSort", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"OriginalSort"}) in #"Removed Columns"