Power Query でデータベースを10倍使いこなす方法

データベースを使いこなすことができるとより高度な業務ができるようになります。しかし、データベースの活用は一般的にはハードルが高くなかなか手が出ません。

Power Query はデータベースからのデータ取得もマウス操作で簡単にできるので、かなりハードルが低くなったと言えます。

しかし、Power Query でデータベースを利用し始めると、デフォルトの機能では限界があることに気付きます。それは以下のような事です。

この記事では Power Query のこの欠点を解消して、データベースをより柔軟に活用する方法をご紹介します。

結論から言えば、対策は問題点の裏返しで、以下のようになります。

データベース活用上の問題点

データベースには大量のデータが蓄積されており、通常ではそのデータは日々更新されています。

例えば営業で受注した内容をデータベースで管理していたとすれば、毎日のように新たな受注の情報がデータベースに登録されているはずです。Power Query でこのデータを活用しようとする場合、デフォルトの機能では受注のデータを全てダウンロードして処理することになります。

例えば、ある支店の受注の状況をグラフにしたいとか、今月の受注の状況をグラフにしたいというようにデータの一部が対象となる場合は、全てのデータをダウンロードしたり処理するのは、無駄が多いことになります。

更に、データベース上のデータが巨大である場合は、全てのデータをダウンロードすること自体が現実的ではなくなってしまいます。そうなると、Power Query ではデータベースのデータを処理できないということになってしまいます。

対策１　ユーザーのインプットをSQLクエリに反映する

この記事ではデータベースには SQL Server を活用する前提で解説します。しかし、他のデータベースであってもSQLのクエリを変えるという考え方は同じように活かすことができると思います。

通常では Power Query は一度作成したクエリの動作を変えるには Power Query エディターで編集する必要があります。

しかし、これをセルに入力した値を SQLクエリに反映する工夫を加えます。

Power Query でセルの値を取得する

Power Query エディターを操作するだけではセルの値を取得することはできません。しかし、M関数式を直接記述することで可能となります。

操作内容

Power Query エディターの詳細エディターで以下のクエリを作成して下さい。

let
    val = Excel.CurrentWorkbook(){[Name="INPUT_CELL"]}[Content]{0}[Column1]
in
    val

解説

このクエリを実行すると、”INPUT_CELL” と名前をつけたセルから値を取得します。ポイントは セルに名前をつけること です。実質的にはたった1行のM関数式でセルの値を取得することができます。

SQLクエリに反映する

セルの値をSQLクエリに反映します。目標は下記のSQLクエリを Power Query で実行することです。このSQLクエリでは支店名がセルの値に一致するデータのみをデータベースから取得します。

select 支店名, 日付, 受注額 from 受注TABLE where 支店名 = '<セルの値を入れる>'

操作内容

では、Power Query のコードはどう書けば良いでしょうか？再び Power Query エディターの詳細エディターを開いて編集します。

let
    支店名 = Excel.CurrentWorkbook(){[Name="支店名セル"]}[Content]{0}[Column1],
    sql = "select 支店名, 日付, 受注額 from 受注TABLE where 支店名 ='" & 支店名 & "'",
    ソース = Sql.Database("SERVER_NAME\SQLEXPRESS","DB_NAME",[Query=sql])
in
    ソース

解説

このコードの2行目で「支店名セル」と名前をつけたセルから値を「支店名」という変数に取り込みます。

続いて、3行目でsqlという変数に上記のSQLクエリ文の内容を代入します。

4行目は SQL Server に SQLクエリを実行させるものです。SERVER_NAME や、DB_NAME などは環境に合わせて設定して下さい。

対策1 まとめ

以上でユーザーニーズに合わせてデータベースから必要なデータのみを取得する Power Query を作成できました。

これでPower Query からデータベースのデータをより軽快に利用できるようになります。是非活用してみて下さい。

対策2　複数選択の条件に対応する

対策1の内容を応用すれば基本的にはどんなことにも対応できますが、ユーザーが１つの選択条件に対して複数選択する場合の効率的な処理方法をご紹介します。

例えば、上記の例で支店の数が100以上もあって、それらの中からユーザーがいくつか選択した支店のデータを取得したい場合は対策1のやり方だとかなり面倒になります。ユーザーが選択する支店の数がいくつになるのか分かりませんのでその辺りの対応が難しいのです。

”where in” をSQLクエリで使う

例えば以下のようなSQLクエリを実行すると、複数の支店のデータを取得することができます。

select 支店名, 日付, 受注額 from 受注TABLE where 支店名 in ('横浜支店','名古屋支店','神戸支店','広島支店')

“where in” の後にカンマ区切りで支店名が並んでいます。このCSV形式の文字列を Power Query で作成できればこれが実現できることになります。

Power Query でCSV形式の文字列を作成する

ユーザーが入力する支店名の数がいくつであってもその数に合わせてCSV形式の文字列を作る必要があります。このためにExcelのテーブルを活用します。

操作内容

まず列名が「支店名」という列が一つだけのテーブルを作成し、テーブルの名前を「支店名テーブル」としておきます。

テーブルにはいくつか支店の名前を入力しておきます。

Power Query エディターの詳細エディターで以下のコードを記載します。

let
    ソース = Excel.CurrentWorkbook(){[Name="支店名テーブル"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"支店名", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "dummy", each "A"),
    CSV = "'" & Table.Group(追加されたカスタム, {"dummy"}, {{"STR", each Text.Combine([支店名],"','")}})[STR]{0} & "'"
in  
    CSV

解説

このクエリで入力した支店名がCSV形式の文字列に変換されます。

ポイントは5行目のグループ化の処理の部分です。通常、グループ化では行数や合計値を取得することになりますが、「Text.Combine」関数を使用することで文字列を結合することが可能です。

この場合は支店名の文字列を結合します。更に間に入る文字列を「’,’」 と指定することで、シングルクオテーションで囲われた支店名のCSV形式文字列とすることができます。

SQLクエリに反映する

前の項までできればほぼ出来たようなものですがこれを完成させます。以下のようにSQLクエリの前半部分にCSV文字列を結合してSQLクエリを完成させます。

let
    ソース = Excel.CurrentWorkbook(){[Name="支店名テーブル"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"支店名", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "dummy", each "A"),
    CSV = "'" & Table.Group(追加されたカスタム, {"dummy"}, {{"STR", each Text.Combine([支店名],"','")}})[STR]{0} & "'",
    sql = "select 支店名, 日付, 受注額 from 受注TABLE where 支店名 in (" & CSV & ")",
    ソース = Sql.Database("SERVER_NAME\SQLEXPRESS","DB_NAME",[Query=sql])
in
    ソース

対策2 まとめ

以上で複数選択の条件に対応したデータベース・データ取得の Power Query を作成することができました。

対策1の方法と合わせて活用することでデータベースから自在にデータを取得することができるようになると思いますので、活用してみてください。

まとめ

データベースは業務で活用できると非常に強力なツールです。Excelの Power Query はデータベースのデータも簡単に扱うことができます。しかし、デフォルトの機能ではユーザーのニーズに合わせたデータを取得することは難しく、一工夫が必要です。

この記事では、Power Query の詳細エディターで数行のM関数式を編集することでデータベースから自在にデータを取得する方法をご紹介しました。

今回紹介したノウハウ使うとExcelとデータベースを連携させて強力な業務ツールを作成できるようになります。

是非皆さんも試しにデータベースを活用した仕組みを構築してみて下さい。きっとこれまで苦労していた業務が一変してしまうかもしれませんよ。