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

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Item[/TD]
[TD="width: 64"]Mfr[/TD]
[TD="width: 64"]MPN[/TD]
[TD="width: 64"]Cost[/TD]
[/TR]
[TR]
[TD="align: right"]10001[/TD]
[TD]Acme[/TD]
[TD]ACME001[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]10002[/TD]
[TD]Bob's[/TD]
[TD]BOB001[/TD]
[TD="align: right"]1.5[/TD]
[/TR]
[TR]
[TD="align: right"]10003[/TD]
[TD]Tom's[/TD]
[TD]TOM001[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Item[/TD]
[TD="width: 64"]Mfr[/TD]
[TD="width: 64"]MPN[/TD]
[TD="width: 64"]Cost[/TD]
[/TR]
[TR]
[TD="align: right"]10001[/TD]
[TD]Bob's[/TD]
[TD]BOB002[/TD]
[TD="align: right"]1.5[/TD]
[/TR]
[TR]
[TD="align: right"]10003[/TD]
[TD]Tom's[/TD]
[TD]TOM001[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD="align: right"]10004[/TD]
[TD]Bill's[/TD]
[TD]BILL001[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 269"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Mfr[/TD]
[TD]MPN[/TD]
[TD]Min Cost[/TD]
[/TR]
[TR]
[TD="align: right"]10001[/TD]
[TD]Acme[/TD]
[TD]ACME001[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]10002[/TD]
[TD]Bob's[/TD]
[TD]BOB001[/TD]
[TD="align: right"]1.5[/TD]
[/TR]
[TR]
[TD="align: right"]10003[/TD]
[TD]Tom's[/TD]
[TD]TOM001[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD="align: right"]10004[/TD]
[TD]Bill's[/TD]
[TD]BILL001[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Item[/td][td=bgcolor:#70AD47]Mfr[/td][td=bgcolor:#70AD47]MPN[/td][td=bgcolor:#70AD47]Min of Cost[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

10001​

[/td][td=bgcolor:#E2EFDA]Bob's[/td][td=bgcolor:#E2EFDA]BOB002[/td][td=bgcolor:#E2EFDA]

1.5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10001​

[/td][td]Acme[/td][td]ACME001[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

10002​

[/td][td=bgcolor:#E2EFDA]Bob's[/td][td=bgcolor:#E2EFDA]BOB001[/td][td=bgcolor:#E2EFDA]

1.5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10003​

[/td][td]Tom's[/td][td]TOM001[/td][td]

0.5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

10004​

[/td][td=bgcolor:#E2EFDA]Bill's[/td][td=bgcolor:#E2EFDA]BILL001[/td][td=bgcolor:#E2EFDA]

4​

[/td][/tr]
[/table]

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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Item[/td][td=bgcolor:#70AD47]Mfr[/td][td=bgcolor:#70AD47]MPN[/td][td=bgcolor:#70AD47]Min of Cost[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

10001​

[/td][td=bgcolor:#E2EFDA]Acme[/td][td=bgcolor:#E2EFDA]ACME001[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10002​

[/td][td]Bob's[/td][td]BOB001[/td][td]

1.5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

10003​

[/td][td=bgcolor:#E2EFDA]Tom's[/td][td=bgcolor:#E2EFDA]TOM001[/td][td=bgcolor:#E2EFDA]

0.5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10004​

[/td][td]Bill's[/td][td]BILL001[/td][td]

4​

[/td][/tr]
[/table]

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.

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Item[/td][td]Mfr[/td][td]MPN[/td][td]Min[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10003​

[/td][td]Tom's[/td][td]TOM001[/td][td]

0.5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10001​

[/td][td]Acme[/td][td]ACME001[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]

10001​

[/td][td=bgcolor:#E2EFDA]Bob's[/td][td=bgcolor:#FFFF00]BOB002[/td][td]

1.5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]

10002​

[/td][td=bgcolor:#E2EFDA]Bob's[/td][td=bgcolor:#FFFF00]BOB001[/td][td]

1.5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10004​

[/td][td]Bill's[/td][td]BILL001[/td][td]

4​

[/td][/tr]
[/table]