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.