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...
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

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