replace special character PU2 from a file in dataflow

Question

replace special character PU2 from a file in dataflow

Peruka, Sikander reddy 155

Mar 31, 2025, 2:28 PM

I have source file in dataflow and few rows in the file that have PU2 special character.

User's image

How do i replace this using the derived transformation in dataflow?

Venkat Reddy Navari 1,190 Reputation points Microsoft External Staff

Mar 31, 2025, 3:24 PM
Hi @Peruka, Sikander reddy In Azure Data Factory (ADF) or Azure Synapse Data Flow, you can use Derived Column Transformation to replace the PU2 special character.

Follow the Below Steps to Replace "PU2" in Data Flow

Add a Derived Column Transformation in your Data Flow.

Choose the column that contains the text with "PU2" (for this example, let’s assume it's a column called Address).

Use the replace function to remove "PU2" from the column's text.

This expression will replace "PU2" with an empty string (''), effectively removing it.

Example:

Input data in the column Address:
514 America PU2 way PMB22094

Derived Column expression:
replace (Address, 'PU2', ''),
This expression will replace "PU2" with an empty string (''), effectively removing it.

Output data after applying the transformation:
514 America way PMB22094

This will clean up the text by removing the unwanted "PU2" wherever it appears in the Address column.

Make sure that the column name (like Address in this example) matches the actual column name in your dataset.

If PU2 appears at the beginning or end of the string, it will be removed from there as well.

If the "PU2" is part of a Unicode character (non-printable), you may need to handle it accordingly, but for most cases, the above method should work.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Rakesh Govindula 965 Reputation points Microsoft External Staff

Apr 1, 2025, 2:25 PM

@Peruka, Sikander reddy,

What is your file type here? CSV file? And can you provide a sample of your file data along with the structure in text format so that we can reproduce the issue from our end?
Peruka, Sikander reddy 155 Reputation points

Apr 1, 2025, 2:38 PM

sample_file.txt

PFA sample file with the special character.
Peruka, Sikander reddy 155 Reputation points

Apr 1, 2025, 2:39 PM

I have opened the file in notepad++ and it shows the special character.
Peruka, Sikander reddy 155 Reputation points

Apr 1, 2025, 2:58 PM

sample_file.txt

PFA sample file and open the file in notepad++ to see the special character.

The earlier provided solution isn't working.

Accepted answer

0 additional answers

Your answer

Venkat Reddy Navari 1,190 Reputation points Microsoft External Staff

Mar 31, 2025, 3:24 PM

Hi @Peruka, Sikander reddy In Azure Data Factory (ADF) or Azure Synapse Data Flow, you can use Derived Column Transformation to replace the PU2 special character.

Follow the Below Steps to Replace "PU2" in Data Flow

Add a Derived Column Transformation in your Data Flow.

Choose the column that contains the text with "PU2" (for this example, let’s assume it's a column called Address).

Use the replace function to remove "PU2" from the column's text.

This expression will replace "PU2" with an empty string (''), effectively removing it.

Example:

Input data in the column Address:
514 America PU2 way PMB22094

Derived Column expression:
replace (Address, 'PU2', ''),
This expression will replace "PU2" with an empty string (''), effectively removing it.

Output data after applying the transformation:
514 America way PMB22094

This will clean up the text by removing the unwanted "PU2" wherever it appears in the Address column.

Make sure that the column name (like Address in this example) matches the actual column name in your dataset.

If PU2 appears at the beginning or end of the string, it will be removed from there as well.

If the "PU2" is part of a Unicode character (non-printable), you may need to handle it accordingly, but for most cases, the above method should work.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Rakesh Govindula 965 Reputation points Microsoft External Staff

Apr 1, 2025, 2:25 PM

@Peruka, Sikander reddy,

What is your file type here? CSV file? And can you provide a sample of your file data along with the structure in text format so that we can reproduce the issue from our end?
Peruka, Sikander reddy 155 Reputation points

Apr 1, 2025, 2:38 PM

sample_file.txt

PFA sample file with the special character.
Peruka, Sikander reddy 155 Reputation points

Apr 1, 2025, 2:39 PM

I have opened the file in notepad++ and it shows the special character.
Peruka, Sikander reddy 155 Reputation points

Apr 1, 2025, 2:58 PM

sample_file.txt

PFA sample file and open the file in notepad++ to see the special character.

The earlier provided solution isn't working.

Answer 1

Rakesh Govindula 965 Microsoft External Staff

Apr 1, 2025, 3:33 PM

Hello @Peruka, Sikander reddy,

You can try the below expression in the derived column transformation. You can give the expression to a new column or the same column as per your requirement. You need to ensure the source delimited text dataset encoding is UTF-8 and if you are copying it to any CSV or text file, the encoding should be the same.


replace(Address,'\u0092','')

enter image description here

If the solution worked for you, please click on accept answer and YES button, this can be beneficial to other community members.

User's image

Peruka, Sikander reddy 155 Reputation points

Apr 1, 2025, 6:32 PM

Thanks this solution worked.

replace(Address,'\u0092','')
Rakesh Govindula 965 Reputation points Microsoft External Staff

Apr 3, 2025, 2:41 AM

Hello @Peruka, Sikander reddy,

if the solution worked for you, please click on accept answer and YES button, this can be beneficial to other community members.

Share via

replace special character PU2 from a file in dataflow

0 additional answers

Your answer