%3CLINGO-SUB%20id%3D%22lingo-sub-1669409%22%20slang%3D%22en-US%22%3EMapping%20data%20flow%20derived%20column%20and%20expression%20builder%20updates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1669409%22%20slang%3D%22en-US%22%3E%3CP%3ESince%20mapping%20data%20flows%20became%20generally%20available%20in%202019%2C%20the%20Azure%20Data%20Factory%20team%20has%20been%20closely%20working%20with%20customers%20and%20monitoring%20various%20development%20pain%20points.%20To%20address%20these%20pain%20points%20and%20make%20our%20user%20experience%26nbsp%3Bextensible%20for%20new%20features%20coming%20in%20the%20future%2C%20we%20have%20made%20a%20few%20updates%20to%20the%20derived%20column%20panel%20and%20expression%20builder.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22create-derive-column.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218402i9CD8365B3FC2B8D3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22create-derive-column.png%22%20alt%3D%22create-derive-column.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20in%20a%20derived%20column%20transformation%2C%20you%20can%20directly%20enter%20your%20expression%20text%20into%20the%20textbox%20without%20needing%20to%20open%20up%20the%20expression%20builder.%20While%20we%20highly%20recommend%20using%20the%20expression%20builder%20for%20any%20advanced%20logical%20development%2C%20this%20is%20especially%20useful%20if%20you%20are%20just%20copy%2Fpasting%20simple%20code%20snippets%20or%20want%20to%20enter%20in%20something%20small.%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditordaperlov_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3EThe%20expression%20builder%20is%20still%20easily%20accessible%20from%20the%20top%20bar%20above%20the%20column%20list%20which%20will%20open%20to%20the%20first%20derived%20column%20or%20from%20the%20'Open%20expression%20builder'%20text%20below%20a%20column's%20expression.%20There%20are%20now%20more%20entry%20points%20for%20quickly%20adding%20columns%20or%20patterns%20from%20the%20derived%20column%20panel%20itself.%20You%20no%20longer%20need%20to%20rely%20on%20having%20existing%20columns%20to%20add%20columns%20or%20open%20the%20expression%20builder.%20These%20changes%20also%20apply%20when%20building%20column%20patterns%20or%20creating%20aggregate%20or%20window%20columns.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22add-derived-column.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218386iE6060CFB2554D817%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22add-derived-column.png%22%20alt%3D%22add-derived-column.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20entering%20the%20expression%20builder%2C%20you%20will%20notice%20that%20all%20expression%20elements%20now%20live%20below%20the%20expression.%20All%20IntelliSense%20features%20are%20still%20available%20in%20the%20expression%20editor%20and%20all%20functions%20are%20still%20available.%20Now%20on%20the%20left-hand%20side%20when%20building%20derived%20columns%2C%20it%20is%20a%20lot%20easier%20to%20navigate%20between%2C%20edit%20and%20create%20new%20derived%20columns.%20You%20are%20also%20able%20to%20edit%20your%20column%20names%20within%20the%20expression%20builder%20itself.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22expresion-builder.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218392i6B20CA8C55CE38D6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22expresion-builder.png%22%20alt%3D%22expresion-builder.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EComing%20out%20with%20these%20changes%20are%20a%20new%20feature%20of%20derived%20columns%20called%20Locals.%20%3CSPAN%3EA%20local%20is%20a%20set%20of%20logic%20that%20doesn't%20get%20propagated%20downstream%20to%20the%20following%20transformation.%20These%20are%20especially%20useful%20when%26nbsp%3Bsharing%20logic%20across%20multiple%20columns%20or%20wanting%20to%20compartmentalize%20your%20logic.%20Locals%20are%20created%20within%20the%20expression%20builder%20and%20referenced%20with%20a%20colon%20in%20front%20of%20there%20name.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22using-locals.png%22%20style%3D%22width%3A%20773px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218393i54F7676CC7C1388C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22using-locals.png%22%20alt%3D%22using-locals.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EThe%20column%20pattern%20experience%20within%20the%20new%20expression%20builder%20is%20also%20much%20improved.%20For%20the%20first%20time%2C%20you%20can%20navigate%20between%20your%20matching%20and%20pattern%20conditions%20within%20the%20expression%20builder.%20The%20development%20experience%20is%20also%20tailored%20towards%20creating%20patterns%20and%20understanding%20how%20to%20work%20with%20patterns.%20These%20changes%20also%20apply%20to%20rule-based%20mapping%20in%20selects%20and%20sinks.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22column-pattern-expression-builder.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218403iE90C7E1BEE7B6404%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22column-pattern-expression-builder.png%22%20alt%3D%22column-pattern-expression-builder.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EFor%20more%20information%20on%20all%20of%20these%20changes%2C%20check%20out%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-factory%2Fconcepts-data-flow-column-pattern%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ecolumn%20pattern%3C%2FA%3E%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-factory%2Fdata-flow-derived-column%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ederived%20column%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-factory%2Fconcepts-data-flow-expression-builder%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eexpression%20builder%3C%2FA%3E%20documentation.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F414555%22%20target%3D%22_blank%22%3E%40daperlov%3C%2FA%3E%26nbsp%3B%20and%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F175989%22%20target%3D%22_blank%22%3E%40Mark%20Kromer%3C%2FA%3E%26nbsp%3Bwill%20be%20hosting%20a%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DdBXF-8tGOD4%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EYouTube%20Live%20Stream%3C%2FA%3E%20at%2011%3A30%20AM%20PST%20on%20September%2015th%2C%202020%20to%20demo%20these%20changes.%20This%20stream%20will%20be%20uploaded%20to%20the%20Azure%20Data%20Factory%20YouTube%20channel%20afterwards.%20%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1669409%22%20slang%3D%22en-US%22%3E%3CP%3EUpdates%20are%20coming%20to%20the%20data%20flow%20expression%20building%20experience%20that%20will%20improve%20developer%20productivity%20and%20address%20common%20customer%20pain%20points!%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1669409%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Data%20Factory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMapping%20Data%20Flows%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1727218%22%20slang%3D%22en-US%22%3ERe%3A%20Mapping%20data%20flow%20derived%20column%20and%20expression%20builder%20updates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1727218%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20sure%20if%20this%20place%20is%20the%20correct%20to%20ask%20this%20if%20I'm%20wrong%20please%20let%20me%20know.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20Excel%20file%20that%20I'm%20processing%20using%20Mapping%20data%20flow...%20in%20this%20process%2C%20I%20remove%20Null%20values%20and%20some%20rows%2C%20and%20after%20this%2C%20my%20data%20have%20in%20the%20first%20row%20the%20names%20of%20the%20columns%20but%20in%20the%20sink%20activity%20my%20columns%20have%20names%20like%20_col1_%2C%20_col2_%2C%20etc...%20how%20I%20can%20set%20my%20first%20row%20as%20a%20header%20before%20all%20transformation%20process%3F%20I%20tried%20to%20use%20derived%20column%20and%20mapping%20columns%20but%20I%20need%20to%20do%20this%20process%20without%20set%20manually%20the%20names%20or%20by%20position.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20help%20%3A)%3C%2Fimg%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

Since mapping data flows became generally available in 2019, the Azure Data Factory team has been closely working with customers and monitoring various development pain points. To address these pain points and make our user experience extensible for new features coming in the future, we have made a few updates to the derived column panel and expression builder. 

 

create-derive-column.png

 

Now in a derived column transformation, you can directly enter your expression text into the textbox without needing to open up the expression builder. While we highly recommend using the expression builder for any advanced logical development, this is especially useful if you are just copy/pasting simple code snippets or want to enter in something small.

 

The expression builder is still easily accessible from the top bar above the column list which will open to the first derived column or from the 'Open expression builder' text below a column's expression. There are now more entry points for quickly adding columns or patterns from the derived column panel itself. You no longer need to rely on having existing columns to add columns or open the expression builder. These changes also apply when building column patterns or creating aggregate or window columns.

 

add-derived-column.png

 

When entering the expression builder, you will notice that all expression elements now live below the expression. All IntelliSense features are still available in the expression editor and all functions are still available. Now on the left-hand side when building derived columns, it is a lot easier to navigate between, edit and create new derived columns. You are also able to edit your column names within the expression builder itself.

 

expresion-builder.png

 

Coming out with these changes are a new feature of derived columns called Locals. A local is a set of logic that doesn't get propagated downstream to the following transformation. These are especially useful when sharing logic across multiple columns or wanting to compartmentalize your logic. Locals are created within the expression builder and referenced with a colon in front of there name. 

 

using-locals.png

 

The column pattern experience within the new expression builder is also much improved. For the first time, you can navigate between your matching and pattern conditions within the expression builder. The development experience is also tailored towards creating patterns and understanding how to work with patterns. These changes also apply to rule-based mapping in selects and sinks.

column-pattern-expression-builder.png

 

For more information on all of these changes, check out the column patternderived column and expression builder documentation.

 

@daperlov  and @Mark Kromer will be hosting a YouTube Live Stream at 11:30 AM PST on September 15th, 2020 to demo these changes. This stream will be uploaded to the Azure Data Factory YouTube channel afterwards.

1 Comment
Visitor

Hi,

 

I'm not sure if this place is the correct to ask this if I'm wrong please let me know.

 

I have an Excel file that I'm processing using Mapping data flow... in this process, I remove Null values and some rows, and after this, my data have in the first row the names of the columns but in the sink activity my columns have names like _col1_, _col2_, etc... how I can set my first row as a header before all transformation process? I tried to use derived column and mapping columns but I need to do this process without set manually the names or by position.

 

Thanks for help :)