Zenn
🍓

PowerQuery でセルの値をパラメーター変わりに使う

2023/03/25に公開
Excel
SQL
#
powerquery
#
パラメーター
#
m
tech

PowerQueryのパラメーター

PowerQueryにパラメーターというものがあるが、
使い勝手を知らない人には説明しづらいため、
セルに入力した値をPowerQuery内のSQLクエリに反映する方法を探した。

パラメーターをつくる

入力セルを決める

まずはパラメーターを入力する箇所を作り、テーブル化する。
「ID」がヘッダー、「neko.cat」は任意の値。

セルを取り込む

テーブルにカーソルを合わせて データ⇒テーブルまたは範囲から を押すと、
PowerQueryエディタが開き、できたクエリ内の数式に以下が入力される。

= Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content]

これではテーブルとして認識されているため、以下のようにする
0番目（テーブルの1行目）だけを取り出した状態になる。

= Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content]{0}[ID]

SQLを用意する

実行したいSQLを用意して、パラメーターに使いたい箇所に任意の文字列を入力する。
今回はテーブルもここで作成し、WHEREの「--PARAM--」の位置にパラメーターを使う。

SELECT
    *
FROM
    (values
    ('NEKO'),('CAT'),('neko.cat'),('cat.neko')
    ) as cat_neko_table (nekocat_id)
WHERE
    nekocat_id = '--PARAM--'

クエリに取り込む

上記で用意してきたものを組み合わせて、１つのクエリにしていく。

let
/*先ほど取り込んだセルの式を入れる*/
PARAM = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content]{0}[ID],

/*先ほど書いたSQLを入れる*/
BASESQL = "
SELECT
   *
FROM
   (values
   ('NEKO'),('CAT'),('neko.cat'),('cat.neko')
   ) as cat_neko_table (nekocat_id)
WHERE
   nekocat_id = '--PARAM--'",

/*「--PARAM--」の文字列を、PARAM（セルの値）にReplaceする*/
SQL = Text.Replace(BASESQL, "--PARAM--", PARAM),

ソース = Sql.Database("サーバー名", "データベース名", 
       [Query=SQL])
/* ODBCなら「ソース = Odbc.Query("dsn=DSN", SQL)」 */
in
   ソース

閉じて読み込みデータを出力すると、パラメーターに入力された内容で
条件付けがされていることがわかる。

パラメーターを「CAT」に変えてデータ更新（すべて更新）してみると、出力データも変わった。

結果

日付の期間指定をする場合は2つパラメーターが必要である。
PowerQueryの相対日付フィルターなどでよいのでは？と思う場面もあるが、
データ量が多い場合は、SQLから期間指定をしたほうが都合がよい時がある。

難点としては、毎回更新する度に「ネイティブクエリ実行の許可」を
しなくてはならない。

本来パラメーターとして用意されている機能も、
よく調べたらユーザーライクに使えるものなのかもしれない。

Discussion