SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Power Query で Excel の特定のセルの情報を使用してクエリを実行

leave a comment

Power Query (Power BI アドイン for Excel) で、特定のセルに入力されている情報を元にクエリを実行する方法を。

例によって手探りなので、違う方法もあるかと。

Excel で、以下の画像のようにテーブル名を指定するセルを用意し、名前を付けた状態にしています。

image

この入力内容を Power Query のクエリで使用して、入力した内容を元にデータを取得するというのが今回の仕組みです。

最初に、Excel のセルの内容を Power Query で読み取り、リストとして取得しています。

let
    var = Excel.CurrentWorkbook(){[Name="TableName"]}[Content],
    T= Table.PromoteHeaders(var)
in
    T[TableName]

 

image

これで「検索対象」というリストに、検索したいテーブル名が取得されている状態となります。

次に、この検索対象を使用して SQL Server に対してクエリの実行を行います。

let
    Source = Sql.Database("localhost", "master"),
    T= Value.NativeQuery(
            Source,
            "SELECT * FROM sys.all_objects
            WHERE name LIKE @TName + '%'",
            [TName=#"検索対象"{0}]) ,
    ret = if (Table.RowCount(T) > 100) then
            error "#(cr,lf)取得件数が 100 件を超えました。#(cr,lf)条件を変更し、件数を減らしてください"
        else
            T
in
    ret

image

今回は、えろす師匠を讃えるため「ひさし」というクエリを作成し、その中では上記のような記述をしています。

先ほど作成した「検索対象」というリストの最初のデータを取得して、それをパラメーター化クエリのパラメーターとして渡して、クエリの実行を行っています。

今回のクエリは Excel にデータの取り込みを行っているのですが、Excel に取り込む時はデータ件数の上限を意識しておいた方がよいかと思いますので、実行結果が 100 件を超えている場合はエラーにするようなクエリとしています。

これで、Power Query としての設定は完了です。

今回は Excel で指定した内容で、接続の最新化を行いたいため、以下のような形でボタンを一つ配置しています。

このボタンをクリックすることで、入力をしたテーブル名でクエリを実行し、データの取得が行われます。

image

ボタンには以下のようなコードが実行されるように設定をしています。

今回は、データモデルではなく、ワークシートに出力を行うための接続でクエリを実行していますので、接続の最新化を実施するような VBA を使用しています。

Sub ボタン1_Click()
    For Each Con In ThisWorkbook.Connections
        If (InStr(Con.Name, "ひさし") > 0) Then
            Con.Refresh
        End If
    Next
End Sub

接続の場合、名前をインデクサとして使用してアクセスができますが、接続の場合は、インデックス番号以外ではピンポイントにアクセスできなさそうでしたらので、Foreach で回して取得しています。

データモデルを使用している場合は、以下のようなスクリプトでピンポイントでモデルをリフレッシュすることができます。

ActiveWorkbook.Model.ModelTables("ひさし").Refresh

 

Power Query や PowerPivot の構成に関しては、VBA のオブジェクトモデルを確認しておくとよさそうです。

Queries オブジェクト (Excel)

ModelTable オブジェクト (Excel)

 

これで準備は完了です。

実際に実行をした際の動作が以下になります。

Power Query

Excel に情報を入力し、「クエリの更新」ボタンを押すことで、入力した内容で SQL Server に情報を取得し、ワークシートに表示している内容が変化します。

100 件を超える場合には、エラーが発生するというような処理も動作していることが確認できるかと。

データを分析する前の、データモデルの作成時の方法として何かに使えるかなと。

Share

Written by Masayuki.Ozawa

2月 17th, 2017 at 11:53 pm

Posted in Power BI

Tagged with

Leave a Reply