Power Query へそのゴマAdvent Calendar 2024

Power Query へそのゴマ　第６章　パラメータとカスタム関数を使用した動的クエリ

Last updated at 2024-12-11Posted at 2024-12-05

この章では、Power Queryでパラメータとカスタム関数を活用して、柔軟性と再利用性の高い動的なデータ取得および変換を実現する方法を学びます。パラメータを使用することで、データソースやクエリの条件を柔軟に変更でき、カスタム関数を用いることで特定の処理を繰り返し行う際の効率が向上します。この技術を応用することで、より洗練されたデータ処理が可能になります。

6.1. パラメータの概要と活用法

パラメータは、Power Queryで柔軟に変数を設定するための機能で、データソースの動的な設定やクエリの条件の動的変更に役立ちます。

6.1.1. パラメータの基本概念

Power Queryにおけるパラメータは、クエリ内で使用する動的な変数であり、データのフィルタ条件やデータソースの設定値などに用いられます。パラメータを利用することで、クエリの一部を変更するだけで異なる条件のデータを取得でき、柔軟なデータ処理が可能になります。

特定の値は、コード中に記載するよりもパラメータ化するほうがメンテナス性が上がります。

接続情報：データベースに接続するためのコード
ファイルパス：環境が変わったとき、修正のためにコードを読まなくて済む
URL：Webサービスは時折URLが変更になります
日付：年や月を指定して処理を行う場合、パラメータを変更するだけで済む
マジックナンバー：説明が必要なマジックナンバーはパラメータにしておく

6.1.2. パラメータの作成と設定方法

Power Queryエディターの「ホーム」タブの「パラメータの管理」で「パラメータの新規作成」を選択し、任意の名前と値を指定してパラメータを作成します。

パラメータの設定例

名前：SelectedMonth
データ型：テキスト
現在の値："2023-01"

6.1.3. パラメータの適用

例えば、パラメータ SelectedMonth で指定した年度のデータのみを取得するフィルタリングが可能です。

let
    Source = Excel.Workbook(File.Contents("C:\Data\SalesData.xlsx"), null, true),
    SalesTable = Source{[Name="Sales"]}[Data],
    
    // パラメータをフィルタリング条件に適用
    FilteredData = 
        Table.SelectRows(
            SalesTable, 
            each Text.Start([SalesDate], 7) = SelectedMonth
        )
in
    FilteredData

6.2. カスタム関数の概要と作成方法

カスタム関数は、Power QueryのM言語を使用して定義する再利用可能な処理のブロックです。このセクションでは、カスタム関数の基本的な概念、作成方法、関数を利用することで得られるメリットについて学びます。

6.2.1. カスタム関数とは

カスタム関数は、特定の処理や計算をまとめたコードのブロックで、同じ処理を複数のクエリやデータセットに適用する際に利用されます。再利用性が高まり、複数のクエリで共通の処理を行う場合に、クエリを簡素化し、保守性が向上します。

6.2.2. カスタム関数の作成手順

詳細エディターを開き、テキストデータの前後に特定の文字列を追加する関数を作成します。関数に渡す引数は、最初に（）内に記述します。

このクエリの名前を「MyFunction」とします。

MyFunction

// テキストを受け取り、指定した接頭辞と接尾辞を追加する関数
(MyText as text) as text => "Prefix_" & MyText & "_Suffix"

引数に as textのように、引数の型を指定したり、戻り値を型指定できますが、省略して以下のように記述すrこともできます。

(MyText) => "Prefix_" & MyText & "_Suffix"

6.2.3. カスタム関数の適用方法

カスタム関数をクエリ内で呼び出し、特定の列やデータに適用します。同一の処理を複数のデータセットに適用可能です。

let
    Source = Table.FromRecords({
        [Name = "Alice"],
        [Name = "Bob"],
        [Name = "Charlie"]
    }),
    
    // カスタム関数を適用して名前を変換
    ModifiedNames = 
        Table.AddColumn(
            Source, 
            "ModifiedName", 
            each MyFunction([Name])  // カスタム関数の呼び出し
        )
in
    ModifiedNames

カスタム関数についての詳細は以下を参照。

6.3. パラメータとカスタム関数の組み合わせ

6.3.1. 動的クエリ

パラメータとカスタム関数を組み合わせることで、より柔軟で効率的な動的クエリを作成できます。

カスタム関数に複数のステップを記述するには、通常のクエリのように let in を使用します。

GetMonthlyData

// カスタム関数：指定された月のデータを取得
(data as binary, month as text) as table =>
let
    Source = Excel.Workbook(data, true, true),
    
    // シート名「Sales」からデータを取得
    SalesTable = Source{[Name="Sales"]}[Data],  
    
    // 「SalesDate」の年月日の頭7桁が month と一致する行を抽出
    FilteredData =
        Table.SelectRows(
            SalesTable,
            each Text.Start([SalesDate], 7) = month  
        )
in
    FilteredData

// ファイルの場所と抽出する年月をパラメータで指定
let
    Source = File.Contents(DataFilePath),
    Result = GetMonthlyData(Source, SelectedMonth)
in
    Result

この例では、パラメータ DataFilePath で示されたエクセルファイルと、パラメータ SelectedMonth を GetMonthlyData 関数に渡すことで、指定した月のデータのみを取得します。

6.3.2. クエリを関数に変換

パラメータを使用したクエリを関数に変換することができます。

Query1

// パラメータ SelectedMonth を使用したクエリ
let
    Source = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx"), true, true),
    
    // シート名「Sales」からデータを取得
    SalesTable = Source{[Name="Sales"]}[Data],  
    
    // 「SalesDate」の年月日の頭7桁が SelectedMonth と一致する行を抽出
    FilteredData =
        Table.SelectRows(
            SalesTable,
            each Text.Start([SalesDate], 7) = SelectedMonth
        )
in
    FilteredData

上記の Query1 の名前を右クリックし、「関数の作成...」を選択し、新しい関数名を入力すると、クエリから関数が作成されます。