Power BI Desktop で Connpass のイベントを解析する

今回は [Connpass](https://connpass.com) のイベントデータを取ってみます。

# Connpass API

[API リファレンス](https://connpass.com/about/api/)によるとイベントの検索クエリのみです。
今回は特定グループのイベントをそれぞれとってみます。

## グループのイベントアドレス

シリーズ (3366 は Power BI 勉強会)
https://connpass.com/api/v1/event?series_id=3366

## ページング

応答に含まれる情報と count、start パラメーターを使ってページングできます。

```json
{
    "results_start": 1,
    "results_returned": 10,
    "results_available": 38,
    "events": [...]
}
```
## Postman で実行してみる

https://connpass.com/api/v1/event?series_id=3366&start=1&count=5 を実行
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/76bb8298-a48c-68da-3615-9fbed0028de0.png)


# Power BI でレポートを作る

1\. Power BI Desktop を開き、「データを取得」をクリック。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/31d9f325-03ae-7f01-2632-8a310bebbccb.png)

2\.「空のクエリ」を追加。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/cc7faa0d-3db4-0760-b70f-77a4c90cc80b.png)

3\.「詳細エディター」をクリックして、以下コードを張り付け。

```fsharp
let
    Count = 10,
    Series_Id = 3366,
    GetConnpassEvents = (start as number) => 
        let
            data = Web.Contents("https://connpass.com/api/v1/event?series_id="& Number.ToText(Series_Id) & "&count=" & Number.ToText(Count) & "&start=" & Number.ToText(start))
        in
            data,  
    AllEventsRawData = List.Generate(
        ()=> GetConnpassEvents(1),
        each Json.Document(_)[results_returned] > 0,
        each GetConnpassEvents(Json.Document(_)[results_start]+Json.Document(_)[results_returned])
    ),    
    AllEventsList = List.Transform(AllEventsRawData, (_) => Json.Document(_)[events]),
    AllEvents = Table.FromList(List.Combine(AllEventsList), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandColumns = Table.ExpandRecordColumn(AllEvents, "Column1", {"event_id", "title", "catch", "description", "event_url", "started_at", "ended_at", "limit", "hash_tag", "event_type", "accepted", "waiting", "updated_at", "owner_id", "owner_nickname", "owner_display_name", "place", "address", "lat", "lon", "series"}, {"event_id", "title", "catch", "description", "event_url", "started_at", "ended_at", "limit", "hash_tag", "event_type", "accepted", "waiting", "updated_at", "owner_id", "owner_nickname", "owner_display_name", "place", "address", "lat", "lon", "series"}),
    ConvertGeolocationType = Table.TransformColumnTypes(ExpandColumns,{{"lat", type number}, {"lon", type number}}),
    ConvertIntType = Table.TransformColumnTypes(ConvertGeolocationType,{{"waiting", Int64.Type}, {"accepted", Int64.Type}, {"limit", Int64.Type}}),
    ConvertDateType = Table.TransformColumns(ConvertIntType,{{"ended_at", each Date.From(DateTimeZone.From(_)), type date}, {"started_at", each Date.From(DateTimeZone.From(_)), type date}, {"updated_at", each Date.From(DateTimeZone.From(_)), type date}}),
    ExpandSeries = Table.ExpandRecordColumn(ConvertDateType, "series", {"id", "title", "url"}, {"series.id", "series.title", "series.url"})
in
    ExpandSeries
```
4\. クエリ名を Events に変更し、「閉じて適用」をクリック。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/d2d1788c-a60d-81f1-6204-0048c7ab234e.png)

5\. レポートを作る。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214116/68328ba1-a969-cb86-7463-22a46f38d5ad.png)

# コードの説明

Power Query と M 言語については以下記事が素晴らしいので参照してください。
[Power Query ってちょっと不思議なところがあるけど、知っておいた方がいいことがあるよという話をした](https://qiita.com/PowerBIxyz/items/a98a991b301c071632dc)

では今回のコードです。

一番初めに変数の設定

```fsharp
let
    Count = 10,
    Series_Id = 3366,
```

Web.Contents 関数を使って Connpass API よりイベントを取得。引数として開始番号を渡す他、数字をテキストに変換するため、Number.ToText を使用。

```fsharp
    GetConnpassEvents = (start as number) => 
        let
            data = Web.Contents("https://connpass.com/api/v1/event?series_id="& Number.ToText(Series_Id) & "&count=" & Number.ToText(Count) & "&start=" & Number.ToText(start))
        in
            data,  

```

イベント数によっては複数回 GetConnpassEvents を呼ぶことになるため、List.Generate を利用。

- 初期化で GetConnpassEvents(1) を呼び、まず 1 ページ目のイベントを取得
- ```Json.Document(_)``` にある ```_``` は GetConnpassEvents の結果のため、Web.Contents の戻り値
- 戻り値を Json.Document で解析して、 ```results_returned``` を使って処理を続けるか確認。レコードが返ってきている限りは次も実行する
- 2 回目以降は戻り値の ```results_start``` と ```results_returned``` の値から ```start``` の値を作る

```fsharp
    AllEventsRawData = List.Generate(
        ()=> GetConnpassEvents(1),
        each Json.Document(_)[results_returned] > 0,
        each GetConnpassEvents(Json.Document(_)[results_start]+Json.Document(_)[results_returned])
    ),    
```
List.Transform から上記の AllEventsRawData を呼び出し、結果から ```events``` だけを取り出す。

```fsharp
    AllEventsList = List.Transform(AllEventsRawData, (_) => Json.Document(_)[events]),
```

その後 List.Combine で取得した ```events``` をすべて結合し、Table.FromList でテーブル化。

```fsharp
    AllEvents = Table.FromList(List.Combine(AllEventsList), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
```
ここからとは GUI でも出来る変換を行っているだけです。

# まとめ

Web API の仕様にもよりますが、大体似たようなパターンでデータを取得できます。実施にはエラーのハンドルなど考える必要もありますが、是非色々なデータを取ってみてください。

# 参照
[Power Query ってちょっと不思議なところがあるけど、知っておいた方がいいことがあるよという話をした](https://qiita.com/PowerBIxyz/items/a98a991b301c071632dc)
[Power Query の List.Generate 関数ってなんだよー](https://qiita.com/PowerBIxyz/items/ed6ebb547a9157561ad1)
[ときおり出てくる "each" キーワードと "_" (アンダースコア) とは](https://qiita.com/PowerBIxyz/items/6668537a576592a04bcf)