How can one convert a number to ordinal form in powerquery-CodePudding

is there a way to convert a rank number to ordinal form in powerquery.

For instance, Rank: 1, 2, 3, 4, 5, 31, 52 etc.This should be displayed as 1st, 2nd, 3rd, 4th, 5th, 31st 52nd.

Thank you!

CodePudding user response：

A simple function to calculate the ordinal suffix:

let
    Source = Table.FromList({1..100},Splitter.SplitByNothing(),{"Number"}),
    #"Added Suffix" = Table.AddColumn(Source, "Ordinal Suffix", each Record.FieldOrDefault([11="th",12="th",13="th"],Text.End(Text.From([Number]),2),Record.FieldOrDefault([1="st",2="nd",3="rd"],Text.End(Text.From([Number]),1),"th")), type text),
    #"Added Ordinal" = Table.AddColumn(#"Added Suffix", "Ordinal Number", each Text.Combine({Text.From([Number], "en-GB"), [Ordinal Suffix]}, ""), type text)
in
    #"Added Ordinal"

CodePudding user response：

Courtesy of

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyaQkQg0qZA VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
    Last2Characters = Text.End( Number.ToText( [Column1] ), 2 ),
    LastCharacter = Text.End( Number.ToText( [Column1]), 1 )
in
     if List.Contains( { "11", "12", "13" }, Last2Characters ) then "th"
else if LastCharacter = "1" then "st"
else if LastCharacter = "2" then "nd"
else if LastCharacter = "3" then "rd"
else "th"),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Custom", "Merged", each Text.Combine({Text.From([Column1], "en-GB"), [Custom]}, ""), type text)
in
    #"Inserted Merged Column"