Search code examples
excelpowerquerydata-analysisdata-cleaningm

How can one convert a number to ordinal form in powerquery

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!

Solution

  • Courtesy of https://gorilla.bi/power-bi/ordinal-numbers/

    enter image description here

    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"