Forum Discussion

Anil2264's avatar
Anil2264
Helper I
4 years ago

Merge 2 Tables

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.  ...
Need Help
AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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's avatar
Anil2264
Helper I
4 years ago

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's avatar
    AlexisOlson
    Super User
    4 years ago

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