#1
|
|||
|
|||
How to merge 2 Excel tables?
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 |
#2
|
||||
|
||||
Quote:
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" |
#3
|
|||
|
|||
Thank you!
|
Tags |
merge tables |
Thread Tools | |
Display Modes | |
|
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 |