1

I need an expression in the expression builder to transform the customer Name as below Take first character of word in the name followed by * . Customer name may contain 1 or more words Name can be Tim or Tim John or Tim John Zac or Tim John Mike Zac

Jen
  • 87
  • 7
  • 'Take first character of word in the name followed by *', can you provide a sample expected result. you want the rows of the column Customer Name which starts with a word right? If thats the case you can use `startswith()` to filter the required records. – Rakesh Govindula Jan 20 '23 at 00:07
  • @RakeshGovindula Eg: Tim as T***** and Tim John as T*****J***** and Tim John Zac as T*****J*****Z***** – Jen Jan 20 '23 at 00:38
  • If you want to do it dynamically, how do you know that start letter of every word which we need to give in the condition? – Rakesh Govindula Jan 20 '23 at 00:42
  • @RakeshGovindula Is there any expression to take the first character of every word and replace the other characters with '*' – Jen Jan 20 '23 at 00:48

1 Answers1

1

I have reproduced above and got below results using derived column.

I have used the same data that you have given in a single column and used the below dataflow expression in derived column.

dropLeft(toString(reduce(map(split(Name, ' '),regexReplace(#item, concat('[^',left(#item,1),']'), '*')), '', #acc +  '  '  + #item, #result)), 2)

Here, some general regular expressions were given errors for me in dataflow, that's why used the above approach.

First, I have used split() by space to get an array of strings. Then used regular expression on every item of array like above.

As we do not have join in dataflow expression, I have used the code from this SO answer by @Jarred Jobe to convert array to a string seperated by spaces.

Result:

enter image description here

NOTE:

Make sure you give two spaces in toString() of above code to get the required result. If we give only one space it will give the results like below.

enter image description here

Update:

Thank you so much for sharing this. I have tried your solution but I got few names wrong .Also I want to replace the rest of the characters with just 5 '' irrespective of how many characters the name has. Also name : Mia hellah came as M* h****h instead of M***** h*****. Another one SAM & JOHN TIBEH should be S***** &***** J***** T*****. I tried to update your expression but I couldn't get it right.

If you want to do like above, you can directly use concat function dataflow expression.

dropLeft(toString(reduce(map(split(Name, ' '),concat(left(#item,1), '*****')), '', #acc +  '  '  + #item, #result)), 2)

Results:

enter image description here

Rakesh Govindula
  • 5,257
  • 1
  • 2
  • 11
  • Thank you so much for sharing this. I have tried your solution but I got few names wrong .Also I want to replace the rest of the characters with just 5 '*' irrespective of how many characters the name has. Also name : Mia hellah came as M** h****h instead of M***** h*****. Another one SAM & JOHN TIBEH should be S***** &***** J***** T*****. I tried to update your expression but I couldn't get it right. – Jen Jan 21 '23 at 11:44
  • @Jen, I have updated my answer as per your requirement, please check that. – Rakesh Govindula Jan 21 '23 at 12:18
  • 1
    Wow it worked!! Thank you so much for your answer. Highly appreciate your support :) – Jen Jan 21 '23 at 12:46
  • Just a query whether adf pipeline copy activity allow these expressions ? I mean whether I can achieve the same while copy the data from one Azure Table storage to another – Jen Jan 21 '23 at 12:58
  • 1
    ADF pipeline Copy activity is not designed to perform the activities like above which involves rows and column level operations. its better use dataflows for this. Refer this [SO link](https://stackoverflow.com/questions/62015027/difference-between-dataflow-and-pipelines) to understand more. – Rakesh Govindula Jan 21 '23 at 14:48