Search code examples
powerqueryalphanumeric

PowerQuery: Force Lowercase for an alphanumeric Text

How are you able to force an alphanumeric string to lowercase (or uppercase) in powerQuery?

I have a series of attribute codes coming into powerQuery , but the codes contain variations of upper case and lower case text. In practice these items would be considered duplicates, but PowerQuery is case sensitive. I've tried using Text.lower / Text.upper but this requires the data to be type text. My data is alphanumeric (123abc, 111, aaa) and text functions do not work for data type any

Suggestions? description below:

' Activity       Activity ID'

  Apple          1CA11
  Apple          1ca11
  Orange         2dp23
  Orange         2DP23

'This should become: 
  Apple          1ca11
  Orange         2dp23

Picture below: Error Example using Text.lower

Removing Duplicates

Solution

  • You could ignore case of just the Activity ID field in Table.Distinct operations

    = Table.Distinct(Source,{{"Activity", Comparer.Ordinal}, {"Activity ID", Comparer.OrdinalIgnoreCase}} )

    or ignore case in all columns in the Table.Distinct

    = Table.Distinct(Source, Comparer.OrdinalIgnoreCase)