Forum Discussion
Connecting 2 tables question
Hi, i'm hoping this is a straightworward question.. I have data across 2 tables and need to track by products/ ALL suppliers etc. My issue is that i have 2 supplier columns for different part...
Hi, Anonymous
You can consider using lookupvalue to create a calculated column in the bill table to integrate the data in the other two tables
Date Due = SWITCH ( 'Bills Table'[Type], "Heavy", LOOKUPVALUE ( 'Table Heavy Products'[Date Due], 'Table Heavy Products'[Product], 'Bills Table'[Product] ), "Light", LOOKUPVALUE ( 'Table Light Products'[Date Due], 'Table Light Products'[Product], 'Bills Table'[Product] ) )Best Regards,
Community Support Team _ Eason
Ok Thanks so much, i think i'm making some progress.
I'm not sure if this is a completely new question or related to the above.
If i have the 3 tables below and i want to track the 4 products to see if they've arrived, or see which suppliers perform well etc, what is the best way to go about this?
| Table Heavy Products | |||
| Product | Supplier | Type | Date Due |
| Apples | H12 | Heavy | 01/02/2021 |
| Oranges | G14 | Heavy | 03/01/2021 |
| Table Light Products | |||
| Product | Supplier | Type | Date Due |
| Apples | H12 | Light | 01/02/2021 |
| Oranges | H12 | Light | 02/01/2021 |
| Bills Table | ||||
| Product | Supplier | Type | Date Arrived | Cost |
| Apples | H12 | Heavy | 01/02/2021 | £ 20.00 |
| Apples | H12 | Light | 02/01/2021 | £ 30.00 |
| Oranges | H12 | Heavy | 03/01/2021 | £ 20.00 |
| Oranges | G14 | Light |
Hi, Anonymous
You can consider using lookupvalue to create a calculated column in the bill table to integrate the data in the other two tables
Date Due =
SWITCH (
'Bills Table'[Type],
"Heavy",
LOOKUPVALUE (
'Table Heavy Products'[Date Due],
'Table Heavy Products'[Product], 'Bills Table'[Product]
),
"Light",
LOOKUPVALUE (
'Table Light Products'[Date Due],
'Table Light Products'[Product], 'Bills Table'[Product]
)
)
Best Regards,
Community Support Team _ Eason