Power Query統合ガイド

Excel Power QueryやPower BI DesktopでSURP MDMに接続し、マスターデータを直接取得・変換する方法を説明します。

---

📋 概要

Power Queryは、Excelに組み込まれたデータ接続・変換ツールです。SURP MDMのAPIに接続することで、マスターデータを取得し、フィルター・結合・集計などの高度なデータ変換を行えます。

Power Queryでできること

• データの取得と変換: APIから直接データを取得し、必要な形に加工
• 複数マスターの結合: 従業員と部署など、複数のマスターを組み合わせる
• フィルターと並び替え: 条件に合ったデータのみを抽出
• データの自動更新: 定期的に最新データに更新
• 再利用可能なクエリ: 一度作成したクエリを何度でも実行

---

🎯 前提条件

必要なプラン

• Excel統合API: Professional以上
• OData API: Professional以上
• 基本API: すべてのプラン

必要なツール

• Microsoft Excel 2016以降（Power Query組み込み）
• または Power BI Desktop

必要な権限

• 読み取り権限（read スコープ）以上のAPIキー

---

🔑 APIキーの取得

1. SURP MDM管理画面にログイン
2. 設定 > APIキー に移動
3. 新しいAPIキーを作成 をクリック
4. 以下を設定:
   • 名前: Power Query接続用
   • スコープ: read（読み取りのみ）
   • 有効期限: 必要に応じて設定（推奨: 1年）
5. 生成されたAPIキーをコピー

例:

sk_live_abcd_1234567890abcdef1234567890abcdef

⚠️ 重要: APIキーは一度しか表示されません。安全な場所に保存してください。

---

🚀 接続方法

方法1: データカタログから接続（推奨）

データカタログAPIを使用すると、利用可能なマスター一覧を確認できます。

ステップ1: データカタログに接続

1. Excel を開く

2. 「データ」タブ → 「データの取得」 → 「その他のデータソースから」 → 「Web から」

3. URLを入力:

   https://mdm.surp.tech/api/v1/catalog

4. 「詳細設定」をクリック

5. 「HTTPリクエストヘッダーパラメーター」を追加:

   • ヘッダー名: Authorization
   • 値: Bearer sk_live_あなたのAPIキー

6. 「OK」をクリック

ステップ2: マスター一覧を表示

Power Queryエディターが開きます。

1. 「masters」フィールドを展開
2. 以下の列を選択:
   • master_type（マスタータイプ）
   • master_type_label（表示名）
   • endpoints（エンドポイント情報）
3. 「閉じて読み込む」

利用可能なマスター一覧が表示されます。

---

方法2: Excel統合APIに直接接続

特定のマスタータイプが分かっている場合は、直接接続できます。

ステップ1: 接続を作成

1. 「データ」タブ → 「データの取得」 → 「Web から」

2. URLを入力:

   https://mdm.surp.tech/api/v1/excel/department

   ※ department の部分を取得したいマスタータイプに変更

3. 「詳細設定」 → 「HTTPリクエストヘッダー」:

   • ヘッダー名: Authorization
   • 値: Bearer sk_live_あなたのAPIキー

4. 「OK」をクリック

ステップ2: データを展開

Power Queryエディターで以下の操作を行います:

1. 「value」フィールドを展開
   • 「List」と表示されている列をクリック
   • 「テーブルに変換」を選択
2. レコードを展開
   • 必要な列を選択（例: departmentCode, departmentName）
3. データ型を変換
   • 日付列は type date に変換
   • 数値列は type number に変換
4. 「閉じて読み込む」

---

方法3: OData接続

OData v4プロトコルを使用すると、Power Queryが自動的にスキーマを検出します。

ステップ1: ODataフィードに接続

1. 「データ」タブ → 「データの取得」 → 「ODataフィード」

2. URLを入力:

   https://mdm.surp.tech/api/v1/odata

3. 「詳細設定」を選択

4. 「HTTPリクエストヘッダー」:

   • ヘッダー名: Authorization
   • 値: Bearer sk_live_あなたのAPIキー

5. 「OK」をクリック

ステップ2: エンティティを選択

Power Queryが自動的にメタデータを読み込み、利用可能なエンティティ（マスター）一覧を表示します:

• department（部署マスター）
• employee（従業員マスター）
• product（製品マスター）

選択したエンティティをクリックして、「読み込み」 または 「変換」

---

📊 Power Query（M言語）の実践例

例1: 基本的なデータ取得

let
    // APIからデータ取得
    Source = Json.Document(Web.Contents(
        "https://mdm.surp.tech/api/v1/excel/department",
        [Headers=[Authorization="Bearer sk_live_..."]]
    )),

    // value配列を展開
    ValueList = Source[value],
    TableFromList = Table.FromList(ValueList, Splitter.SplitByNothing()),
    ExpandRecords = Table.ExpandRecordColumn(TableFromList, "Column1",
        {"id", "departmentCode", "departmentName", "status"}
    ),

    // アクティブな部署のみフィルタ
    FilterActive = Table.SelectRows(ExpandRecords,
        each [status] = "active"
    )
in
    FilterActive

---

例2: リレーションフィールドの展開

従業員データで、部署名を自動展開します。

let
    Source = Json.Document(Web.Contents(
        "https://mdm.surp.tech/api/v1/excel/employee?flatten=true",
        [Headers=[Authorization="Bearer sk_live_..."]]
    )),

    ValueList = Source[value],
    TableFromList = Table.FromList(ValueList, Splitter.SplitByNothing()),
    ExpandRecords = Table.ExpandRecordColumn(TableFromList, "Column1",
        {"employeeCode", "name", "email", "department_display", "hireDate"}
    ),

    // 列名を日本語に変更
    RenameColumns = Table.RenameColumns(ExpandRecords, {
        {"employeeCode", "社員番号"},
        {"name", "氏名"},
        {"email", "メールアドレス"},
        {"department_display", "所属部署"},
        {"hireDate", "入社日"}
    })
in
    RenameColumns

ポイント: flatten=true パラメータで、department_display のような参照先の表示名が自動追加されます。

---

例3: 複数マスターの結合

従業員マスターと部署マスターを結合します。

let
    // 従業員データ
    Employees = Json.Document(Web.Contents(
        "https://mdm.surp.tech/api/v1/excel/employee",
        [Headers=[Authorization="Bearer sk_live_..."]]
    ))[value],
    EmployeeTable = Table.FromList(Employees, Splitter.SplitByNothing()),
    ExpandEmployees = Table.ExpandRecordColumn(EmployeeTable, "Column1",
        {"employeeCode", "name", "department", "email"}
    ),

    // 部署データ
    Departments = Json.Document(Web.Contents(
        "https://mdm.surp.tech/api/v1/excel/department",
        [Headers=[Authorization="Bearer sk_live_..."]]
    ))[value],
    DepartmentTable = Table.FromList(Departments, Splitter.SplitByNothing()),
    ExpandDepartments = Table.ExpandRecordColumn(DepartmentTable, "Column1",
        {"id", "departmentCode", "departmentName", "location"}
    ),

    // 結合（department IDで）
    MergedTables = Table.NestedJoin(
        ExpandEmployees, {"department"},
        ExpandDepartments, {"id"},
        "DeptInfo",
        JoinKind.LeftOuter
    ),

    // 部署情報を展開
    ExpandDeptInfo = Table.ExpandTableColumn(MergedTables, "DeptInfo",
        {"departmentCode", "departmentName", "location"}
    )
in
    ExpandDeptInfo

---

例4: 条件付きフィルター

複数の条件でデータをフィルターします。

let
    Source = Json.Document(Web.Contents(
        "https://mdm.surp.tech/api/v1/excel/product",
        [Headers=[Authorization="Bearer sk_live_..."]]
    ))[value],
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
    ExpandRecords = Table.ExpandRecordColumn(TableFromList, "Column1",
        {"productCode", "productName", "price", "stock", "status"}
    ),

    // 価格が1000円以上
    FilterPrice = Table.SelectRows(ExpandRecords,
        each [price] >= 1000
    ),

    // 在庫あり
    FilterStock = Table.SelectRows(FilterPrice,
        each [stock] > 0
    ),

    // 有効な商品のみ
    FilterActive = Table.SelectRows(FilterStock,
        each [status] = "active"
    )
in
    FilterActive

---

例5: 大量データのページネーション

10,000件を超えるデータを取得する場合は、ページネーションが必要です。

let
    // ページネーション関数
    GetAllPages = (master_type as text, page_size as number) =>
    let
        GetPage = (offset as number) =>
        let
            url = "https://mdm.surp.tech/api/v1/excel/" & master_type
                & "?limit=" & Number.ToText(page_size)
                & "&offset=" & Number.ToText(offset),
            response = Json.Document(Web.Contents(url,
                [Headers=[Authorization="Bearer sk_live_..."]]
            )),
            data = response[value],
            has_more = response[has_more]
        in
            {data, has_more},

        // 再帰的に全ページ取得
        GetAllPagesImpl = (offset as number, accumulated as list) =>
        let
            page = GetPage(offset),
            current_data = page{0},
            has_more = page{1},
            new_accumulated = List.Combine({accumulated, current_data})
        in
            if has_more then
                @GetAllPagesImpl(offset + page_size, new_accumulated)
            else
                new_accumulated
    in
        GetAllPagesImpl(0, {}),

    // 全従業員データ取得（1000件ずつ）
    AllEmployees = GetAllPages("employee", 1000),
    ConvertToTable = Table.FromList(AllEmployees, Splitter.SplitByNothing()),
    ExpandRecords = Table.ExpandRecordColumn(ConvertToTable, "Column1",
        {"employeeCode", "name", "department_display"}
    )
in
    ExpandRecords

---

🔄 データの自動更新

更新設定の方法

1. Power Queryで接続を作成
2. 「閉じて読み込む」をクリック
3. データが読み込まれたテーブル内を右クリック
4. 「テーブル」→「外部データのプロパティ」
5. 以下の設定を有効化:
   • ✅ 「ファイルを開くときにデータを更新する」
   • ✅ 「バックグラウンドで更新する」
   • 更新間隔: 60分（推奨）

Power BIでの自動更新

Power BI Desktopでは、スケジュールされた更新を設定できます:

1. Power BI Serviceにワークスペースを作成
2. レポートを発行
3. データセットの設定 → スケジュールされた更新
4. 更新頻度を設定（例: 1日1回、朝8時）

---

💡 ベストプラクティス

1. パラメータ化

APIキーやベースURLをパラメータ化すると、再利用しやすくなります。

パラメータの作成:

1. Power Queryエディターで 「パラメータの管理」
2. 新しいパラメータを作成:
   • 名前: APIKey
   • 種類: テキスト
   • 現在の値: sk_live_...

クエリでの使用:

let
    APIKey = APIKey,  // パラメータ参照
    Source = Json.Document(Web.Contents(
        "https://mdm.surp.tech/api/v1/excel/employee",
        [Headers=[Authorization="Bearer " & APIKey]]
    ))
in
    Source

---

2. エラーハンドリング

APIエラーをキャッチして、わかりやすいメッセージを表示します。

let
    Source = try Json.Document(Web.Contents(
        "https://mdm.surp.tech/api/v1/excel/employee",
        [Headers=[Authorization="Bearer sk_live_..."]]
    )) otherwise null,

    Result = if Source = null then
        #table(
            {"メッセージ"},
            {{"APIからデータを取得できませんでした。APIキーを確認してください。"}}
        )
    else if Source[value]? = null then
        #table(
            {"メッセージ"},
            {{"データが見つかりませんでした。"}}
        )
    else
        Table.FromList(Source[value], Splitter.SplitByNothing())
in
    Result

---

3. キャッシュの活用

データの更新頻度が低い場合（部署マスターなど）は、更新間隔を長く設定してAPIリクエストを削減します。

推奨間隔:

• 頻繁に変更: 15分〜30分
• 日次更新: 1日1回
• 静的マスター: 1週間に1回

---

⚠️ トラブルシューティング

Q1. 「DataSource.Error: Web.Contents が失敗しました」

原因:

• APIキーが間違っている
• ネットワーク接続の問題
• URLが間違っている

対処方法:

1. APIキーを再確認（Bearer の後にスペースがあることを確認）
2. URLが正しいか確認
3. ブラウザでURLにアクセスして動作確認

---

Q2. 「Expression.Error: トークンが無効です」

原因: M言語の構文エラー

対処方法:

1. 詳細エディターでコードを確認
2. カンマ、括弧、引用符の閉じ忘れをチェック
3. サンプルコードと比較

---

Q3. データが空（0行）

原因:

• マスタータイプが間違っている
• データが登録されていない
• value 配列の展開に失敗

対処方法:

1. マスタータイプを確認（/api/v1/catalog で確認）
2. SURP MDM管理画面でデータが存在するか確認
3. Power Queryのステップを1つずつ確認

---

Q4. レート制限エラー（429 Too Many Requests）

原因: 短時間に大量のリクエストを送信

対処方法:

1. 自動更新の間隔を長くする（推奨: 60分以上）
2. ページネーションで一度に取得する件数を減らす
3. プランをアップグレード

---

🔗 関連ガイド

• Excel統合ガイド - Excelでの基本的な使い方
• OData接続ガイド - OData v4での接続詳細
• API利用ガイド - APIの基本
• APIサンプルコード - その他の言語での実装例

---

📞 サポート

Power Query統合に関する質問は、よくある質問をご覧いただくか、システム管理者にお問い合わせください。