Derived Column expression failing

Question

Hi Microsoft Q&A,

I am trying to follow the instructions in the data flow delta lake and I am having issue with the expression builder as shown in the below documentation.

https://learn.microsoft.com/en-us/azure/data-factory/tutorial-data-flow-delta-lake

Thankfully,

Gunasekar Desaiyan

Hyderabad

Answer 1

The issue could be related to the syntax or the actual values in your dataset. Let me guide you through the process to troubleshoot and fix the expression in the derived column:

Expression Overview:

• The expression used is: iif(equals(Year, 1988), 1, Rating).
  • This expression sets NewRating to 1 if the Year column equals 1988, otherwise, it assigns the value from the Rating column.
  Common Problems:

  - **Data Type Mismatch:** The `Year` column might be stored as a string rather than a number. In that case, `equals(Year, 1988)` would fail.
  
     - **Column Name Case Sensitivity:** Ensure the column names `Year` and `Rating` are spelled correctly and match the exact case in your dataset.
  
        - **Null or Missing Values:** If the `Year` or `Rating` column contains `NULL` values, the expression may fail.
  
        **Troubleshooting Steps:**
  
           - **Check Data Types:** Use the data preview feature in Azure Data Factory to inspect the types of `Year` and `Rating`. If `Year` is a string, modify the expression to: `iif(equals(toInteger(Year), 1988), 1, Rating)`.
  
              - **Verify Column Names:** Double-check that `Year` and `Rating` are the exact column names in your dataset.
  
                 - **Handle Nulls:** Add a null check to prevent the expression from failing due to null values. For example: `iif(isNull(Year) || isNull(Rating), null, iif(equals(toInteger(Year), 1988), 1, Rating))`.
  
                 **Testing the Expression:**
  
                    - Open the expression builder in the Derived Column transformation.
  
                       - Use the updated expression: `iif(equals(toInteger(Year), 1988), 1, Rating)` (adjusted for data type conversion).
  
                          - Validate the expression to ensure it parses correctly.
  
                          **Run a Preview:**
  
                             - Once the expression is updated, use the **Data Preview** tab in the Derived Column settings to verify that the transformation works as expected.
  

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.