undefined

[Hemendra3]

I have a column say division # 61, 62, 92 and wanted to create a new column as [Rev. Description]

if [Div]= 62 and column [Description ] having text say "Abs" then convert as "Absolute" and "Doc"  as "Doctor"

if [Div]= 22 and column [Description ] having text say "Mon" then convert as "Monday" 

else [Description ] 

I wrote MQuery with contains (ignore case sensitive by "Comparer.OrdinalIgnoreCase") but my file stopped loading. when I deleted this step, it was back on track. how to mitigate this?

my exact query was as under :

= Table.AddColumn(
#"Prod Category",
"Product Category",
each
if [Division] = 62 and Text.Contains([Product Description], "petronas", Comparer.OrdinalIgnoreCase) then "Engine Oil"
else if [Division] = 62 and Text.Contains([Product Description], "Reniso", Comparer.OrdinalIgnoreCase) then "Reniso"
else if [Division] = 92 and Text.Contains([Product Description], "Compressor", Comparer.OrdinalIgnoreCase) then "RF Compressor"
else [Product Description],
type text
) 

Thanks in avdance.

[v-aatheeque]

Hi @Hemendra3 

Following up to see if the solution shared by @Shahid12523 @bhanu_gautam @dufoq3 helped with the Text.Contains logic in your M Query. Were you able to load the file successfully after applying the updated step to create the  column?

[v-aatheeque]

Hi @Hemendra3 

Just following up to check if the solution shared by @Shahid12523, @bhanu_gautam, and @dufoq3 helped with the Text.Contains logic in your M Query.

Were you able to load the file successfully after applying the updated step to create the column?

If we don’t hear back, we may close this thread in line with our community guidelines, but you’re always welcome to post a new query anytime.

Thank you for being part of the Microsoft Fabric Community!

[Shahid12523]

Your query hangs because Comparer.OrdinalIgnoreCase breaks query folding → Power Query scans all rows locally.

How to Fixes:

Filter [Division] first (reduce rows).

Use Text.Lower([Product Description]) + search lowercase terms (faster than Comparer.OrdinalIgnoreCase).

Best: move the logic into SQL (CASE WHEN).

Recommended: replace Comparer.OrdinalIgnoreCase with Text.Lower().

[dufoq3 · Accepted solution]

Hi @Hemendra3, another solution.

Add mnore replacements as list of lists in Replacements step.

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjNU0lFyTCpWKM7PTVUoSa0oUfDNz1OK1QFKGQGlghNLk1KLFApSS4ry8xKLwRKWIIn8kgygOFAtWJNSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Division = _t, Description = _t]),
    Replacements = List.Buffer(
  { {"Abs", "Absolute"},
    {"Mon", "Monday"},
    {"Petronas", "Engine Oil"} }),
    ReplacedDescription = Table.TransformColumns(Source,{{"Description", each Text.Combine(List.ReplaceMatchingItems(Text.Split(_, " "), Replacements, Comparer.OrdinalIgnoreCase), " "), type text}})
in
    ReplacedDescription

[bhanu_gautam]

@Hemendra3 , Try using

let
Source = #"Prod Category",
AddRevDescription = Table.AddColumn(
Source,
"Rev. Description",
each if [Division] = 62 and Text.Contains([Description], "Abs", Comparer.OrdinalIgnoreCase) then "Absolute"
else if [Division] = 62 and Text.Contains([Description], "Doc", Comparer.OrdinalIgnoreCase) then "Doctor"
else if [Division] = 22 and Text.Contains([Description], "Mon", Comparer.OrdinalIgnoreCase) then "Monday"
else [Description],
type text
)
in
AddRevDescription

[v-aatheeque]

Hi @Hemendra3 

Just checking in to see if the solution shared by the @bhanu_gautam helped resolve your issue with the Text contians logic in M Query. Were you able to load your file successfully with the updated step for creating the [Rev. Description] column?