前回はPower Queryを使う前の各種設定について話をしました。いよいよPower Queryを実際に使ってみるのですが、今回は以下のテーマについて簡単に見ていくことにします。なお、ExcelのPower Queryを使ったほうが解説しやすいので、基本的にExcelベースで話を進めていきたいと思います。
各種データソース（Excelバージョン）
記事「Power Query -いきなりデモ」で示したのですが、Power Queryは100種以上のデータソースに接続することができます。Power Queryについて知らないExcelユーザーにとっては、このデータソースの接続（以降「データの取得と変換」）は無縁の世界になりますが、Power Queryを使うユーザーはこれがなければ何もできません。最初の入り口が「データの取得と変換」になりますが、Excelユーザーにとって馴染み深いのは何と言ってもExcelの中でデータに対する処理になります。
データソースの詳細は上記記事を参照頂きたいですが、ここで重要なことはExcelのほうがPower BIよりも接続できるコネクターが少ないことです。例えば、Power BIではPDFの中身を抽出する機能がありますが、Excel版Power Queryにはまだ搭載されていません（2020年4月時点）Excel側でもきちんと対応されています。
（私見ですが）Power BIのほうが接続できるデータソースのほうが多い理由はPower BIが後発、かつスタンドアローンのアプリケーションであり、開発の自由度が高いからなのではと考えています。一方のExcelは膨大なユーザーベースを抱えており、あらゆるビジネスニーズを満たすため、誰もが使っているExcelという宿命であるがゆえに、開発の優先順位や致命的なエラーが発生した場合のリスクを分散する必要があるためではないでしょうか。（※ここはあくまで想像にすぎません）
また、以前まで両方にあったFacebookへの接続は残念ながら2020年4月から廃止となったようです。
余談ですが、Power Queryという名称はPower Queryエディタを立ち上げて初めて小さく表示されるようになっており、ExcelやPower BIの初期画面ではその名称を見つけることはできません。
これにはMicrosoftなりの考えがあったようですが、Power 〇という言葉を付けることがユーザーに圧迫感を与えてしまうのではないかという危惧があったという噂があります。ところが、Power Queryはその使い勝手の良さからあっという間にExcelのパワーユーザーの間で広まり、Power Queryという名前がいつの間にか定着してしまったのです。こうした背景からなのか、2019年にはPower Queryの専用サイトが立ち上がり、今ではMicrosoftのデータプラットフォームに関連するあらゆるサービスでPower Queryが使用できるようになっています。
データの取得
ここでは簡単にExcelにあるデータをクエリとして取得する方法について見ていきます。データが入っているスプレッドシートからそのデータをクエリとして抽出するため、同じExcelワークブック内にクエリを作成します。
オリジナルデータ
サンプルとして使用するデータは下図の通り。同じワークブックでデータをクエリ化するために、3つのパターンを用意しました。
テーブル化してクエリ化
最初のパターンはワークシートでデータが既にテーブル化されたデータをクエリ化するものですが、以下の手順で進めていきます。
- データの範囲を選択
- Ctrl + T（テーブル化）
-
「テーブルツール」>「デザイン」より、名前ボックス内の名前をTable1に変更
-
この状態でAlt + APT（ショートカットキー）でテーブルをクエリ化
- クエリエディタが自動的に開きますので、ナビケーションを展開
- Power Queryが実行したこと（①～④まで）を確認
- 「閉じて読み込む」>「閉じて次に読み込む」を選択
- 「データのインポート」で「接続の作成のみ」を選択し、Excelの右側に「クエリと接続」ウィンドーが出ているのを確認
※その他のデータのインポートの種類は次の記事にて解説
-
最初のクエリ作成が完了（※このクエリをもう一度開くときは、上図のTable1クエリをダブルクリックすればOK）
同じワークブックにあるデータをクエリ化する際、殆どの場合はテーブル化されたデータを使用します。これはテーブル化されたデータは範囲指定が簡単であり、範囲の領域にかかわらず、名前を指定すればデータを取得できるためです。
ところが、スプレッドシート上に数式等が混じったデータセットがある場合、テーブル化しないほうが便利な場合もあります。その場合には下記「名前の定義によるクエリ化」を使います。
名前の定義によるクエリ化
- データの範囲を選択
- 名前ボックスにTable2と入力
- 範囲を選択したまま、Alt + APTでクエリエディタに取り込む
- Table1とは異なり、ヘッダーはColumn1とColumn2になっているため、「ヘッダーの昇格」を行う
なお、このやり方はリボンの「ホーム」>「1行目をヘッダーとして使用」で操作していますが、下記のように、「プレビュー画面」の左上のテーブルアイコンを左クリックすることでも実現できます。
- Table1と同様に操作し、下記2つのクエリが作成されたことを確認
通常データ（テーブル化や名前の定義なし）
上記2つのやり方以外に、同じブック内からデータを抽出する方法は・・ありません。上記のデータセット（通常データ）はノーマルな形でワークシート内に格納されていますが、Alt + APTでクエリ化しようとすると、以下のように自動的にテーブル化が始まります。これは言い換えれば、最初の「テーブル化してクエリ化」と同じになりますので、その他の方法で強引にデータを取得しようにもできないわけです。
次の記事で説明しますが、同じブック内でデータをクエリ化するには範囲設定が必ず必要ですが、他のブックを参照する場合にはExcel.WorkbookとFile.ContentsというPower Query関数を使用すれば、範囲設定しなくてもシート名からデータを抽出することが可能です。ちなみに、これらの関数は自分で書くのではなく、データ抽出の操作を行った際に自動的に生成されるものですので、何も難しいことはありません。
まとめ
今回はデータソースの接続と同じブック内のデータをクエリ化する例について解説しました。ポイントは以下の3つ。
- ExcelよりもPower BIのほうが接続できるデータソースが多い
- PDFからデータを抽出したい場合はPower BIを使用すること
- 同じブック内のデータをクエリ化するには2つ方法があり、テーブル化してクエリ化のほうが使用頻度は高い
