I want to merge a table into another one, however the table I want to merge may have a different number of columns with different names every time the task is launched. For this reason, I would like to merge all columns from this table except those listed in the “Where all these columns match list” (if this last exception is not possible, that’s not an issue, they can be deleted afterwards). The columns listed in “Where all these columns match list” will always be named the same way in both tables, in every task. Is it possible to obtain this behavior in EasyMorph?
Here’s an example of what I’m trying to do: I have two tables with product information for three products, in one table I have information about “Height” and in the other table, “Type”.
If I know that the columns in the second table are always going to be “Product” + “Type”, then I can do a “Merge another table” action:
However, what if I don’t know what information of the product I get in the second table I want to merge beforehand? The “Product” column will still be there, but the other column could be “Price” for example. In this case, I’ve been able to do same “merge” action, but by appending both tables, then unpivoting, removing blanks, and pivoting:
The problem I have with this solution is the number of rows in the unpivoted table. In this case, the unpivoted table has 12 rows, which is not a problem at all, but if we have tens thousands of products (rows) and hundreds of columns in the first place, then the number of rows when unpivoting can increase to millions, tens of millions and potentially hundreds of millions of rows. In order to avoid performance issues in our local machine when doing this unpivot, I was wondering if there was another way to do a merge of all columns except “Product” (which will always be present in both tables), independently of the name of these columns (because they could change from “Type” to “Price” to other headers). This way I could merge the columns in one step without having to do the unpivot/pivot actions.
I attach the sample project + 3 excel tables that go with it. The two methods can be seen in two different groups in the EasyMorph project.
If both tables have the same number of rows and they are in the same order (as in your example), then you can simply use the “Append” action in the “Append columns” mode, i.e. appending columns side by side.
Thanks for the idea! Actually in real life I will have different number of rows/products, but with some actions I can obtain that same row number/order in each table, so I will do that and then append columns.
Basically, instead of selecting which columns to obtain, select which columns to not obtain. In the following example, in the highlighted table, instead of selecting “Type” and “Range”, we would select the columns we don’t want to obtain, such as “Product”. So if in the future we get a table with the columns “Product”, “Width”, and “Power”, “Width” and “Power” will be included in the merge, even though I have not selected them explicitly.
How about that (see below)? Notice that the merge mode in this case must be “Left join”. The example only merges all columns without specifying them explicitly. It doesn’t remove columns, but that’s the easiest part.
