Forum Discussion
How to merge or append views ?
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
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"
1 Reply
- MFelix
Super User
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"