cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
datavis Member
Member

merge queries

I have two tables with many of the same headers but all the records are not the same. One table has about 1000 records and the other table has about 100 records. I should end up with 1,100 records total.

When I select merge queries and choose the headers in each of the tables are that are identical headers, I get the new column at the far right of the table. When I try to expand or aggregate, it does not merge the data. What am I doing wrong?

Any help would be very much appreciated.

2 ACCEPTED SOLUTIONS

Accepted Solutions

Re: merge queries

If you specify the "Join Kind" as Full Outer, you should get all rows from both tables when performing a Merge statement.

 

But there is a difference between a Merge statement and what you are describing (if I understand correctly). With a Merge statement you are combining queries based on matching rows. With Append you are combining queries based on matching columns. 

 

So I think that you should try an Append Queries As New. So your tables of 1000 and 100 rows, will return a result set of 1100 rows, as you desire. The number of columns for each query must be the same for each query, so for the appending you will use the same columns from both querys. 

 

Append requires columns to be exactly similar to work in best condition. if columns in source queries are different, append still works, but will create one column in the output per each new column, if one of the sources doesn’t have that column the cell value of that column for those rows will be null.

 

Append is similar to UNION ALL in T-SQL. It will not remove duplicated. Then you have to use Group By or Remove Duplicate Rows to get rid of duplicated. 

View solution in original post

datavis Member
Member

Re: merge queries

Thank you. Append Queries worked!

View solution in original post

3 REPLIES 3

Re: merge queries

If you specify the "Join Kind" as Full Outer, you should get all rows from both tables when performing a Merge statement.

 

But there is a difference between a Merge statement and what you are describing (if I understand correctly). With a Merge statement you are combining queries based on matching rows. With Append you are combining queries based on matching columns. 

 

So I think that you should try an Append Queries As New. So your tables of 1000 and 100 rows, will return a result set of 1100 rows, as you desire. The number of columns for each query must be the same for each query, so for the appending you will use the same columns from both querys. 

 

Append requires columns to be exactly similar to work in best condition. if columns in source queries are different, append still works, but will create one column in the output per each new column, if one of the sources doesn’t have that column the cell value of that column for those rows will be null.

 

Append is similar to UNION ALL in T-SQL. It will not remove duplicated. Then you have to use Group By or Remove Duplicate Rows to get rid of duplicated. 

View solution in original post

datavis Member
Member

Re: merge queries

Thank you. Append Queries worked!

View solution in original post

Highlighted

Re: merge queries

Perfect, could you please mark my previous reply as the solution. thank you!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors