別々の予算と実績のデータを対比する EXCEL2010 PowerQuery

　予算で作成したデータに実績のデータをPowerQueryのマージ機能（外部結合）で連結しようとしたのですが、以下のように予算になかった顧客分の実績は、予算表の顧客列では空欄になってしまいます。対応方法について考えてみたいと思います。

１．値の置換機能を利用する

・予算表：範囲内を選択→テーブルに変換(ctl+T)→PowerQueryタブ＞テーブルから

　閉じて次に読み込む＞接続のみ＞OK

・実績表：範囲内を選択→テーブルに変換(ctl+T)→PowerQueryタブ＞テーブルから

　閉じて次に読み込む＞接続のみ＞OK

①Power Queryタブ＞マージ

②テーブル１（予算）を選択

③テーブル２（実績）を選択

④どちらも顧客の列を選択

⑤完全外部を選択

OKをクリック

↓

・NewColumnのボタンをクリック

・すべての列を選択

・チェックボックスをはずす

OKをクリック

↓

 

①このようにデータがないことを意味するnullとなっているところに富士通と入れたいのですが。。。

さていよいよここでは値の置換の機能を応用してやってみましょう

②（顧客の列を選択しておいて）変換タブ＞値の置換を選びます。

↓ 

・上段は、「null」とタイプして

・下段は、（後程書き換える目印として何でもいいですが）とりあえず「＠＠＠」とタイプします。

・OKをクリックします。

↓

次の画面になったら数式バーの中で

①見えにくい場合は、クリックで範囲が広くなります。

②「"＠＠＠"」を「each [顧客.1]」と書き換えてenteします。

↓

下のようにnull（空欄）がなくなりました。

あとは、形を整えてエクセルにデータを返します。

↓

・顧客.1の列を削除

・予算と実績の列のnullを０へ置換

２．PowerQueryのif関数を利用する

　予算と実績のデータがマージと列の展開がされるまでは同じです。

・列の追加タブ＞カスタム列の追加で上のような画面になりますので

・式に「if [顧客]=null then [顧客.1] else [顧客]」とタイプしてOKをクリックします。

　（PowerQueryでのif構文は、

　　if 条件式 then 動作1 else 動作2 としなければなりません。）

　（また、空白を意味するnullは、文字列ではないので"null”とはしません。プレビューのテーブルの中でも斜体で右寄せの書式になっていますね。）

あとは、前述のとおり列の削除、並び順、null→０への置換など形を整えてエクセルにデータを返しましょう。

 

３．数式バーを修正せずリボンメニューだけで

数式バーでの修正がよくわからなくて面倒という場合にリボンメニューだけでやろうとすると

・予算のテーブルと実績のテーブルをクエリの追加（マージではなく）で結合する。

（この時点でエクセルにデータを返すとピボットで目的の表がつくれますが。。。）

・顧客の列以外は削除する。

・列に対して重複部分の削除を行う。

・このようにして作成した予算と実績の両方を含む重複のない顧客データの列に対して改めて予算と実績をそれぞれマーする。

とPowerQueryのリボンの機能の操作だけで作業ができます。

以下に詳細エディターの内容を掲載します。

画像は、

テキストは、　

let
    ソース = Table.Combine({テーブル1,テーブル2}),
    削除された重複 = Table.Distinct(ソース, {"顧客"}),
    削除された列 = Table.RemoveColumns(削除された重複,{"予算", "実績"}),
    マージされたクエリ数 = Table.NestedJoin(削除された列,{"顧客"},テーブル1,{"顧客"},"NewColumn",JoinKind.LeftOuter),
    #"展開された NewColumn" = Table.ExpandTableColumn(マージされたクエリ数, "NewColumn", {"予算"}, {"予算"}),
    マージされたクエリ数1 = Table.NestedJoin(#"展開された NewColumn",{"顧客"},テーブル2,{"顧客"},"NewColumn",JoinKind.LeftOuter),
    #"展開された NewColumn1" = Table.ExpandTableColumn(マージされたクエリ数1, "NewColumn", {"実績"}, {"実績"}),
    置き換えられた値 = Table.ReplaceValue(#"展開された NewColumn1",null,0,Replacer.ReplaceValue,{"予算", "実績"})
in
    置き換えられた値

以上。

バージョン：

エクセル2010

PowerQuery　2.26