「Power Query for Excel」を使ってMySQL(Redmine)に接続

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

その後、MySQL(Redmine)にも接続してみましたので、その手順を紹介します。

スポンサーリンク

クエリ エディターの起動

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

MySQL(Redmine)に接続してみます。[POWER QUERY]メニューから[その他のソースから]の[ODBCから]をクリックします。

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

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

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

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

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

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

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

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

抽出する列の絞り込み

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

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

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

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

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

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

クエリの数式を表示

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

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

Excelシートへの読み込み

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

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

まとめ

「Microsoft Query」と同じように、「Power Query」でもMySQL(Redmine)に接続できることが確認できました。

「Microsoft Query」はAccessの操作感でクエリーを編集できるのに対して、「Power Query」はExcelの操作感でクエリを編集ができます。

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

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