ADF expression - Sample & examples

ForEach loop,

@Item()

GetMetaData

@Item().Name

FileFormat -> yyMMdd_HHmmss

FormatDateTime:

@formatDateTime(utcnow(),'yyyy-MM-dd HH:mm:ss')

Date to integer to string

concat(Column_2,'_',toString(toDate(Column_3, 'MM/dd/yyyy','en-US'), 'yyyyMMdd')) 

Str upper ->  upper('test')

concat ->  concat('test-1','_','test-2')

Data Flow: SinkDataset ( enable -> optimize ->  Single partition)

If expression (Data flow)

iif(GROUPNAME == 'WG', 'Y', 'N')

Compare -> 

Ex

 iif((compare(TABLENAME,"VENDTABLE")>0),iif(isNull(sendVendorId), concat(ACCOUNTNUM,'_',PARTY,'_',PARTNERDATAAREAID),ACCOUNTNUM ),concat(ACCOUNTNUM,'_',PARTY,'_',PARTNERDATAAREAID))

iif((compare(TABLENAME,"VENDTABLE")>0),"SUPPLIER","CUSTOMER")

iif((compare(TABLENAME,"PurchLine")>0),"0",toString(round(toDecimal(EXTENDEDPRICE),2)))

iif((compare(TABLENAME,"PurchLine")>0),"SO","PO")

Agreegate: first('fieldname')

Filter On: TABLENAME == "VendTable"

Address street (3 fields)

Derived column1:

Address1-> split(STREET, "\n")

Derived column2:

Address1 ->

replace(iif(length(STREET) > 100, substring(STREET,1, 100), Address1[1]),'\n',' ')

Address2 ->replace(iif(length(STREET) > 100, substring(STREET,101, 100), iif(size(Address1) > 1,Address1[2], '')),'\n',' ')

Address3 ->replace(iif(length(STREET) > 200, substring(STREET,201, 100), iif(size(Address1 ) > 2,Address1 [3], '')),'\n',' ')

substring: (replace .csv to .zip)

@concat(

    substring(

        dataset().p_fileName,

        0,

        sub(

            length(dataset().p_fileName)

            ,4)

    ),'.zip'

)

Replace -> replace(toString(DATE,'YYYY-MM-DD'),'00:00:00','')

Dataset:

UTF-8 (format by D365FO DMF)

Row delimiter -> edit (yes) -> \r\n

First row as header -> Yes

Quote character -> No Quote char (If no need of quote character)

Bearer token (Web activity)

"typeProperties": {

        "url": {

            "value": "@pipeline().globalParameters.gp_oauth2tokenURI",

            "type": "Expression"

        },

        "method": "POST",

        "headers": {

            "Content-Type": "application/x-www-form-urlencoded"

        },

        "body": {

            "value": "@concat(\n\t'tenant_id=',activity('Get tenant-id from KeyVault').output.value,\n\t'&client_id=',activity('Get client-id from KeyVault').output.value,\n\t'&client_secret=',activity('Get client-secret from KeyVault').output.value,\n\t'&grant_type=client_credentials&resource=',pipeline().globalParameters.gp_oauth2tokenURIresource\n)",

            "type": "Expression"

        }

    }

Get Activity response:

@activity('GetToken').output.access_token

WritableURI

@uriComponentToString(replace(last(split(uriComponent(activity('GetAzureWritableUrl').output.value),'BlobUrl%22%3A%22')),'%22%7D',''))