'How to replace multiple text in the column using Azure dataflow?

I have a column called "Location" in my data. I have 100000 records.

enter image description here

I am cleaning the location using the below code in the dataflow using derived column expression and naming the new column called "Location_new"

case(Location == 'Otago/Southland', "Otago",
    Location == 'Nelson - Marlborough', "Nelson",
    Location == 'National', "Other")

The code is working, however, it's not replacing and getting null for my entire new column

enter image description here

Please note, the above code is an example, I am replacing close to 250 names as my data is messy.

Can anyone advise how to overcome this? Is there any better way available for cleaning the data?

azureazure-data-factoryazure-data-flow


Solution 1:[1]

I have reproed in with the sample. Your case statement looks fine and you are getting the NULL values in the new column might because your incoming data does not satisfy any of the case statements.

As Nick.McDermaid mentioned in the comments sections, you need to provide the else value in the case statement to get the incoming values as is when none of the conditions in the case statement works.

Case statement without else:

enter image description here

Case statement with else:

case(Location == 'Otago/Southland', "Otago",
Location == 'Nelson - Marlborough', "Nelson",
Location == 'National', "Other",
Location)

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions