CODEWITHSUNDEEP
powerbipowerquery
Rasec Malkic
Rasec Malkic

Reputation: 659

How to change to null rows containing lowercase letters

I have the query code below, for which I want to replace rows when begins with FOLIC and replace with null the rows that have lowercase (not all are uppercase)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvMMcfT19FNwVNBwcg1x1HV2DPIPcfVz1VSK1YlWKsssSczNzFNwBvNQhFzBPF9Hdz/XYM9QXzCvKjMvGaESTAR4+AcDcZB/aDBC0M3fx9NZwdHZ00UpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FRUITS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FRUITS", type text}}),
    Custom1 = Table.AddColumn(#"Changed Type", "WITHOUT LOWERCASE", 
                            each 
                                if Text.Start(Text.From([FRUITS]),5)="FOLIC" then "NOT VITAMIN"
                                else if [FRUITS] = Text.Lower([FRUITS]) then null
                                else [FRUITS]
                            )
in
    Custom1

This is how it looks the added column and the expected output I'm looking for. Is almost working but in the rows that have upper and lower case letters (vitamin C, vitamin E) is not converting to null.

enter image description here

Upvotes: 0

Views: 64

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Try it the opposite way.

[...]
else if [FRUITS] <> Text.Upper([FRUITS]) then null
[...]

Or like this:

if Text.Start(Text.From([FRUITS]),5)="FOLIC" then "NOT VITAMIN"
else if [FRUITS] = Text.Upper([FRUITS]) then [FRUITS]
else null

Upvotes: 2

Related Questions