Power Query is a business intelligence tool available in Excel that allows you to import data from many different sources and then clean, transform and reshape your data as needed.
Power Query is case sensitive, sometime Text data have many variations of upper case and lower case characters and should be forced to lowercase or to uppercase to remove duplicates.
Text.lower or Text.upper operations are working only with Text data.
For data type ANY they will generate Expression.Error if the field contains numeric value for example.
The solution is to ignore case for needed field or ignore case in all columns in the Table.distinct.
For example, you have two fields Activity and Activity ID, like below :
Activity | Activity ID
Apple | 1CA11
Apple | 1ca11
Orange | 2dp23
Orange | 2DP23
The result should be like :
Activity | Activity ID
Apple | 1CA11
Orange | 2DP23
To ignore case of just the Activity ID field in Table.Distinct operations, you need to use :
= Table.Distinct(Source,{{“Activity”, Comparer.Ordinal}, {“Activity ID”, Comparer.OrdinalIgnoreCase}} )
To ignore case in all columns in the Table.Distinct :
= Table.Distinct(Source, Comparer.OrdinalIgnoreCase)
