加工したい元データを読み込んだ後は、PowerQueryエディタ―を使ってデータを整形します。データを見やすくするために、必要な情報だけを残したり、データ型を変えたりします。
今回は、リボンメニューやマウス操作で簡単にできる「表の結合」手順です。
練習準備
練習用ファイルを用意しました。ダウンロードしてご活用ください。
PowerQueryエディターの開き方
1.データタブ > クエリと接続 > 右端に表示されるクエリを右クリック > 編集
2.PowerQueryエディタ―が開く
結合は2種類ある
パワークエリで表と表を結合して1つの表にまとめることができます。結合には2種類あります。
①横方向 …列が増える
例えば、売上データの表(日付・商品コード・売上数量)と商品マスタの表(商品コード・単価)を、商品コードをキー項目にして結合。売上データの表に単価の列を追加できる。
②縦方向 …行が増える
例えば、同じフォーマット(列名)で月別に売上データがあるとき、1月・2月・3月のデータを結合して、1月のデータの下に2月・3月の行を追加できる。
表の結合(横方向)
売上データと商品マスタを「商品コード」をキーにしてクエリを結合します。
1.PowerQueryエディタ―を開き、「クエリのマージ」をクリックする
●作業中のクエリ …売上データ
●追加するクエリ …商品マスタ
クエリのマージには2種類あるが、今回は「クエリのマージ」を選択している。
クエリのマージ …作業中のクエリに別のクエリを追加する
新規としてクエリのマージ …作業中のクエリと別のクエリを結合して新しいクエリを作成する
2.追加するクエリ・キー項目(照合列)・結合の種類を選択して「OK」をクリックする
●追加するクエリ …商品マスタ
●キー項目 …商品コード(列名をクリックする)
●結合の種類 …左外部(※結合の種類は次の章で解説)
3.「展開ボタン」をクリック > 追加したい列にチェックを入れる > 「OK」をクリック
4.列を並び替えて、閉じて読み込む
「結合の種類」の使い分け
結合の種類は6つあります。ざっくり分けると、
●内部 …キー項目が一致している行だけ残す
●外部 …キー項目が一致していなくても行を残す
●左 …マージ画面で上にあるテーブル(最初のテーブル)
●右 …マージ画面で下にあるテーブル(2番目のテーブル)
- 左外部(最初の行すべて、および2番目の行のうち一致するもの)
左側(マージ画面の上)のテーブルの全てのデータと、右側(マージ画面の下)のテーブルに一致するデータを表示します。左側のテーブルにないデータはNULLになります。 - 右外部(2番目の行すべて、および最初の行のうち一致するもの)
右側(マージ画面の下)のテーブルの全てのデータと、左側(マージ画面の上)のテーブルに一致するデータを表示します。右側のテーブルにないデータはNULLになります。 - 完全外部(両方の行すべて)
両方のテーブルの全てのデータを表示し、一方にないデータはNULLになります。全体を網羅する結合です。 - 内部(一致する行のみ)
両方のテーブルに存在する一致するデータのみを表示します。共通部分だけが残ります。 - 左反(最初の行のみ)
左側(マージ画面の上)のテーブルのデータのうち、右側(マージ画面の下)のテーブルにないデータのみを表示します。差異を抽出するのに使います。 - 右反(2番目の行のみ)
右側(マージ画面の下)のテーブルのデータのうち、左側(マージ画面の上)のテーブルにないデータのみを表示します。差異を抽出するのに使います。
表の結合(縦方向)
9月・10月・11月の売上データを結合して新規クエリを作成します。
1.PowerQueryエディタ―を開き、クエリの追加 >「クエリを新規クエリとして追加」をクリックする
2.「3つ以上のテーブル」を選択。追加するテーブルの方に「売上9月」「売上10月」「売上11月」を入れて「OK」をクリックする
3.9月~11月のデータがまとまった新規クエリが追加された
以上です。