Forum Discussion

cymorg's avatar
cymorg
Icon for Resolver I rankResolver I
7 years ago
Solved

Conditional fill transformation based on preceding value

How would I transform column 1 such that blank values are replaced with the value from the preceding row, so rows 266 & 267 would contain "Use Of Fire Evacuation Chai", row 270 "2001 Manual Handling ...
Need Help
  • Vvelarde's avatar
    Vvelarde
    7 years ago

    cymorg 

     

     

    Ok, Please follow this:

     

    1. Select your column

    2. Go to Tranform - Replace Values 

     

    Value to Find    --- Empty

    Replace with ---- null

     

     

    3. Transform ---Fill Down

     

     

    Ready

     

    Regards

     

    Victor

     

     

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
7 years ago

cymorg 

 

Hi, In Query Editor with Fill Down Option.

 

Regards

 

Victor

cymorg's avatar
cymorg
Icon for Resolver I rankResolver I
7 years ago

In this scenario "In Query Editor with Fill Down Option" does nothing.

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    7 years ago

    cymorg 

     

     

    Ok, Please follow this:

     

    1. Select your column

    2. Go to Tranform - Replace Values 

     

    Value to Find    --- Empty

    Replace with ---- null

     

     

    3. Transform ---Fill Down

     

     

    Ready

     

    Regards

     

    Victor

     

     

    • cymorg's avatar
      cymorg
      Icon for Resolver I rankResolver I
      7 years ago

      Vvelarde Victor - So, Transform --- Fill Down creates a Table.FillDown M Query function call.  This function acts on the specified  column's null values, ignoring non-null values.  Therefore, converting empty strings to null before calling this function results in the desired outcome.

      It's perfectly clear now thanks to you. 10/10 Thanks.:robothappy: