Forum Discussion
Complex ADF transformation on Specific Rows to Columns
GemmaLowe I am guess you missed removing all the nulls or blanks from the columns during the transformation process.
You can configure the Flatten transformation to include only non-empty values from COL1 and COL2.
This will prevent unnecessary values in the flattened result.After flattening, add a Derived Column transformation and create new columns (NewCOL3, NewCOL4, NewCOL5, NewCOL6) referencing the flattened values from the original COL1 and COL2 columns.
You can use expressions like:
NewCOL3 = iif(FlattenedValues != '', COL1, '') NewCOL4 = iif(FlattenedValues != '', COL2, '') NewCOL5 = iif(FlattenedValues != '', LEAD(FlattenedValues, 1), '') NewCOL6 = iif(FlattenedValues != '', LEAD(FlattenedValues, 2), '')In your approach, you attempted to use the LOOKUP transformation, which is a valid step. However, instead of using the column names as values, you should reference the actual values from the first 4 rows (Manufacturing, 1-Jan-23, Baked Goods, Lemon Cookies) to create the new columns (NewCOL3, NewCOL4, NewCOL5, NewCOL6).
https://learn.microsoft.com/en-us/azure/data-factory/data-flow-stringify?WT.mc_id=%3Fwt.mc_id%3Dstudentamb_357518
Let me know if this works for you. @ me in replies, or I'll lose your thread!!!
Note:If this post is helpful, please mark it as the solution to help others find it easily. Also, if my answers contribute to a solution, show your appreciation by giving it a thumbs up!
GemmaLowe I am guess you missed removing all the nulls or blanks from the columns during the transformation process.
You can configure the Flatten transformation to include only non-empty values from COL1 and COL2.
This will prevent unnecessary values in the flattened result.
After flattening, add a Derived Column transformation and create new columns (NewCOL3, NewCOL4, NewCOL5, NewCOL6) referencing the flattened values from the original COL1 and COL2 columns.
You can use expressions like:
NewCOL3 = iif(FlattenedValues != '', COL1, '')
NewCOL4 = iif(FlattenedValues != '', COL2, '')
NewCOL5 = iif(FlattenedValues != '', LEAD(FlattenedValues, 1), '')
NewCOL6 = iif(FlattenedValues != '', LEAD(FlattenedValues, 2), '')In your approach, you attempted to use the LOOKUP transformation, which is a valid step. However, instead of using the column names as values, you should reference the actual values from the first 4 rows (Manufacturing, 1-Jan-23, Baked Goods, Lemon Cookies) to create the new columns (NewCOL3, NewCOL4, NewCOL5, NewCOL6).
https://learn.microsoft.com/en-us/azure/data-factory/data-flow-stringify?WT.mc_id=%3Fwt.mc_id%3Dstudentamb_357518
Note: