PowerQueryの完全外部結合をSQLサーバーの結果と同じにする方法はありますか？

Question

SQL serverでfull outer joinをやった結果と

PowerQueryでfull outer joinをやった結果で行数が異なるため気になりまして調べました

事象は以下の方のブログと同じで、

http://officetanaka.net/excel/function/GetAndTransform/13.htm

full outerなのに、右側のシート（テーブル）がの一部が欠落してしまうようです

”でもなあ、これもなあ、何で「4」が表示されないんだろ…。これじゃ"完全"になってねーじゃん…。結局「FROM テーブル1」ってことは「テーブル1から～」ってことで、照合列はあくまでテーブル1からしか持ってこないってことなのかな。ってことはやっぱ、FROMで指定したテーブル1の方が中心というか、メインというか、主役というか、そういうことなんだろうか。今度、データベース系の偉い人に聞いてみよう。”

そして、この現象で右側のシートでマッチしない行が２つ以上あるとNULLが１行となるため、FULL OUTERでのSQLでの出力異なる現象になります。

ＳＱＬｓｅｒｖｅｒと同じ出力にする方法はあるのでしょうか

Answer 1

> 事象は以下の方のブログと同じで、> *http://officetanaka.net/excel/function/GetAndTransform/13.htm*

> でもなあ、これもなあ、何で「4」が表示されないんだろ…。

まず上記の件に関しては、単に[テーブル2]の[ID]を表示列として選択していないから
そのように見えているだけです。

> SQL serverでfull outer joinをやった結果

（ SQL Server 上で実行する SQL ）

SELECT t1.ID AS [テーブル1のID], 
       t1.記号, 
       t2.ID AS [テーブル2のID], 
       t2.名前
FROM dbo.テーブル1 AS t1
FULL OUTER JOIN dbo.テーブル2 AS t2 
ON t1.ID = t2.ID;

---

（実行結果）

つまり、FULL OUTER JOIN だけでは、結合キーとなっている
2 つの列の値を 1 つの列に統合するような操作は行なわれません。

少なくとも、上記記事で挙げられている例において、
「完全外部結合の結果から一部のキーの値が欠落している」
という解釈するのはそもそも誤りです。

そしてそれは PowerQuery であっても同様です。

（ PowerQuery の詳細エディタ）

let
    Source1 = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    ChangedType1 = Table.TransformColumnTypes(Source1,{{"ID", Int64.Type}, {"記号", type text}}),
    Source2 = Excel.CurrentWorkbook(){[Name="テーブル2"]}[Content],
    ChangedType2 = Table.TransformColumnTypes(Source2,{{"ID", Int64.Type}, {"名前", type text}}),
    JoinedTable = Table.NestedJoin(ChangedType1, {"ID"}, ChangedType2, {"ID"}, "テーブル2", JoinKind.FullOuter),
    Expanded = Table.ExpandTableColumn(JoinedTable, "テーブル2", {"ID", "名前"}, {"テーブル2のID", "名前"}),
    RenamedColumns = Table.RenameColumns(Expanded,{{"ID", "テーブル1のID"}})
in
    RenamedColumns

---

（プレビュー結果）

> PowerQueryでfull outer joinをやった結果で行数が異なる

そういう結果が返されるか否かは、実際に PowerQuery で実行されているクエリと、
そのクエリから参照しているデータソース次第でしょう。

Answer 2

> この２つのデータを、PowerBIかExcelのPowerQueryで完全外部結合、> キーをBusinessEntityIDでJOINすると、表示が左側のテーブルの> BusinessEntityIDのみが表示され（ここは右側のテーブルを> 表示すれば良いのですが）

それは 2 つのテーブルを結合するところまでで終わらせてしまっていて、
結合された方のテーブルの列を全く展開していないからではないでしょうか。

（ PowerQuery の詳細エディタ）

let
    Source1 = Excel.CurrentWorkbook(){[Name="Person"]}[Content],
    ChangedType1 = Table.TransformColumnTypes(Source1,{{"FirstName", type text}, {"BusinessEntityID", Int64.Type}}),
    Selected1 = Table.SelectRows(ChangedType1, each [BusinessEntityID] >= 290 and [BusinessEntityID] <= 295),
    Source2 = Excel.CurrentWorkbook(){[Name="BusinessEntityAddress"]}[Content],
    ChangedType2 = Table.TransformColumnTypes(Source2,{{"BusinessEntityID", Int64.Type}, {"AddressID", Int64.Type}}),
    Selected2 = Table.SelectRows(ChangedType2, each [BusinessEntityID] >= 290 and [BusinessEntityID] <= 295),
    JoinedTable = Table.NestedJoin(Selected1, {"BusinessEntityID"}, Selected2, {"BusinessEntityID"}, "BusinessEntityAddress", JoinKind.FullOuter),
    Expanded = Table.ExpandTableColumn(JoinedTable, "BusinessEntityAddress", {"BusinessEntityID", "AddressID"}, {"BusinessEntityAddressのBusinessEntityID", "AddressID"}),
    RenamedColumns = Table.RenameColumns(Expanded,{{"BusinessEntityID", "PersonのBusinessEntityID"}})
in
    RenamedColumns

---

（ Table.NestedJoin 関数を呼び出した時点のプレビュー）

（ その次に Table.ExpandTableColumn 関数を呼び出した時点のプレビュー）

（最終結果）

Answer 3

adventureworksのDBで再現してみました

select  

　FirstName,

BusinessEntityID

from person.person

where BusinessEntityID between 290 and 295

order by BusinessEntityID

実行結果は、

Ranjit 290

Gustavo 291

Catherine 293

Kim 295

select b.BusinessEntityID,b.AddressIDfrom   Person.BusinessEntityAddress bwhereb.BusinessEntityID between 290 and 295order by b.BusinessEntityID

290 34

292 975

294 1086

これらを full outer joinで結合すると

select  p.FirstName, p.BusinessEntityID,b.BusinessEntityID,b.AddressID

from    person.person pfull outer join Person.BusinessEntityAddress b on p.BusinessEntityID = b.BusinessEntityID

where p.BusinessEntityID between 290 and 295 or b.BusinessEntityID between 290 and 295order by p.BusinessEntityID

これで

NULL NULL 292 975

NULL NULL 294 1086

Ranjit 290 290 34

Gustavo 291 NULL NULL

Catherine 293 NULL NULL

Kim 295 NULL NULL

となります

この２つのデータを、PowerBIかExcelのPowerQueryで完全外部結合、キーをBusinessEntityIDでJOINすると、表示が左側のテーブルのBusinessEntityIDのみが表示され（ここは右側のテーブルを表示すれば良いのですが）

NULLが１行として扱われ、５行しか表示されなくなってしまいます