Merge two tables and choose the one with lower price

1

Hello, I have two tables with a list of products from two different suppliers. I want to create a third table with the sum of the two tables, but I would like to include only the cheapest product among the duplicates (I have the EAN field to compare). To merge the two tables, I am using a button per row with the addrow function, but I don’t know how to compare the duplicate EANs and select the one with the lower price. I would greatly appreciate it if someone could help me with this.

2

That’s an interesting take which i would be interested on knowing how to do.
The approach i’d take would be to try and order them with the sort function then try to get the first. Maybe try Unique() or First()?
If someone could help here, it would be great!

3

Thanks for the help.
I have tried several formulas but I can’t do it. Maybe I should create another table with the result, but I don’t know how to compare the two rows with the same EAN to keep the one with the lowest price.

4

It is significantly easier if you enter both lists into the same “input” table and discriminate between suppliers by a column with a selection: supplier 1, supplier 2 etc.

However, given you probably want this for convenience and as a one-shot, here’s the solution. It collects all unique EANs, then for each EAN looks up an item in each table and based on which table(s) have the item with that EAN, and whose price is lower, inserts a row into the resulting table with either a reference from Table 1, or Table 2, or both if prices match:

The action:

ListCombine(
  [Random Product Table 1].[EAN Code],
  [Random Product Table 2].[EAN Code]
).Unique().ForEach(CurrentValue.WithName(EAN,
  [Random Product Table 1].Filter(
    [EAN Code] = EAN
  ).Sort(true, [Random Product Table 1].Price).First().WithName(MinFromT1,
    [Random Product Table 2].Filter(
      [EAN Code] = EAN
    ).Sort(true, [Random Product Table 2].Price).WithName(MinFromT2,
      [Product table].AddOrModifyRows(
        [EAN Code] = EAN,        
        [Product table].[EAN Code], EAN,
        [Product table].[Row from Table 1], If(
          MinFromT2.IsBlank()
          OR MinFromT1.IsNotBlank() AND MinFromT1.Price <= MinFromT2.Price,
          MinFromT1,
          ""
        ),
        [Product table].[Row from Table 2], If(
          MinFromT1.IsBlank()
          OR MinFromT2.IsNotBlank() AND MinFromT2.Price <= MinFromT1.Price,
          MinFromT2,
          ""
        )
      )
    )
  )
))

Again, would’ve been much easier if the input was a single table.

Also there are other ways to solve this, e.g. use the button to only create necessary rows with all EANs but then use live formulas. Or if you track everything in a single table, you wouldn’t have to even copy it anywhere — just have a formula calculating whether among all rows with the same EAN this one has the lowest price, then filter the table.

1 Like
5

Thank you very much, Paul. This is amazing. It’s much more complex than I thought. I greatly appreciate your help.