Thread: [Solved] How to merge 2 Excel tables?
View Single Post
 
Old 02-15-2020, 04:23 PM
p45cal's Avatar
p45cal p45cal is offline Windows 10 Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by lynn0712 View Post
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.
Attached Files
File Type: xlsx msofficeforums44409Example Tables.xlsx (19.9 KB, 8 views)
Reply With Quote