Forum Discussion

JediMole's avatar
JediMole
Helper II
8 months ago
Solved

Sort Order issue

I created a data table so my visuals will sort properly, but it appears about 239 of the answers are unique in such a way its causing the selection to sort by not to work. 

 

When I check the data I can see all the unique free text answers result in a null value for the sort order, so the error message makes sense im trying to rack my brain how to best fix this

I know I could replace the Answers with a numeric number to force them in a specific order but that seems like a chest, is there a way to make this work?

Help
question
  • Jai-Rathinavel's avatar
    Jai-Rathinavel
    8 months ago

    Hi JediMole I have created a Power Query logic that automatically assigns a new Sort Order value to manually entered answers. This prevents errors when sorting by other columns and ensures consistent ranking. Attached the file and power query for your reference.



    let
    Source = Csv.Document(File.Contents("C:\Users\jaike\Downloads\rank.csv"), [Delimiter=",", Columns=3, Encoding=1252]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sort Order", Int64.Type}}),
    MaxRank = try List.Max(List.RemoveNulls(#"Changed Type"[Sort Order])) otherwise 0,
    NullAnswerKeys = Table.Distinct(Table.SelectColumns(Table.SelectRows(#"Changed Type", each [Sort Order] = null), {"Answer"})),
    RankedNullKeys = Table.AddIndexColumn(NullAnswerKeys, "NewRank", MaxRank + 1, 1),
    Joined = Table.NestedJoin(#"Changed Type", "Answer", RankedNullKeys, "Answer", "New", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "New", {"NewRank"}, {"NewRank"}),
    Final = Table.AddColumn(Expanded, "New Sort Order", each if [Sort Order] = null then [NewRank] else [Sort Order], Int64.Type),
    Output = Table.RemoveColumns(Final, {"NewRank","Sort Order"}),
    #"Sorted Rows" = Table.Sort(Output,{{"New Sort Order", Order.Ascending}})
in
    #"Sorted Rows"


    Thanks,
    Jai

7 Replies

  • GeraldGEmerick's avatar
    GeraldGEmerick
    Super User
    8 months ago

    JediMole Could you replace all null values with a random number that is 12 or greater? You could make the range large enough that you would likely not get duplicates. Another thought, do you really need the Additional Comments for what you are doing? You could simply get rid of those if they are not really that important or move those to another, linked table.

    • JediMole's avatar
      JediMole
      Helper II
      8 months ago

      I could replace the Nulls, I did replace them with just a 0 (zero) but I didnt try all unique numbers.  I do need the additional as I have another drill thru visual the team uses

       

      • Jai-Rathinavel's avatar
        Jai-Rathinavel
        Super User
        8 months ago

        Hi JediMole I have created a Power Query logic that automatically assigns a new Sort Order value to manually entered answers. This prevents errors when sorting by other columns and ensures consistent ranking. Attached the file and power query for your reference.



        let
    Source = Csv.Document(File.Contents("C:\Users\jaike\Downloads\rank.csv"), [Delimiter=",", Columns=3, Encoding=1252]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sort Order", Int64.Type}}),
    MaxRank = try List.Max(List.RemoveNulls(#"Changed Type"[Sort Order])) otherwise 0,
    NullAnswerKeys = Table.Distinct(Table.SelectColumns(Table.SelectRows(#"Changed Type", each [Sort Order] = null), {"Answer"})),
    RankedNullKeys = Table.AddIndexColumn(NullAnswerKeys, "NewRank", MaxRank + 1, 1),
    Joined = Table.NestedJoin(#"Changed Type", "Answer", RankedNullKeys, "Answer", "New", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "New", {"NewRank"}, {"NewRank"}),
    Final = Table.AddColumn(Expanded, "New Sort Order", each if [Sort Order] = null then [NewRank] else [Sort Order], Int64.Type),
    Output = Table.RemoveColumns(Final, {"NewRank","Sort Order"}),
    #"Sorted Rows" = Table.Sort(Output,{{"New Sort Order", Order.Ascending}})
in
    #"Sorted Rows"


        Thanks,
        Jai

  • MichaelVenables's avatar
    MichaelVenables
    Regular Visitor
    8 months ago

    Hi,

     

    Just a simple idea which may or may not help. But your sort order column is of format 'Text' so it wont easily order as a text format. If you change this to Interger it can be sorted in the order you might want it to be?

    Thanks,

    Michael

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    8 months ago

    Hi JediMole 

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.


    Thank you.

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    8 months ago

    Hi JediMole 

    May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.


    Thank you