Datamesh - blanking mapped columns

In Datamesh, when the lookup value in the target sheet is blanked out (delete) or even changed to a value that has no match in the source sheet, all the mapped columns in the target sheet remain with the previous value which makes the data not consistent.

Is there a was to blank out the mapped value if the lookup value is blanked out or if this value has no match in the source sheet ?




  • Ella
    Ella ✭✭✭✭

    Datamesh is one-directional, the data moves from Source to Target sheet. Are you saying you want to delete something from the Target and have it be deleted from the Source sheet as well?

  • Hi Fabien,

    At the moment DataMesh will only move data in one direction (from the Source sheet to the Target sheet(s)). Deleting or removing a row / lookup value in either sheet will not cause the matching row to be removed in the other sheet. Please feel free to submit an Enhancement Request for this.

    As a workaround suggestion, you might consider setting up a process for archiving rows in both sheets using the Automated Move Row functionality.

    Hope that helps!


    Nathan L.

  • Hello,

    My point is not about deleting or removing a row in either de Source or Target sheet but making sure that the data is consistent in the target sheet. The purpose of Datamesh is to have consistency between the source and target sheet.

    Lets assume you have a source sheet with Employee info and that in the target sheet the lookup value is the employee_id and that you want to copy over some info from the source sheet (name, DOB etc..).

    The first time you'll run the data mesh everything will be good. If there is a match the data is copied over, and if there is no match the mapped cells will be blank.

    But let assume now that in the target sheet, you update the lookup value in an existing row with an other employee_id and that you do a typo (oh no !), then it doesn't match and the data will remain the same in that row creating unconsitent data. You end up with a valid employee name, DOB for a non valid employee_id.

    I see Datamesh as a lookup tool like VLOOKUP so it should tell you NOMATCH in the mapped columns if it is a nomatch and if the lookup value is blank, then the mapped column should also be empty everytime it runs.

    Thank you !


  • Ella
    Ella ✭✭✭✭

    Hi Fabien,

    When you run it it gives you a summary of the matched and the unmatched rows. There is no way that it can empty columns if it is not able to find a match in the Target Source.

  • Manuel Macias
    Manuel Macias ✭✭
    edited 02/12/20

    Hi Fabien,

    A work around that works fairly well though not always perfectly depending your setup is to create a placeholder row in the source sheet with no values in any cell except for the 'Look Up Value' cell being referenced by DataMesh. There you insert a dash (-) or any other symbol or combination of letters you choose.

    From there just make sure that the 'Look Up Value' cell in the TARGET sheet either has a real value such as an employee id in your case or a dash (-).

    Now, all you have to do is make sure to select YES to 'Overwrite Existing Data in Target Sheet(s)' in the DataMesh configuration window and run it. DataMesh will match the dash values in the Source and Target sheets and overwrite any inconsistent data in the cells back to blank.

    IMPORTANT: Make sure you never delete the placeholder row from the source sheet as this would make the entire setup useless.


  • Hi Manuel,

    Thank you. This is exactly what I did with a entry at the bottom of the dropdown !

    But unfortunately, even if the "Restrict to dropdown only" option is checked the user (Editor) can still blank the lookup value (by pressing delete) and it defeat the purpose. The option only prevents for entry not in the list but blank is allowed.


  • Kelly Drake
    Kelly Drake Overachievers Alumni

    If you run the data mesh on a schedule it will replace/correct the accidental deletions.

    As a best practice when I data mesh data from one sheet to another, I lock column so that no one except sheet Admins can create data errors like this.

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • I have this issue too. A lookup value is deleted in the target sheet after a datamesh has been done. I tried adding a blank cell in the lookup value in the source sheet with a corresponding zero in the data field (which would work for my situation - to removed a number and make it zero), but this didn't work. Any workaround since this was posted 2 yrs ago?