Forum Discussion
Union two generated tables with different columns (not Query)
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?
SOLUTION:
I had to make sure the two set of tables had the same column quantities and it worked.
3 Replies
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
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.
SOLUTION:
I had to make sure the two set of tables had the same column quantities and it worked.
