Merging column to current table column (without adding new column)

[navafolk]

Hi pros,

I have 2 tables like

- 'Current' table:

NAME	CUR	TYPE
a	usd	on
b	eur	off
c	jap	on

- 'New' table:

NAME

a

d

e

How could I merge data from 'New'[NAME] to 'Current'[NAME] without adding new column in DAX? Leave other columns of 'Current' blank for those added from 'New' that 'New' does not mention about. For [NAME] appears in both tables, 'Current' table is pioritized. My expected merging would be:

NAME	CUR	TYPE
a	usd	on
b	eur	off
c	jap	on
d
e

Thank you very much for your help.

[navafolk]

Anyone, please help.

[v-kelly-msft · Accepted solution]

Hi  @navafolk ,

Create a calculated table using below expression:

Table = 
var _tab=CALCULATETABLE(VALUES('Current'[NAME]),FILTER('Current','Current'[NAME]<>BLANK()))
Return
 ADDCOLUMNS(UNION(_tab,EXCEPT(VALUES('New'[NAME]),VALUES('Current'[NAME]))),"CUR",CALCULATE(MAX('Current'[CUR]),FILTER('Current','Current'[NAME]=EARLIER('Current'[NAME]))),"TYPE",CALCULATE(MAX('Current'[TYPE]),FILTER('Current','Current'[NAME]=EARLIER('Current'[NAME]))))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[navafolk]

Hi @amitchandak, just one more question, in case [NAME] appears in both tables, can we make 'Current' table pioritized?

It is something like NATURALLEFTOUTERJOINT, but these 'Current' and 'New' has no relationship. Thank you.

[amitchandak]

@navafolk , Append in power query

refer for steps: https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[navafolk]

Thank you @amitchandak, sorry for my unclear, but I am seeking this within DAX.

[amitchandak]

@navafolk , a new table like 

Union(Table1, selectcolumns(Table2, Table2[Name], "CUR", "", "TYPE", "")

or

Union(Table1, selectcolumns(Table2, Table2[Name], "CUR", blank() , "TYPE", blank())

Also refer: https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[navafolk]

It is just awesome. I stuck at creating null column in DAX. Big thanks.