Identifying All Uppercase letters in a column

[johnlhaase]

Hello

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

Thanks

John Haase

[johnlhaase]

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

[collinsg]

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"