About EasyMorph Tutorials & Examples Web-help

"Merge another table" action - merge all columns (independently of the column name)

Hello,

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?

Thanks,

Roberto

Hello,

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.

Ways to merge.morph (6.3 KB)
Table1.xlsx (8.5 KB) Table2.xlsx (8.5 KB) Table3.xlsx (8.5 KB)

Thanks very much,

Roberto

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.

Hello,

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.

Regards,

Roberto

Hello again,

If I don’t have the same number of rows, is there another solution that would be equivalent to “merge all columns”, independently of their name?

Thanks very much,

Roberto

Hi Roberto,

I’m not sure I understand how such a merge would work. Can you provide an example?

Hello Dmitry,

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.

Something similar happens with the “Unpivot” action - “Unpivot all columns except these” - here we would have “Merge all columns except these”.

Here’s the project that appears in the screenshot, and the 2 source tables.

Merge all columns except.morph (3.4 KB)
Table1.xlsx (8.7 KB) Table2.xlsx (8.7 KB)

Thanks,

Roberto

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.

Merge all columns.morph (4.1 KB)

1 Like
To learn more about EasyMorph visit easymorph.com.