Custom columns in DataFlow Gen2

[FabricGeek]

Hi,

If I want to concatenate columns and then remove a certain number of characters from right/left, do I need to use the "Custom Calculation" option and this creates a new column? 

Is there anyway to do the above on the existing column and can I use the no-code options to achieve this or does it require the M-Language expressions? Also, the expression builder is not very intuitive as the available functions are not available and have to be looked-up online.

I think there are certain -and simple - operations you can do with the no-code options e.g. uppercase the text, split at a certain point, convert data type, etc. but anything like what I am trying to do, requires the M Language.

I would appreciate your views on this. Thanks in advance.

[FabricGeek]

Thanks for the prompt response. Unfortunately, I don't get any examples for my columns.
I actually want to append a literal string e.g. X000A to an ID (existing field). I can see that I can add a column and then merge with existing column but was hoping that the X000A could be incorporated within an expression.

Similarly, I want to add 0000 to an existing number and then take 4 digits from the right. I can see how the latter would be done using the extract option but don't see where I can incorporate the concatenation of 0000.

Perhaps this is where I need to use the custom calculations?

Thanks for your support. 

[pqian_MSFT · Accepted solution]

1. Can you clarify what do you mean by "I don't get any examples for my columns" - do you mean you provided the examples but it didn't work?

2. Extending current string value with a suffix can be done by clicking Format -> Add Suffix

Doing "Add Suffix" to a number column will actually convert it to text first, and then do the literal append of "0000" - at that point your number loses their numerical meaning and are simply textual. Perhaps this is what you wanted to do, perhaps there are numerical ways of achieving this.

Adding column from example does not change existing columns in place. However, you can do the same by adding the column first, then dropping the old column(s). 

[pqian_MSFT]

Concat two columns:

Select the two columns and click Transform -> Merge Columns

To cleanse the column, use Transform-> Extract

Finally, something like this is perfect candidate for Column from Example:

Give it a try - type out a few examples of what you want the output to be, and the UI should figure out how to do this effectively.