[MySQL] Power Query for Excelからデータベースに接続する方法

「Microsoft Power Query for Excel」(以下、Power Query)をインストールしてみました。
インストール自体は、ウィザードに従ってクリックするだけなので、とても簡単です。

その後、MySQLのデータベースにも接続してみましたので、その手順を紹介します。

クエリ エディターの起動

Excelを起動すると、[POWER QUERY]メニューが追加されていました。

ODBC経由で、MySQLのデータベースに接続してみます。

[POWER QUERY]メニューから[その他のソースから]の[ODBCから]をクリックします。

データベースへの接続文字列の入力を求められます。「Microsoft Query」であればウイザードが助けてくれるところですが、これはいきなりハードルが高めですね（汗）

「Microsoft Query」で接続した時のプロパティを参考に、次のように入力して[OK]ボタンをクリックしてみます。

Driver=MySQL ODBC 5.3 Unicode Driver;SERVER=localhost;DATABASE=bitnami_redmine;PORT=3306

ユーザー名とパスワードを入力して[接続]ボタンをクリックします。

ナビゲーターが起動するので’bitnami_redmine”issues’を選択してみたところ、プレビューの評価でエラーが発生してしまいました…

「Microsoft Query」からはいつも通り接続できるので、テーブル自体に問題があるとは思えません。エラーは、うまくプレビューできないことが原因なのでしょうか？

試しにプレビューする必要がないよう、SQLステートメントで直接指定してみます。

『クエリ エディター』が起動しました！

ずらりと並んだ機能が「ETLツール」っぽいですね。左から順に「クエリの一覧」「そのクエリで抽出されるデータのサンプル」「クエリの設定」が表示されています。

抽出する列の絞り込み

Redmine内部のフィールド(root_id, lft, rgt)を、抽出対象から削除してみます。サンプルから削除したい列を選択して、[ホーム]メニューの[列の削除]をクリックすると削除できました。

Excelシートの感覚で操作できるのは、とても良いですね(^^)

列が消えると、適用したステップに「削除された列」が追加されました。クエリへの操作は、ここにステップとして記録されるんですね。

レコードのフィルタリング

抽出するレコードを、今年作成されたチケットだけにしてみます。これもExcelシートの感覚で操作できます。[created_on]の右横のフィルターマークをクリックして、[日付フィルター]の[年]の[今年]をクリックします。

この操作だけでフィルタリングされ、今年作成されたチケットだけになりました。

クエリの数式を表示

また、[ホーム]メニューの[詳細エディター]をクリックすると、クエリの設定が数式で表示されます。このエディターで、直接、クエリを編集することもできるんですね。

数式には、見慣れない関数がたくさん使われていますが、これらの数式については、次のページに解説を見つけました。ただ公開されて日が浅いためか、一部、リンク切れのページもありました。

Excelシートへの読み込み

クエリの編集が終わったら、[ホーム]メニューの[閉じて読み込む]をクリックして、Excelシートにデータを読み込みます。

読み込みが終わると、次のようにExcelシートにデータが展開されました。

まとめ

Power Queryも、Microsoft Query同様、MySQL(Redmine)に接続できます。

Microsoft Queryがクエリ編集に特化した外部ツールなのに対して、Power QueryはExcel上のユーザー操作からクエリが生成されます。

今回は、クエリエディターの「抽出する列の絞り込み」と「レコードのフィルタリング」だけで記事にしましたが、複数のクエリをマージすることで、より複雑な抽出もできることがわかりました。

Accessに慣れているので「Microsoft Query」も捨てがたいのですが、ETLツールとしての「Power Query」にも興味があるので、積極的に使っていきたいと思います。