Forum Discussion

kapil512's avatar
kapil512
Icon for Helper II rankHelper 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:   A B C Rank XX kapil ...
Need Help
Show and Tell
Tips and Tricks
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity 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
    Icon for Helper II rankHelper 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
      Icon for Community Champion rankCommunity 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"
      • kapil512's avatar
        kapil512
        Icon for Helper II rankHelper II
        8 years ago

        Thank you Marcel,

         

        I will try and let you know, if i could not able to achive my result.

         

        I will share my Pbix file.

         

        Thank you,

        Kapil Dev