LoginSignup
11
15

More than 1 year has passed since last update.

@spumoni(spumoni)

(Power BI) Mから始めよう #8 〜クエリ・パラメータの各種使い方

Last updated at Posted at 2021-10-23

image.png
※この記事は、2021年10月21日に執筆しました。

Power BI の Power Query エディターで使用するクエリ・パラメータの使い方について考えます。レポートで使用する What-ifパラメーターではありません。
　クエリ・パラメータは、クエリの中の値を別の場所に記述することで、メンテナンスがしやすくなります。例えば、エクセルファイルの保存場所とか、APIのキーなどをパラメータにして、PBIXファイルを他の人に配布したり、別の環境で使いたいときなど、Power Queryの中身を書き換えることなく使えるので便利です。ただし、PCの環境変数を参照するようなことはできません。

image.png

A.クエリ・パラメータとテンプレート

image.png

１．任意の値：データソースの参照先にパラメータを使う

※参照： パラメーターを使用します。

　通常、Webからのデータを取得するのには、以下のダイアログにURLを入力します。
image.png
　Power Query エディターの表示リボンにある「パラメータ」の「常に許可」にチェックを入れます。
image.png
　すると、以下のようにパラメータを参照するアイコンが表示され、パラメータの選択、新規パラメータ作成ができるようになります。
image.png
　この設定は、オプションと設定から行うこともできます。
image.png

２．値の一覧：パラメータをドロップダウンで選択する

※参照：パラメーターのプロパティ

　パラメータの値を変更したいとき、候補の一覧から選択できると便利です。それを行うには、パラメータの新規作成で、「提案された値」を「値の一覧」にして、候補となる値をその下に張り付けます。（エクセルからペーストできます）
image.png
　該当のクエリで、テキストフィルターのステップを追加します。
image.png
　行のフィルターに、先ほど作ったパラメータを選択します。
image.png
　Power BIのメニューから「パラメーターの編集」で、先ほど登録した候補が選択できます。
image.png
　選択を変更した後は、「変更の適用」ボタンが表示されますので、それを押すと、先ほどのパラメータが適用されます。
image.png

３．クエリ：候補を別で管理する

　パラメータ値の候補を別に持つこともできます。まず、データの入力でテーブルを作成します。
image.png
　次に「リストに変換」を行います。
image.png
　先ほどのパラメータの管理で、提案された値に「クエリ」を選択すれば、今作成したリストが選択できます。
image.png
　以上で、先ほどと同じように、パラメータをドロップダウンで選択できるようになります。

候補を元のテーブルから作成する

　元のテーブルのCountryを右クリックし、「新しいクエリとして追加」を行います。
image.png
　Countryの項目がリストになって作成されますので、重複を削除します。
image.png
　このリストを、先ほどと同様にパラメータの管理で。クエリに登録します。
image.png
　元のテーブルのCountry項目のフィルターにこ登録します。
image.png
　これで、パラメータのリストを手作りしなくて出来上がりました。

テンプレート形式で保存すると、開始時にパラメータ選択ができます

※参照：Deep Dive into Query Parameters and Power BI Templates

このファイルを、Power BIテンプレートファイル（拡張子 .pbit)で保存します。
image.png
　このテンプレートファイルを開くと、最初にパラメータの選択ダイアログが表示されるようになります。
image.png

B.カスタム関数とURLパラメータでWebスクレイピング

image.png
※参照：カスタム関数の引数
※ビデオ：Use Power BI Web From Example over multiple URLs

　連続したWebページのデータを取得したいとき、１ページづつ取得していくのもいいのですが、もっと簡単に取得する方法が欲しいです。テーブルの値をパラメータにして、関数に渡すことで、URLを書き換えて取得する方法を説明します。
　以前書いた記事も参照してください。※Mから始めよう #6 〜Power Queryの関数を作成する

１．注意事項；Webスクレイピングの注意点

　今回行う作業はWebスクレイピングという行為で、サイトによっては禁止されていることがあります。利用規約が書かれている場合は、確認してから利用しましょう。
　利用規約に書かれている中で、気を付けなければならない点の１つ目は、利用方法についてです。Webで一般公開されているデータを閲覧することに関しての制限はありませんが、利用方法については著作権法による制限が発生することがあります。公開に際しては、データの入手先についての記載を求めているものもあります。また、会員制限定で提供されている情報の場合、許可なく会員外に公開することはできません。その他、音声、映像を違法なサイトからダウンロードすると、処罰の対象となります。
　２点目は、アクセス方法です。Webスクレイピングなどの機械的なデータ取得が禁止されている場合があります。高負荷を与えてサービスに支障が出た場合、訴えられる可能性もあります。比較的新しいサーバーの場合、高負荷に耐えることができますが、過信してはいけません。一度に大量のアクセスが発生してDDoS攻撃とみなされないよう、１ページ取得ごとにインターバルを設けるというのが一般的です。
　robots.txtを読めという話題もありますが、あれは基本的にGooglebotなどの検索サイトが使っているロボット向けのファイルです。ロボットが広く巡回することは、クローリングと言ってますね。

２．カスタム関数の作成

　前置きは以上にして、本題に入ります。今回は、気象庁のホームページから気象情報をダウンロードします。利用規約は、以下の通りになっています。
　※気象庁ホームページについて

　今回使用する「過去の気象データ検索」は、1月ごとにデータが表示されています。１年のデータを取得するには、これを１２回行う必要があります。

（※注）今回は、説明のために月ごとのデータを取得していますが、CSV形式でダウンロードも可能です。データが必要な場合は、ダウンロードサービスをご利用ください。
※過去の気象データ・ダウンロード

image.png

　最初に、２０２０年１月のデータを取得し、データを整形して、クエリ名を「長野県上田市」にします。
image.png

　このクエリを詳細エディタで開き、１行目に引数を指定し、URLの中身を書き換えます。

変更前
let
    ソース = Web.BrowserContents("https://www.data.jma.go.jp/obd/stats/etrn/view/daily_a1.php?prec_no=48&block_no=0402&year=2020&month=1&day=&view="),
変更後
(Year, Month) =>
let
    ソース = Web.BrowserContents(
                "https://www.data.jma.go.jp/obd/stats/etrn/view/daily_a1.php?prec_no=48&block_no=0402&" & 
                "year=" & Number.ToText(Year) & "&month=" & Number.ToText(Month) & "&day=&view="),

　日付の部分で、年月をリテラルで入力しているので、ここも変えてやります。

変更前
    追加されたプレフィックス = Table.TransformColumns(置換されたエラー, {{"日", each "2020/1/" & Text.From(_, "ja-JP"), type text}}),
変更後
    追加されたプレフィックス = 
        Table.TransformColumns(
            置換されたエラー, 
            {
                {"日", 
                 each Number.ToText(Year) & "/" & Number.ToText(Month) & "/" & Text.From(_, "ja-JP"),
                 type text
                }
            }
        ),

　これで、クエリは関数に代わり、以下のような画面が表示されます。
image.png

年月テーブルの作成

　年、月の入ったてーぶろを作成します。最初は、からのクエリから2020年１月のみで作ります。

let
    source = Table.FromValue(2020, [DefaultColumnName = "Year"]),
    step1 = Table.AddColumn(source, "Month", each {1}),
    step2 = Table.ExpandListColumn(step1, "Month")
in
    step2

image.png

列の追加で「カスタム関数の呼び出し」

　列の追加のリボンから、「カスタム関数の呼び出し」を選択し、関数クエリとパラメータを入力します。
image.png
　以下のように、データがテーブルとして追加されます。
image.png
　これを、項目名の右の左右に開くボタンを押して、展開します。
image.png

image.png
　以下のようなテーブルが出来上がります。YearとMonthの項目を削除して、年月日を日付、残りを10進数と、型を整えます。

３．遅延の設定

　今は、1ページのみの呼び出しでしたが、複数のページを読み込むためには、遅延を設定します。
　詳細エディタを開いて以下ように、「呼び出されたカスタム関数」のステップを変更します。遅延は1秒以上入れるのが望ましいので、今回は2秒にしておきました。

let
    source = Table.FromValue(2020, [DefaultColumnName = "Year"]),
    step1 = Table.AddColumn(source, "Month", each {1}),
    step2 = Table.ExpandListColumn(step1, "Month"),
    // 変更前
    //    呼び出されたカスタム関数 = Table.AddColumn(step2, "長野県上田市", each 長野県上田市([Year], [Month])),
    //
    // Function.InvokeAfterで、取得間隔を5秒に設定
    呼び出されたカスタム関数 = 
        Table.AddColumn(
            step2, 
            "長野県上田市", 
            each Function.InvokeAfter(()=> 長野県上田市([Year], [Month]), #duration(0,0,0,2))
        ),
    #"展開された 長野県上田市" = Table.ExpandTableColumn(呼び出されたカスタム関数, "長野県上田市", {"年月日", "降水量(mm)", "最高気温", "最低気温", "降雪(cm)"}, {"年月日", "降水量(mm)", "最高気温", "最低気温", "降雪(cm)"}),
    削除された列 = Table.RemoveColumns(#"展開された 長野県上田市",{"Year", "Month"}),
    変更された型 = Table.TransformColumnTypes(削除された列,{{"年月日", type date}, {"降水量(mm)", type number}, {"最高気温", type number}, {"最低気温", type number}, {"降雪(cm)", type number}})
in
    変更された型

１年分のデータ取得

　クエリのステップ1を以下のように変更することで、1年分のデータが取得できます。

step1 = Table.AddColumn(source, "Month", each {1..12}),

以上で2年分のデータが取得できますので、最後にPower BIのレポートでビジュアルを作成します。

image.png

Ｃ．動的Ｍクエリ・パラメータ

※参照： Power BI Desktop の動的 M クエリ パラメーター (プレビュー)
※ビデオ：Power BI: Dynamic M query parameters

　Power Queryの中で、パラメータを使用してデータを作成するのは、コンテンツの編集権限が必要です。しかし、動的クエリ・パラメータを使用すると、閲覧者がスライサーやフィルターを使用してPower Queryの動作を変えることができます。また、Power Queryでデータをフィルターするので、パフォーマンスの向上が期待できます。

　ただし、一般にこの機能が必要なケースはあまりないと思われます。Azure Data Explore、Snowflake、Google BigQueryなどの一部のソースで役に立つと思われます。

　動的クエリパラメータを使うには、DirectQueryで接続している必要があります。（サポートされていないDirectQuery接続もあります）加えて、下記のオプションの設定をしなければなりません。
image.png

　まず、Direct Queryでデータを取得し、パラメータを使った処理を行います。

image.png

　スライサーとするテーブルを作成します。

image.png

　Power Queryエディターを閉じるときに以下のような警告が出ます。

image.png

スライサーに設定したテーブルの項目を選択肢、プロパティの詳細を開くと、**「パラメーターにバインド」**という項目が表示されます。表示されない場合は、接続がDirect Queryになっているか、オプションのチェックが入っているか確認してください。

image.png

　**「パラメータにバインド」**の項目に、パラメータの名前を選択すると、また警告が表示されます。

image.png

　レポートで、スライサーとテーブルを作成します。

image.png

　スライサーの選択を変更すると、テーブルが変更になります。

image.png

複数選択、すべて選択

　モデルビューで設定するプロパティで、「パラメーターにバインド」の下に**「複数選択」と「すべて選択」**のスイッチがあります。使用すると、パラメータはリスト形式で渡されますので、Power Queryの式を変更する必要があります。

変更前
let
    ・・・（省略）
    フィルターされた行 = Table.SelectRows(削除された他の列, each [crcd9_country] = Country)
in
    フィルターされた行
変更後
let
    ・・・（省略）
    フィルターされた行 = 
        if Type.Is(Value.Type(Country), List.Type) then
            if (List.Contains(Country, "__SelectAll__")) then    // すべて選択
                削除された他の列
            else                                                 // 複数選択
                Table.SelectRows(
                    削除された他の列, 
                    each List.Contains(Country, [crcd9_country])
                )
        else                                                     // 単一選択
            Table.SelectRows(
                削除された他の列, 
                each [crcd9_country] = Country
            )
in
    フィルターされた行

注意事項

※参照：Microsoft Docs 潜在的なセキュリティ リスク、考慮事項と制限事項

  • この機能は、現時点でプレビュー中です。Microsoft Docsの注意事項をよく読んでから試用してください。
  • この機能を使用すると、インジェクション攻撃のリスクリが生じる可能性があります。
  • この機能を使用すると、各種の制限が発生します。

関連記事

11
15
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
11
15