I am transforming data from blob storage to another. While doing it I have to perform some transformation. 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

CodePudding user response：

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:

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.

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: