creating table from multiple other columns

[nadav50]

hi ,

i added a pic that im trying to do in power bi but not succeed,

hope you understand 🙂

[KHorseman · Accepted solution]

Hit the New Table button and add this DAX formula:

new table = ADDCOLUMNS(
	NATURALINNERJOIN(
		'Table 1',
		SUMMARIZE(
			'Table 2',
			'Table 2'[closing balance]
		)
	),
	"sum balance",
	'Table 1'[opening balance] + 'Table 2'[closing balance]
)

Note that you need to have a relationship between the two source tables. Also if your real data is not quite like this sample data, you may have to use NATURALLEFTOUTERJOIN instead. Depends on whether or not one of the two tables has blanks or duplicates.

[efglynn]

So where is the "New Table" button?

[KHorseman]

@efglynnUnder the Modeling tab in the ribbon.

[Sean]

@nadav50@efglynn

Frankly I'm surprised @KHorseman offered a DAX solution maybe because this was last year

I think the Query Editor is made for this!

Look at these 2 links describing all the joins and select which one you need in your case

http://www.excelguru.ca/blog/2015/12/16/merge-tables-using-outer-joins-in-power-query/

http://www.excelguru.ca/blog/2015/12/23/merge-tables-using-inner-and-anti-joins-in-power-query/

Once you know which join type you need its really easy to do in the Query Editor

[nadav50]

Amazing!! THX