Forum Discussion

johnlhaase's avatar
johnlhaase
Helper I
2 years ago

Identifying All Uppercase letters in a column

Hello

 

What is the M code for identifying a row/record with all uppercase letters?

 

Thanks

John Haase

Need Help

2 Replies

  • collinsg's avatar
    collinsg
    Solution Sage
    2 years ago

    Good day johnlhaase,

    The essence of the following is to take a string and compare it to itself in uppercase, i.e.

    if "string" = Text.Upper("string") then true else false

    The idea could be applied to a single column or a record. In the case of a record the steps are:

    1. Add a column, converting each record to a list.
    2. Combine the elements of each list as text.
    3. Compare each combined text with itself in upper case.

    Here is some sample code, the interesting part starting with #"Convert record to text".

    Hope this helps

     

    let
        Source = Table.FromRows(
            Json.Document(
                Binary.Decompress(
                    Binary.FromText(
                        "i45WSszJUcjJL08tUtJBYRsaGSvF6kQrOfr4KIQGBLgGAcWQ2QaG+kBkZGBkAlbmm1mRmqLgnFicCpRD4YQEhbqClSCbjmwS0CY9E1MMy1AMSUxKxrQGxelQBchiWE1AtgKbASiaUBSDFMQCAA==",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ((type nullable text) meta [Serialized.Text = true])
            in
                type table [#"Column 1" = _t, #"Column 2" = _t, #"Other types" = _t]
        ),
        #"Convert record to text" = Table.AddColumn(Source, "Is All Upper", each Text.Combine(Record.ToList(_))),
        #"Test for uppercase" = Table.TransformColumns(
            #"Convert record to text", {{"Is All Upper", each if _ = Text.Upper((_)) then true else false}}
        ),
        #"Changed Type" = Table.TransformColumnTypes(#"Test for uppercase", {{"Is All Upper", type logical}})
    in
        #"Changed Type"
  • johnlhaase's avatar
    johnlhaase
    Helper I
    2 years ago

    Hello here is the solution that works for my sitution. You can subsitute {0..9} or lower upper case in the {..}

    = Table.AddColumn(#"Renamed Columns1", "New", each Text.Select([Name], {"a".."z"}))

     

    Thanks