Merging 2 different tables

Question

Hello experts,Attached is an Excel file with an example of what I need:I have 2 data tables,1) A list of items quantities in different warehouses2) A list of purchase orders of items, each item can have several PO's.The requirement is to present in "Table" visual a list of items whose total quantity in warehouses is "0" and for each such item the total quantity available for it in procurement (if there is no procurement then "0").Thank you very much for any helpWH's InventoryWH NameItem No.Item Name&nbsp;Qty. in WHWH1Item1Name10WH2Item1Name110WH1Item2Name25WH2Item2Name20WH3Item2Name21WH1Item3Name30WH1Item4Name40WH1Item5Name50WH2Item5Name50WH1Item6Name62WH2Item6Name67WH3Item6Name60WH1Item7Name70WH1Item8Name80WH2Item8Name80&nbsp;&nbsp;&nbsp;&nbsp;PO's BalancePO No.Item No.Item Name&nbsp;Qty. on PurchasePO1Item1Name18PO2Item1Name17PO3Item2Name25PO4Item4Name46PO5Item4Name43PO5Item6Name610PO5Item8Name820PO6Item8Name810PO6Item9Name92PO6Item10Name106PO6Item11Name113&nbsp;&nbsp;&nbsp;&nbsp;Visual TableItem No.Item Name&nbsp;Qty. in WH's&nbsp;Qty. on PurchaseItem3Name300Item4Name409Item5Name500Item7Name700Item8Name8030

jdbuchanan71 · Accepted Answer

RafaelAri&nbsp;
You can create and Items table that will sit above Warehouse and PO and link to them both based on Item No.
This is the DAX code for the new table.
Items = 
DISTINCT(
    UNION ( 
        SUMMARIZE('Warehouse Inventory','Warehouse Inventory'[Item No.],'Warehouse Inventory'[Item Name]),
        SUMMARIZE('PO Balance','PO Balance'[Item No.],'PO Balance'[Item Name])
    )
)
Then you link it to the other tables in the model:

Then a couple of mesures to sum the amounts from Warehouse and PO.
Qty. in WH's = SUM ( 'Warehouse Inventory'[Qty. in WH] )Qty. on Purchase = SUM ( 'PO Balance'[Qty. on Purchase] )
&nbsp;
Add the item number and name from the Items table and the meausres to get the counts then set the fitler on the table to [Qty. in WH's] = 0

I have attached my sample file for you to look at.
&nbsp;

Forum Discussion

Merging 2 different tables

2 Replies

Related content

Merge Summarize Table

Merge different Tables

Merge two tables with different source using query

Merge Multiple Tables with Different Column Names

Snowflake Tables Not Merging in Service

WH's Inventory
WH Name	Item No.	Item Name	Qty. in WH
WH1	Item1	Name1	0
WH2	Item1	Name1	10
WH1	Item2	Name2	5
WH2	Item2	Name2	0
WH3	Item2	Name2	1
WH1	Item3	Name3	0
WH1	Item4	Name4	0
WH1	Item5	Name5	0
WH2	Item5	Name5	0
WH1	Item6	Name6	2
WH2	Item6	Name6	7
WH3	Item6	Name6	0
WH1	Item7	Name7	0
WH1	Item8	Name8	0
WH2	Item8	Name8	0

PO's Balance
PO No.	Item No.	Item Name	Qty. on Purchase
PO1	Item1	Name1	8
PO2	Item1	Name1	7
PO3	Item2	Name2	5
PO4	Item4	Name4	6
PO5	Item4	Name4	3
PO5	Item6	Name6	10
PO5	Item8	Name8	20
PO6	Item8	Name8	10
PO6	Item9	Name9	2
PO6	Item10	Name10	6
PO6	Item11	Name11	3

Visual Table
Item No.	Item Name	Qty. in WH's	Qty. on Purchase
Item3	Name3	0	0
Item4	Name4	0	9
Item5	Name5	0	0
Item7	Name7	0	0
Item8	Name8	0	30