Forum Discussion

Joris_NL's avatar
Joris_NL
Helper III
2 years ago
Solved

Custom sorting for calculated table (circular error)

Hi,

 

I have a calculated table, in which I want the NAME column (all unique) to sort by CATEGORY first, and then sorted alphabetically second. Like this:

NAME   CATEGORY Nr.    *My custom sorting column*   (The desired sorting result, based on custom sorting column ) 
A 1 1001 first position
B 2 2003 third
C 1 1002 second
D 3 3004 fourth
E4 4006 sixth
F3 3005 fifth

 

I managed to make the output for the custom sorting column:

 

 

My custom sorting column = 
RANKX ( 'Table' ; 'Table'[NAME] ; ; ASC )
+
( 1000 * 'Table'[CATEGORY Nr.] )

 

 

 

But I can't use this column to sort NAME! This gives a circular dependancy error. So the order of the names is used for the RANKX function, which is then used to order the names. 

 

Any suggestions?

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    If possible, one of ways is to create sort column in power query editor.
    Please check the below picture and the attached pbix file.

     

    let
    Source = source,
    #"Sorted Rows" = Table.Sort(Source,{{"CATEGORY Nr.", Order.Ascending}, {"NAME", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"CATEGORY Nr."}, {{"Count", each Table.AddIndexColumn( _, "index",1,1)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"NAME", "index"}, {"NAME", "index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "sort_order", each [#"CATEGORY Nr."]*1000 + [index]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"NAME", type text}, {"sort_order", Int64.Type}})
in
    #"Changed Type"

     

     

     

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    Hi,

    If possible, one of ways is to create sort column in power query editor.
    Please check the below picture and the attached pbix file.

     

    let
    Source = source,
    #"Sorted Rows" = Table.Sort(Source,{{"CATEGORY Nr.", Order.Ascending}, {"NAME", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"CATEGORY Nr."}, {{"Count", each Table.AddIndexColumn( _, "index",1,1)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"NAME", "index"}, {"NAME", "index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "sort_order", each [#"CATEGORY Nr."]*1000 + [index]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"NAME", type text}, {"sort_order", Int64.Type}})
in
    #"Changed Type"

     

     

     

     

  • Joris_NL's avatar
    Joris_NL
    Helper III
    2 years ago

    Wow thank you, that must help a lot of people. Unfortunately I'm working with a calculated table. But I'll try to add the column to the original source table and get back to you.