Union two generated tables with different columns (not Query)

[henrique0galli]

Hi,

So I've got two tables which were generated through DAX.

Table01 and Table02

I can't manage them via Query because they wer generated through DAX (correct?)

So, I want to combine these two tables in a way that the outcome states only one line per Item and the balance of each month.

Below are some data for these two tables:

Table01 (inbounds items, QTY is positive)

Item	Product Description	Qty	Delivery Date
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
78011	END PANEL FRAME 2103x930mm	16	Monday, 7 May 2018
76898	END PANEL FRAME 1431X930MM	16	Monday, 7 May 2018
76898	END PANEL FRAME 1431X930MM	16	Monday, 7 May 2018
76898	END PANEL FRAME 1431X930MM	16	Monday, 7 May 2018
76895	END PANEL FRAME 1431x1230mm	16	Monday, 7 May 2018
76895	END PANEL FRAME 1431x1230mm	16	Monday, 7 May 2018
76895	END PANEL FRAME 1431x1230mm	16	Monday, 7 May 2018

Table02 (outbounds items, QTY is negative)

Item	TOTAL	Month
4023/16	-27	June
4044/16	-9500	June
4045/16	-1	June
4098/15	-24	June
76859	-7	June
76860	-48	June
76865	-640	June
76866	-3327	June
76868	-589	June
76869	-308	June
76871	-5002	June
76872	-3128	June
76873	-1767	June
76876	-155	June
76895	-59	June
76897	-50	June
76898	-244	June
76914	-930	June
76957	-201	June
76958	-2960	June
76959	-613	June
76968	-3449	June
76981	-3447	June
76987	-279	June
76988	-94	June

So the idea is, these are inbounds of products arriving into my warehouse (Table01) and the outbounds being despatached (Table02). I want to put these data together, summarizing each line as an Item, saying it's final balance for the month. The ideia is to have this linear as data are updated (this will be the next step, don't need to worry about this part on this inquire).

Also, should the dates in these two tables be formatted the same beforehand?

[Greg_Deckler]

Can you present the expected output? Have you looked at NATURALLEFTINNERJOIN and NATURALLEFTOUTERJOIN?

https://msdn.microsoft.com/en-us/query-bi/dax/naturalleftouterjoin-function-dax

[henrique0galli]

Hi @Greg_Deckler,

I haven't tried NATURALLEFTINNERJOIN and NATURALLEFTOUTERJOIN.

Apparently it won't work because the tables have different columns (I might be wrong, sorry I am new to this).

According to msdm regards NATURALLEFTOUTERJOIN Function (DAX): "Only columns from the same source table (have the same lineage) are joined on."

Here is something similar to the output I am expecting:

Item	Balance	Month
78011	30	June
76898	50	July
76895	-10	July
76866	-50	August
76987	20	August
76988	30	September

*this is an example only, not related to the table calculations above*

As you can see, there is one line for each product (Item), with it's balance and month.

[henrique0galli · Accepted solution]

SOLUTION:

I had to make sure the two set of tables had the same column quantities and it worked.