CODEWITHSUNDEEP
mergepowerquery
Umut K
Umut K

Reputation: 1390

Merging in powerquery

although i selected Full Join, i couldn't get the all rows from both tables.

how can i get all rows from both tables ? (all 12093 rows)

maybe another join type may help ? 

let
    Source = Table.NestedJoin(#"Beton Irsaliye Kumulatif",{"Proje No & Adi", "Firma Kodu"},#"Beton Muhasebe Kumulatif",{"Proje No & Adi", "Hesap No"},"Beton Muhasebe Kumulatif",JoinKind.FullOuter)
in
    Source

enter image description here

Upvotes: 0

Views: 62

Answers (1)

Marc Pincince
Marc Pincince

Reputation: 5192

Your merge is accounting for all your rows. It's just that 4 of the rows in the first table don't have matches in the second table.

Here's a simple example of what is happening. Here, I have two tables: Table1 and Table2. Both have 10 rows. In fact, both are exactly the same.

enter image description here

If I choose to do a Full Outer join with these, using Col1 and Col2 for matching, I'll see this:

enter image description here

It tells me that 10 of the rows from the first table (Table1) match rows of the second table (Table2).

Now, if I change the last two rows of Table1 (specifically, the last two rows of Col2 of Table1) like this:

enter image description here

Then when I try to do a Full Outer join the same way, I'll see this:

enter image description here

Only 8 of the rows from the first table (Table1) match rows of the second table (Table2).

But when I continue with the merge, I'll see Table1's information in a table with Table2's matching information as embedded tables in column "NewColumn" of that table:

enter image description here

When I then expand "NewColumn", I see all the info from Table1, as before, and all matching info from Table2, as well as rows that don't have matches between the two tables.

enter image description here

All rows of both tables are accounted for.

Upvotes: 1

Related Questions