Consolidate growing tables

[Leeds Knight]

I have two named Tables detailing price proposals from mfrs on different dates

I want to merge these into a single table detailing the minimum cost and associated detail per Item

Item	Mfr	MPN	Cost
10001	Acme	ACME001	1
10002	Bob's	BOB001	1.5
10003	Tom's	TOM001	2

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

Item	Mfr	MPN	Cost
10001	Bob's	BOB002	1.5
10003	Tom's	TOM001	0.5
10004	Bill's	BILL001	4

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

Requirement - single table by Item number, telling me who has provided the lowest quote with which MPN value:

Item	Mfr	MPN	Min Cost
10001	Acme	ACME001	1
10002	Bob's	BOB001	1.5
10003	Tom's	TOM001	0.5
10004	Bill's	BILL001	4

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>

 

[sandy666]

like this?

Item	Mfr	MPN	Min of Cost
10001​	Bob's	BOB002	1.5​
10001​	Acme	ACME001	1​
10002​	Bob's	BOB001	1.5​
10003​	Tom's	TOM001	0.5​
10004​	Bill's	BILL001	4​

with PowerQuery (Get&Tranaform)

[SIZE=1]let
    Source = Table.Combine({Table1, Table2}),
    Group = Table.Group(Source, {"Item", "Mfr", "MPN"}, {{"Count", each _, type table}}),
    Min = Table.AggregateTableColumn(Group, "Count", {{"Cost", List.Min, "Min of Cost"}})
in
    Min[/SIZE]

 

[sandy666]

or

Item	Mfr	MPN	Min of Cost
10001​	Acme	ACME001	1​
10002​	Bob's	BOB001	1.5​
10003​	Tom's	TOM001	0.5​
10004​	Bill's	BILL001	4​

but with longer code (Power Query aka Get&Transform)

[SIZE=1]// Append1
let
    Source = Table.Combine({Table1, Table2}),
    Group = Table.Group(Source, {"Mfr"}, {{"Count", each _, type table}}),
    ItemList = Table.AddColumn(Group, "Item", each List.Distinct(Table.Column([Count],"Item"))),
    ExpandItem = Table.ExpandListColumn(ItemList, "Item"),
    MPNList = Table.AddColumn(ExpandItem, "MPN", each List.Distinct(Table.Column([Count],"MPN"))),
    ExpandMPN = Table.ExpandListColumn(MPNList, "MPN"),
    Min = Table.AggregateTableColumn(ExpandMPN, "Count", {{"Cost", List.Min, "Min of Cost"}}),
    DeDup = Table.Distinct(Min, {"Mfr"}),
    Reorder = Table.ReorderColumns(DeDup,{"Item", "Mfr", "MPN", "Min of Cost"})
in
    Reorder[/SIZE]

 

[Leeds Knight]

Ah-ha! Thanks Sandy666 - I didn't quite understand your code (still learning), but you got me thinking...

Final solution:
Click on Table1 > Data Tab > Get & Transform Data ribbon > From Table/Range > Close & Load
Click on Table2 > Data Tab > Get & Transform Data ribbon > From Table/Range > Close & Load
Data Tab > Get & Transform Data ribbon > Get Data > Combine Queries > Append

Primary Table = Table 1
Table to append to the primary table = Table 2
OK
Close & Load

I do end up with duplicate sheets though...

 

[sandy666]

see in the File

there are two results out of which one (your expected result), in my opinion, is incorrect due to various ITEMs and MPNs. IMO post#2 has proper result.

Item	Mfr	MPN	Min
10003​	Tom's	TOM001	0.5​
10001​	Acme	ACME001	1​
10001​	Bob's	BOB002	1.5​
10002​	Bob's	BOB001	1.5​
10004​	Bill's	BILL001	4​