mergepowerqueryfull-outer-join

Merge two tables (Full Outer Join) but get additional rows (PowerQuery)

I want to join two tables with 3 merging columns.

Here is the first table: enter image description here

Here the second table: enter image description here

That´s what I get: enter image description here

I want to get all rows from the Table "IST" as well as all rows from Table "SOLL". Every time when there is data in one of each tables, I want to get just null values. PowerQuery just adds arbitrarily new rows, which aren´t in the original tables.

I just used "Merged Queries as New" and used as matching columns "CC", "Date", "Block/Day".

Code: = Table.NestedJoin(#"SOLL", {"CC", "Date", "Block/Day"}, #"IST", {"CC", "Date", "Block/Day"}, "IST", JoinKind.FullOuter)

I expect the following after Expanding: enter image description here

Solution

  • I'm not sure where you are getting Block 5, Block 6 etc... since they are not even in your tables.

    I suspect you are using SQL and Query Folding may be messing this up. See if Table.Buffer(...) fixes it up for you. Try:

    Table.NestedJoin(
  Table.Buffer(#"SOLL"), {"CC", "Date", "Block/Day"},
  Table.Buffer(#"IST"), {"CC", "Date", "Block/Day"},
  "IST", JoinKind.FullOuter
)

    You could also add Table.Buffer(#"the last step") at the end of each query.