概要
このドキュメントは、Power BI Desktop と Power BI サービスで外部API（connpass API v2、Doorkeeper API）を使ってデータを取得・加工する際に学んだことをまとめたものです。
1. M言語の基本ルール
変数名のルール
- 数字から始まる変数名は使えない
- ❌
90日前 = ... - ✅
過去90日 = ...
- ❌
- スペースを含む場合は
#"..."で囲む - 日本語の変数名は使用可能
関数の引数名と外側の変数名の衝突に注意
// ❌ 引数名 subdomain が外側の Query フィールド名と衝突する
GetLatestEvent = (subdomain as text) =>
Web.Contents(..., [Query=[subdomain = subdomain]])
// ✅ 引数名を sd に変更して衝突を回避
GetLatestEvent = (sd as text) =>
Web.Contents(..., [Query=[subdomain = sd]])
2. Power BI サービスでの動的クエリの制限
問題
Power BI サービスでは、URLを動的に生成するクエリ（動的データソース）は自動更新できない。
解決策：Query=[] オプションを使う
// ❌ 動的URLとみなされ更新不可
ソース = Web.Contents("https://api.example.com/events?" & "subdomain=" & P1)
// ✅ Query=[] オプションを使うことで更新可能
ソース = Web.Contents(
"https://api.example.com/events/",
[
Query=[subdomain = P1, count = "100"]
]
)
重要：Text.Combine も動的とみなされる場合がある
ベースURLは固定の文字列にして、パラメーターは Query=[] に渡す。
3. Power BI サービスでの外部API認証設定
問題
ヘッダーにAPIキーを渡す方式の外部APIは、Power BI サービスの資格情報設定で「匿名」認証が拒否される場合がある。
解決策
資格情報設定画面で以下のように設定する：
設定項目 値 認証方法 匿名 プライバシーレベル Public テスト接続をスキップする ✅ チェックを入れる ← これが重要！
APIキーはヘッダーで渡す
ソース = Json.Document(Web.Contents(
"https://api.example.com/events/",
[
Headers=[#"X-API-Key"= "YOUR_API_KEY"],
Query=[subdomain = P1, count = "100"]
]
))
4. 並列読み込みの無効化
複数のクエリが同時にAPIを叩くと、アクセス制限（429 Too Many Requests）に引っかかる。
設定方法
- 「ファイル」→「オプションと設定」→「オプション」
- 「現在のファイル」→「データの読み込み」
- 「テーブルの並列読み込み」→「同時ジョブの最大数」
- 「1（並列読み込みを無効にする）」を選択
5. APIのアクセス制限回避：Function.InvokeAfter
APIに1秒間に1リクエストの制限がある場合、Function.InvokeAfter で待機時間を入れる。
GetLatestEvent = (sd as text) as record =>
Function.InvokeAfter(
() =>
let
response = try Json.Document(Web.Contents(
"https://api.example.com/events/",
[
Headers=[#"X-API-Key"= "YOUR_API_KEY"],
Query=[subdomain = sd, count = "1", order = "2"]
]
)),
結果 = if response[HasError] then
[最新イベント日 = null, 最新イベントタイトル = "APIエラー: " & response[Error][Message]]
else if response[Value][results_available] = 0 then
[最新イベント日 = null, 最新イベントタイトル = "イベントなし"]
else
[
最新イベント日 = response[Value][events]{0}[started_at],
最新イベントタイトル = response[Value][events]{0}[title]
]
in
結果,
#duration(0, 0, 0, 1.2) // 1.2秒待機
),
待機時間の目安
待機時間 特徴 1.0秒 API制限ギリギリ 1.2秒 適度な余裕で安全 ✅ 1.5秒 より安全だが処理時間が長い
6. エラーハンドリング
try...otherwise パターンで途中エラーでも処理を止めない。
response = try Json.Document(Web.Contents(...)),
結果 = if response[HasError] then
[最新イベント日 = null, 最新イベントタイトル = "APIエラー: " & response[Error][Message]]
else if response[Value][results_available] = 0 then
[最新イベント日 = null, 最新イベントタイトル = "イベントなし"]
else
[
最新イベント日 = response[Value][events]{0}[started_at],
最新イベントタイトル = response[Value][events]{0}[title]
]
7. SubdomainList の管理
複数のクエリで共通して使うサブドメインリストは、別クエリ（SubdomainList）として管理して参照する。
// SubdomainList クエリ
let
Subdomains = {
"subdomain-1",
"subdomain-2",
"subdomain-3"
// ...
},
クリーン済み = List.Transform(Subdomains, each Text.Clean(Text.Trim(_)))
in
クリーン済み
注意：Power BI サービス（Dataflow Gen1）での制限
Dataflow Gen1 では他のクエリを参照する「計算されたテーブル」は Premium ライセンスが必要。Power BI Pro では使えない。
→ 解決策：SubdomainList をクエリ内に直接埋め込む。
8. ページング対応
APIの1回の取得件数上限（100件）を超える場合のページング処理。
// 1ページ分を取得する関数
GetPage = (start as number) as list =>
Json.Document(Web.Contents(
"https://api.example.com/events/",
[
Headers=[#"X-API-Key"= "YOUR_API_KEY"],
Query=[
subdomain = P1,
count = "100",
start = Number.ToText(start)
]
]
))[events],
// 1回目のAPIコールで総件数を確認
ソース1 = Json.Document(Web.Contents(...)),
総件数 = ソース1[results_available],
// 必要なページ数を計算
ページ数 = Number.RoundUp(総件数 / 100),
// 各ページのstartパラメーター値のリストを生成
Starts = List.Generate(
() => 1,
each _ <= (ページ数 - 1) * 100 + 1,
each _ + 100
),
// 全ページのイベントを取得して1つのリストに結合
全イベント = List.Combine(List.Transform(Starts, GetPage)),
注意
Power BI サービスでは動的な start パラメーターがあるためページングと動的クエリ制限が相性が悪い。100件以内の場合はページングなしで設計し、超えた場合に警告を出す方が現実的。
// 100件超えた場合の警告
件数チェック = if List.Count(SubdomainList) > 100 then
error "SubdomainList が100件を超えています（現在 " & Number.ToText(List.Count(SubdomainList)) & " 件）。ページング対応が必要です。"
else
SubdomainList,
9. 複数のAPIソースを結合する
connpass と Doorkeeper など異なるAPIのデータを同じ列名で結合する。
// connpass テーブルと Doorkeeper テーブルを結合
最終テーブル = Table.Combine({connpassテーブル, DoorkeeperTable})
ポイント
- 列名を統一する（例：
accepted、public_url、name）
id など型が異なる場合は Text.From() でテキスト型に統一する
10. Dataflow について
Dataflow の役割
Power BI の Dataflow は ETL（Extract・Transform・Load）に相当する。
ETL 内容 Dataflow での役割 Extract データを抽出 外部APIからデータを取得 Transform データを変換 列の選択・名前変更・型変換など Load データを読み込む Power BI Desktop にデータを提供
Dataflow Gen1 vs Gen2
Gen1 Gen2 Power BI Pro で使える ✅ ❌（Fabric Premium が必要） 計算されたテーブル ❌（Premium必要） ✅ 外部API（匿名認証） ❌ 制限あり 未検証
結論
現時点では Power BI Desktop + Power BI サービス（pbix アップロード） が最も確実。
11. Power BI ダッシュボードのTips
日付の階層化を防ぐ
日付型の列が自動的に年・四半期・月・日に階層化される場合：
方法①：「ファイル」→「オプション」→「データの読み込み」→「自動日付/時刻」のチェックを外す
方法②：M言語で日付を YYYY-MM 形式のテキスト型に変換する
年月 = Text.From(Date.Year(...)) & "-" & Text.PadStart(Text.From(Date.Month(...)), 2, "0")
月の並び順が正しくない場合
テキスト型の 年月 列は文字列順で並ぶため、数値型の並べ替え列を追加する。
年月数値 = Date.Year(...) * 100 + Date.Month(...)
Power BI Desktop の「列ツール」→「列で並べ替え」→「年月数値」を選択。
DAX メジャーの例
// イベント数
イベント数 = COUNTROWS('テーブル名')
// 申込人数合計
申込人数合計 = SUM('テーブル名'[accepted])
// ユニーク支部数（同じ月に同じ支部が複数回開催しても1）
ユニーク支部数 = DISTINCTCOUNT('テーブル名'[subdomain])
12. connpass API v2 メモ
- エンドポイント：
https://connpass.com/api/v2/events/
- 認証：
X-API-Key ヘッダー
- アクセス制限：1秒間に1リクエスト
- 1回の最大取得件数：100件
- 主なパラメーター：
subdomain、count、order、ym
13. Doorkeeper API メモ
- エンドポイント：
https://api.doorkeeper.jp/groups/{group}/events
- 認証：
Authorization: Bearer YOUR_TOKEN ヘッダー
- 1回の取得件数：25件（ページネーション必要）
- 主なパラメーター：
sort、since、until、page
