Merge 2 Tables

[Anil2264]

Hi,

Can you please help me out to have below output in power query which joins I need to make? Please help

All data columns are existing in 3 source files then I wanted to output as below.

@amitchandak 

Thanks

Anil Pal

[AlexisOlson]

For each new data column, remove the values in the columns to the left and then combine them.

Full sample query you can paste into the Advanced Editor of a new blank query:

let
    DATA1 = #table(type table [DATA1 = text], {{"A"},{"B"},{"C"},{"D"},{"E"},{"F"},{"G"}}),
    DATA2 = #table(type table [DATA2 = text], {{"A"},{"D"},{"G"},{"J"},{"H"},{"I"}}),
    DATA3 = #table(type table [DATA3 = text], {{"B"},{"J"},{"L"},{"K"},{"M"},{"N"}}),
    List1 = DATA1[DATA1],
    List2 = DATA2[DATA2],
    List12 = List.Distinct(List.Combine({List1, List2})),
    Table1 = DATA1,
    Table2 = Table.SelectRows(DATA2, each not List.Contains(List1,  [DATA2])),
    Table3 = Table.SelectRows(DATA3, each not List.Contains(List12, [DATA3])),
    Source = Table.Combine({Table1, Table2, Table3})
in
    Source

(If you have DATA1, DATA2, DATA3 defined as separate queries, remove those definitions from the query above.)

[Anil2264]

Thanks Alex for your response but in my actual records i have multiple columns & in single columns almsot having 1Lac rows 

then how can we achieve it.

Please help me

[AlexisOlson]

I don't understand "almsot having 1Lac rows". Can you give an example that is more like your actual data?

[Anonymous]

Oh ok. That is JoinKind.Right.Anti

--Nate

[Anil2264]

Hi,
But Full outer join will print both tables record right, I only wanted to see the non-existing item of my first table into my merging table

[amitchandak]

@Anil2264 , I think append, keep the column names different

Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Anonymous]

Full Outer Joins. 

--Nate