How to combine two columns from two different table into a new table

[a4]

Hi all

I have two different  tables (accounts and leads) which both have their columns. I want to create a new table by combining the _memberno of accounts table with _coldprospectrefernce of leads table into a single column and name of accounts table with fullname of leads table into another column.

The datatypes of columns are same .

Please find the screenshot above. Any help is appreciated.

Kind Regards

Amit Kumar

[FOliveira]

How can we do this in the Query Editor?
Any ideas?

[v-zhenbw-msft]

Hi @a4 ,

Does the following screenshot meet your requirement?

If yes, you can refer the below steps.

1. Create a new table that contains name column and fullname column.

Table = 
    UNION(VALUES(accounts[name]),VALUES(leads[fullname]))

2. Then create a new column to get the memberno and coldpropectrefrence.

Column = 
var _account_value = CALCULATE(SUM(accounts[memberno]),FILTER(accounts,'Table'[name]=accounts[name]))
var _leads_value = CALCULATE(SUM(leads[coldprospectrefrence]),FILTER(leads,'Table'[name]=leads[fullname]))
return
IF(
    ISBLANK(_account_value),_leads_value,_account_value)

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[a4 · Accepted solution]

Hi @v-zhenbw-msft 

Thanks for the solution but I figured the solution by my own.

This is what I did: 

Combination =

UNION(SUMMARIZE(accounts,accounts[_memberno],accounts[name]),SUMMARIZE(leads,leads[_coldprospectreference],leads[fullname]))

Kind Regards

Amit Kumar

[v-zhenbw-msft]

Hi @a4 ,

Glad to hear that you find the solution.

Please kindly mark your reply as a solution to help others find it more quickly and end this thread.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[negi007]

@a4 

Under the table tools select new table and paste the below dax code. You may need to change the code according to your table structure

You can combine values from multiple columns and create one columns with unique values. 

Account_Leads= FILTER (

DISTINCT ( UNION ( VALUES ( Accounts[_memberno] ), VALUES ( leads[_coldprospectrefernce ])),

[new_tab] <> BLANK ()

)

[a4]

Hi @negi007 

This is correct when I want to create a single column by combining values from other coulmns but over here i want  to create two columns.

_memberno + _coldprospectrefernce= 1st column.

name + fullname= 2nd column.

Please go through my first post for the problem statement.

Kind Regards

Amit Kumar

[a4]

Hi negi0007,
This is not what I am looking for I want to combine  _memberno of accounts table with _coldprospectrefernce of leads table into a single column and name of accounts table with fullname of leads table into another column i.e 

I want two new columns to be created in a new table.

Kind Regards

Amit Kumar