cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RickWald
Frequent Visitor

Merging 2 Queries with Overlapping Data

I have two queries that I'd like to merge together. They have the same columns. The problem is that 1 query has gaps in some of the data. Here is an example:

RickWald_0-1640305192034.png

 

 Would I be able to merge these queries to fill in the 0's from table 1? Is it possible to delete "Apple" and "Banana" from table 1 and do a Full Outer Row merge?

 

Thanks!

 

 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

You can append three pieces together.

  1. Table2 inner join with the null rows of Table1.
  2. Table1 left anti join with Table2.
  3. Table2 left anti join with Table1.

Sample query with Table1 and Table2 definitions embedded:

let
    Table1 =
        Table.FromRows(
            {{"Apple", 1, null}, {"Banana", 2, null }, {"Orange", 1, null}, {"Peach", 4, 4}, {"Pear", 2, 2}},
            type table [Fruit = text, Price = number, Weight = number]
        ),
    Table2 =
        Table.FromRows(
            {{"Apple", 1, 1}, {"Banana", 2, 2 }, {"Watermelon", 2, 3}},
            type table [Fruit = text, Price = number, Weight = number]
        ),
    Appended = Table.Combine(
        {
            Table.NestedJoin(Table2, {"Fruit"}, Table.SelectRows(Table1, each [Weight] = null), {"Fruit"}, "Merge", JoinKind.Inner),
            Table.NestedJoin(Table1, {"Fruit"}, Table2, {"Fruit"}, "Merge", JoinKind.LeftAnti),
            Table.NestedJoin(Table2, {"Fruit"}, Table1, {"Fruit"}, "Merge", JoinKind.LeftAnti)
        }),
    #"Removed Columns" = Table.RemoveColumns(Appended,{"Merge"})
in
    #"Removed Columns"

View solution in original post

3 REPLIES 3
v-angzheng-msft
Community Support
Community Support

Hi, @RickWald 

 

May I ask if your problem has been solved? Is the above post helpful to you?

The solution provided by @AlexisOlson  above can work for you.

 

If all your blank items can find matching values in another table, then you can even more easily get the results by appending the data from the two tables and then filtering out the blank rows.

Result:

vangzhengmsft_0-1640585556666.png

 

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AlexisOlson
Super User
Super User

You can append three pieces together.

  1. Table2 inner join with the null rows of Table1.
  2. Table1 left anti join with Table2.
  3. Table2 left anti join with Table1.

Sample query with Table1 and Table2 definitions embedded:

let
    Table1 =
        Table.FromRows(
            {{"Apple", 1, null}, {"Banana", 2, null }, {"Orange", 1, null}, {"Peach", 4, 4}, {"Pear", 2, 2}},
            type table [Fruit = text, Price = number, Weight = number]
        ),
    Table2 =
        Table.FromRows(
            {{"Apple", 1, 1}, {"Banana", 2, 2 }, {"Watermelon", 2, 3}},
            type table [Fruit = text, Price = number, Weight = number]
        ),
    Appended = Table.Combine(
        {
            Table.NestedJoin(Table2, {"Fruit"}, Table.SelectRows(Table1, each [Weight] = null), {"Fruit"}, "Merge", JoinKind.Inner),
            Table.NestedJoin(Table1, {"Fruit"}, Table2, {"Fruit"}, "Merge", JoinKind.LeftAnti),
            Table.NestedJoin(Table2, {"Fruit"}, Table1, {"Fruit"}, "Merge", JoinKind.LeftAnti)
        }),
    #"Removed Columns" = Table.RemoveColumns(Appended,{"Merge"})
in
    #"Removed Columns"

Thank you Alexis!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!