Forum Discussion

GemmaLowe's avatar
GemmaLowe
Copper Contributor
Mar 01, 2024

Complex ADF transformation on Specific Rows to Columns

Hello Experts,

 

I have a transformation that I have tried a few dataflow scenarios that do not yield the results needed. We have a file that extracts data like the below sample:

 

COL1COL2
Manufacturing 
1-Jan-23 
Baked Goods 
Lemon Cookies 
  
Raw Materials40470
Factory Overheads60705
Staff Costs91057.5
Electricity136586.25

 

I would like the output table to look like the below:

COL1COL2NewCOL3NewCOL4NewCOL5NewCOL6
Raw Materials40470Manufacturing1-Jan-23Baked GoodsLemon Cookies
Factory Overheads60705Manufacturing2-Jan-23Baked GoodsLemon Cookies
Staff Costs91057.5Manufacturing3-Jan-23Baked GoodsLemon Cookies
Electricity136586.25Manufacturing4-Jan-23Baked GoodsLemon Cookies

 

The transformation should take the values of the first 4 rows as new column values and remove any nulls or whitespaces. I have used UNPIVOT and LOOKUP transformations but they return the column name as the value and not the values in rows 1-4, so I know I am missing a step in the process. Any suggestions on the dataflow for this challenge?

  • BabatundeDallas's avatar
    BabatundeDallas
    Mar 07, 2024

    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).

     

    You can read more at Microsoft Learn

     

    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! 
  • BabatundeDallas's avatar
    BabatundeDallas
    Brass Contributor
    Mar 07, 2024

    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).

     

    You can read more at Microsoft Learn

     

    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! 

Resources