How to merge 2 Excel tables?

lynn0712 · #1 02-14-2020, 04:21 PM

I have 2 tables on one Excel workbook: _CustomerList and _SalesList. I created a one to many relationship between them based on a column called, Customer. How can I merge the 2 tables, with the end result being a worksheet that has columns from both tables. Examples for the tables are in the attached spreadsheet.

Example Tables.xlsx

p45cal · #2 02-15-2020, 04:23 PM

Quote:

Originally Posted by lynn0712

I created a one to many relationship between them based on a column called, Customer.

How did you do this?

You have Power Query/Get & Transform in your version of Excel?

In the attached, two queries to put the tables into queries & connections, and one to merge. M Code for the merge could be:

Code:

let
    Source = Table.NestedJoin(Table1, {"Customer"}, Table2, {"Customer"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Description", "Amount"}, {"Description", "Amount"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table2",{{"Amount", Currency.Type}})
in
     #"Changed Type"

I've put the resultant table to the right of your desired result table. If the data changes in either of the source tables you just need to refresh the green table with a right-click and choose Refresh.

lynn0712 · #3 02-16-2020, 02:42 PM

Thank you!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Nesting Tables in a Merge Document	TeresaRyanKS	Mail Merge	2	02-04-2020 12:09 PM
Populating Tables with data when available only - Using Mail merge	stevejohhnym	Mail Merge	1	06-25-2019 12:50 AM
Mail Merge does not split tables if entry has a number in front	B-Man	Mail Merge	5	05-19-2019 07:25 AM
How to Merge Two tables in excel	MShahid777	Excel	1	09-02-2018 02:43 PM
mail merge from access query or two tables	peter	Mail Merge	0	08-24-2010 05:32 PM