We’re struggling to find the right way to do this. We are using EasyMorph to take different data sets and feed them into a standard schema.
It’s convenient to have a structure like what’s shown in the screenshot where the person just has to fill in the calculation, however if the column already exists in the input data then it creates a duplicate of the column (e.g. Quantity (2))
One possible solution would be if Calculate could have an option to replace the column if it exists.
Are there better ideas out there?
We also considering adding a unique prefix, then selecting only those columns with the prefix and then using the Rename Columns with Lookup transformation, but that’s alot of steps and more complicated than it should be.
The unique prefix idea actually isn’t as bad as I thought
The “Mapping” table can be set up and copy/pasted between projects and the person can just fill in the ~ prefixed columns and it gets renamed at the end… Slightly tedious to set up the first time, but can be reused fairly easily
What should happen if a column already exists in the source dataset, but another column is calculated with the same name – should the old values be ignored and overwritten? Can the expression for new column use old values, or it uses only other columns?
Another idea: I would try adding empty columns first using Append transformation with empty table (i.e. no rows, only columns Customer, Product, etc. ). In this case, if a column already exists then it stays unchanged, and if not – a new empty column is created. Appending an empty table is a way to ensure that columns with certain names exist.
Then use “Modify columns” transformation instead of “Calculate columns” in your opening post.
