How to merge or append views ?

[Anonymous]

Hello, 

I have 2 PostgreSQL views. I have made a sample data below for reference. 

What I want is to get the desired result posted below for these 2 tables, remember column names in both the rows are different but has the same value in it(which I can change to append).

Table 1 

Name	Age	Salary
A	20	10000
B	30	20000
C	40	30000

Table 2

Name_text	Age_Number	Address_value	Gender_text
A		X1	M
D	50	Y1	M
E	60	Z1	F

Desired Result

Name	Age	Address_value	Gender_text	Salary
A	20	X1	M	10000
B	30			20000
C	40			30000
D	50	Y1	M
E	60	Z1	F

Result I am getting when appending is. 

Name	Age	Address_value	Gender_text	Salary	Age_Number
A	20			10000
B	30			20000
C	40			30000
D		Y1	M		50
E		Z1	F		60
A		X1	M

I dont want duplicate rows, I can change column names and then append but then the result I am getting is this - 

Name	Age	Address_value	Gender_text	Salary
A	20			10000
B	30			20000
C	40			30000
D	50	Y1	M
E	60	Z1	F
A		X1	M

Can someone please help on this, I cannot use KNIME or alteryx. Just have to use Power Query or dax to get this solved.

Plus I have used merging queries and it is not working.

Thanks 

[MFelix · Accepted solution]

Hi @Anonymous ,

You need to first do a merge to get the value on the lines where there are commons values like line A and then the append and finally remove duplicates on the first column

Check code below for both tables) and PBIX file attach, be aware that you may need to make some changes to the way the merge is made.

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyABKGBkCgFKsTreQE5BkbgMVhQs5AnokBWBwsFAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Name" = _t, Age = _t, Salary = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Age", Int64.Type}, {"Salary", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name"}, #"Table (2)", {"Name"}, "Table (2)", JoinKind.FullOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Address_value", "Gender_text"}, {"Address_value", "Gender_text"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table (2)", each ([#"Name"] <> null)),
    #"Appended Query" = Table.Combine({#"Filtered Rows", #"Table (2)"}),
    #"Removed Duplicates" = Table.Distinct(#"Appended Query", {"Name"})
in
    #"Removed Duplicates"

// Table (2)
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVIA4ghDIOGrFKsTreQCZJkaAIlIhJgrkGUGEosCibkpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name_text = _t, Age_Number = _t, Address_value = _t, Gender_text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name_text", type text}, {"Age_Number", Int64.Type}, {"Address_value", type text}, {"Gender_text", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Name_text", "Name"}, {"Age_Number", "Age"}})
in
    #"Renamed Columns"