Forum Discussion

kapil512's avatar
kapil512
Helper II
8 years ago

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:

 

ABCRank
XXkapilYes1
XXkapilNo2
XXkapilDon't Know3
YYDev11
YYDev2-32
YYDev4-63
YYDev7-104
YYDev>105

 

i have the use data like this, i want the sorting order based on the "A" and "B" and "C" comuns.

 

Thanks for your help.

 

Thanks,

kapil

Need Help
Show and Tell
Tips and Tricks

6 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

    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.

    • kapil512's avatar
      kapil512
      Helper II
      8 years ago

      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

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        8 years ago

        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"