CODEWITHSUNDEEP
mergepowerqueryfull-outer-join
M123
M123

Reputation: 23

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

Upvotes: -1

Views: 530

Answers (2)

Sam Nseir
Sam Nseir

Reputation: 12016

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.

Upvotes: 0

horseyride
horseyride

Reputation: 21298

You want to start the merge in IST and use

  #"Merged Queries" = Table.NestedJoin(Source, {"CC", "Date", "Block/Day"}, SOL, {"CC", "Date", "Block/Day"}, "SOL", JoinKind.LeftOuter),
  #"Expanded SOL" = Table.ExpandTableColumn(#"Merged Queries", "SOL", {"CC", "Date", "Number", "Pieces", "Block/Day"}, {"SOL.CC", "SOL.Date", "SOL.Number", "SOL.Pieces", "SOL.Block/Day"})

enter image description here

Upvotes: 0

Related Questions