Combine table in power query to add rows from one table to another

[Anonymous]

Hello,

I want to get the all the columns data from table 2 to table 1 where table2[column2]<> table1[column2]

This is just an example, I have lot of rows and columns that I want to merge b/w the two tables and want to match the column 2 of table 2 to table 1 and where column 2 data don't match, I want to add all the rows from 2nd table to 1st table.

table 1:

Column1	Column2	Column3	Column2a	Column5	Column6
Alpha	AB01076	Active	AB01076C	Final	Active
Alpha	AB01079	Active	AB01079A	Primary	Active
Alpha	AB01083	Active	AB01083Z	Primary	Active
Alpha	AB01163	Active	AB01163B	Final	Active
Alpha	AB01167	Active	AB01167C	Final	Active
Alpha	AB01168	Active	AB01168A	Final	Active

table 2:

Column1	Column2	Column3	Column2a	Column5	Column6
Alpha	AB01083	Active	AB01083A	Candidate	Active
Alpha	AB01086	Active	AB01086A	Candidate	Active
Alpha	AB01163	Active	AB01163D	Candidate	New
Alpha	AB01168	Active	AB01168B	Candidate	Active
Alpha	AB01171	Active	AB01171Z	Candidate	Active

End result table 1:

Column1	Column2	Column3	Column2a	Column5	Column6
Alpha	AB01076	Active	AB01076C	Final	Active
Alpha	AB01079	Active	AB01079A	Primary	Active
Alpha	AB01083	Active	AB01083Z	Primary	Active
Alpha	AB01163	Active	AB01163B	Final	Active
Alpha	AB01167	Active	AB01167C	Final	Active
Alpha	AB01168	Active	AB01168A	Final	Active
Alpha	AB01086	Active	AB01086A	Candidate	Active
Alpha	AB01171	Active	AB01171Z	Candidate	Active

[Anonymous]

Hello Ryan,

Thanks for your help. Actaually what I was trying to do is delete the duplicats by doing merge on one and append on the other without creating a new table. But when I am trying to append it, it doesn't show the first table where I merged it. The reason is on the one I want to show all data has informtion on lot of columns that first one don't have it. But while doing that on the 2nd table where I want to append, it doesn't show the 1st table where I merged it.

[ryan_mayu]

@Anonymous 

for the first part, you can select append quries instead of append quries as new to avoid creaing new table.

I don't quite understand the rest of your reply, could you pls update your sample data and provide the expected output?

[Anonymous]

So I did the merge on table 1 and want to append in table 2 but when I try to append, it doesn't give me option of table 1.

if you have time I can show you live data on screen share.

[ryan_mayu · Accepted solution]

@Anonymous 

per our discussion, i found a workaround for you. 

pls try if we can get the correct output.

[ryan_mayu]

@Anonymous 

i know the reason , it's becasue in table 2 we used table's info. 

So we can't append table 1 and table 2 direclty

[Anonymous]

Also tried to create a new table and try to append that with table 1, that also didn't work, thinking I will remove the duplicate. Can you let me know what is the option now to do the append

[ryan_mayu]

@Anonymous 

select table 1 and append quries as new

[Anonymous]

I have added lot of calculated columns in table 1 in data view of PBI, so I wanted to keep the table 1. 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

[ryan_mayu]

@Anonymous 

pls see the attachment below