Power Query へそのゴマ　第１１章　ウェブコンテンツの取得と操作

本章では、Power Queryを使用してウェブコンテンツを取得し、データを分析可能な形式に変換する方法について詳しく解説します。ウェブページ、API、RSSフィードなどのウェブコンテンツからデータを取得する手順を具体例を交えて説明します。また、認証が必要な場合の設定方法や、取得したデータを操作する際のベストプラクティスについても取り上げます。

# 11.1 Web.BrowserContents を使ったウェブコンテンツの取得

[サンプルページ](https://fukuyori.github.io/RPALT20220117/)を使用して、ウエブコンテンツを取得します。

## 11.1.1 ウェブページのテーブルからデータ取得

Power Queryでは、ウェブページからテーブル形式のデータを直接取得できます。

- Power Queryエディターで「新しいソース」>「Web」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/2a56ce6d-9391-14e4-1a54-c7ff3fe5ddba.png)
- URLを入力し、「OK」を押します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/ae6bdeb6-06c5-0861-1642-31605b4759e8.png)
- 初めてアクセスする場合は、「Webコンテンツへのアクセス」で接続方法が問われます。「匿名」のままで「接続」を押します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/3e4b1ab1-a3fc-9f78-7406-323a99571d2f.png)
- ナビゲーターで取得したウェブページの中から、目的のテーブルを選択します。
   ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/d019bef6-da2f-d506-3b23-a4fdbc314ef9.png)
この操作で、以下のクエリが作成されます。

```
let
    // Webページの取得
    Source = Web.BrowserContents("https://fukuyori.github.io/RPALT20220117/"),
    
    // HTMLからテーブルを取得
    #"Extracted Table From Html" = 
        Html.Table(Source, 
        {
            {"Column1", "TABLE[id='Table-1'] > * > TR > :nth-child(1)"}, 
            {"Column2", "TABLE[id='Table-1'] > * > TR > :nth-child(2)"}, 
            {"Column3", "TABLE[id='Table-1'] > * > TR > :nth-child(3)"}, 
            {"Column4", "TABLE[id='Table-1'] > * > TR > :nth-child(4)"}
        }, 
        [RowSelector="TABLE[id='Table-1'] > * > TR"]
    ),

    // ヘッダーを作成
    #"Promoted Headers" = 
        Table.PromoteHeaders(
            #"Extracted Table From Html", 
            [PromoteAllScalars=true]
        ),

    // 型変換
    #"Changed Type" = 
        Table.TransformColumnTypes(
            #"Promoted Headers",
            {
                {"city", type text}, 
                {"state_name", type text}, 
                {"population", Int64.Type}, 
                {"density", Int64.Type}
            }
        )
in
    #"Changed Type"
```

## 11.1.2 ウェブページから例を使用してテーブルを取得

サンプルページ上の以下の部分を取得します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/1d73b4aa-7ed3-bfb0-e8c7-8d5b636ee058.png)

しかし、HTMLコードでは、この部分は`Table`ではなく、`ul`でリストを使って表示されているため、ナビゲータでテーブルとして認識されていません。

```
<div id="route">
<h4>id=rsltlst</h4>
<ul id="rsltlst" class="routeList">
  <li>
    <dl>
      <dt>ルート1</dt>
      <dd>
        <ul>
          <li class="time">19:03→<span class="mark">19:38</span><span class="small">35分</span></li>
          <li><span class="mark">726円</span></li>
          <li>乗換：<span class="mark">0回</span></li>
        </ul>
      </dd>
    </dl>
  </li>
```

ウエブページの情報を選択して取得したい場合、ナビゲーターで「例を使用してテーブルを追加」を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/e4c70c6d-8e97-9a6a-82da-e0984be018a9.png)

「例を使用してテーブルを追加」では、上部に表示されるウエブページを見ながら、下のテーブルに取得したいデータを実際にいくつか埋めていきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/e41a1200-57ec-142d-0f39-54e725336579.png)

いくつかデータを埋めていくと、項目取得のルールを判定し、残りのデータを自動で埋めてくれます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/cf1fceee-c87c-b2cb-6af9-c51b47475092.png)

ルールが見つからない場合、以下のようなメッセージが出てきますので、データ入力途中の一覧から選択するようにしてください。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/09b40e38-6f6c-08e9-baf0-66a6571949de.png)

また、ここで取得した時刻には所要時間も繋がってしまっています。HTMLの書き方によって、目的の値で取得できない場合もあります。その場合は、後のデータのクレンジング等で処理を行うことになります。

## 11.1.3 オプション

`Web.BrowserContents`関数は、最初のパラメータとして指定されたURIの生のHTMLを返します。オプションの第２パラメータとして、オプションレコードを指定できます。

### ApiKeyName

APIキーが必要なページからデータを取得する場合、`ApiKeyName`にキーの名前を入れます。

```
let
    Source = 
        Web.BrowserContents(
            "https://api.openweathermap.org/data/2.5/weather?lat=35.6809591&lon=139.7673068&mode=xml",
            [
                ApiKeyName = "appid"
            ]
        ),
    XmlDocument = Xml.Document(Source),
in
    Value3
```

クエリを実行すると、以下の様にキーの値を求められます・

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/3df704ba-3ab2-5e77-8d31-44b1624a2d6e.png)

入力したキーを変更したい場合は、「データソース設定」から該当するURIを選択し、「アクセス許可の編集」を選択し、資格情報を「編集」します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/96786e01-be49-193e-f398-4807e8cc78e0.png)

### WaitFor

`Web.BrowserContents` の `waitFor` オプションは、通常、JavaScriptで生成されたコンテンツを含む動的なウェブページにアクセスする際に使用されます。このオプションは、特定の要素がページ上で読み込まれるのを待つようにPower Queryに指示するために必要です。

```
let
    Source = Web.BrowserContents("https://example.com/", [
        WaitFor = [
            Selector = "div.ready",         // ターゲット要素のCSSセレクタ
            Timeout = #duration(0,0,0,10)   // タイムアウト設定（10秒）
        ]
    ]),
    ExtractedHtml = Text.FromBinary(Source)
in
    ExtractedHtml
```

`Timeout` および `Selector` のフィールド (あるいはその一方) を含むレコードとすることができます。 

`Timeout` のみを指定すると、関数は、HTML をダウンロードする前に、指定の時間待機します。 `Selector` と `Timeout` の両方を指定した場合、ページに `Selector` が存在するようになる前に `Timeout` の有効期限が切れると、エラーがスローされます。 `Timeout` を指定しないで `Selector` のみを指定すると、既定の Timeout である 30 秒が適用されます。

`WaitFor` は、JavaScriptのAjaxを使い非同期でデータがロードされるようなケースで、ページロードのタイミングを制御する必要があります。

## 11.1.4 注意点

Web.BrowserContents は、Webページをブラウザーのようにレンダリングし、結果を取得します。ユーザーのローカルブラウザーコンテキストでレンダリングされるため、サーバー側で動作するデータフローなどの環境では使用できません。

また、Power Queryは主にデータの取得と変換を目的としており、ウェブページのインタラクション（ボタンをクリックする、フォームに入力するなど）はサポートしていません。

---

# 11.2 Web.Contents を使ったウエブコンテンツの取得

Power Query の `Web.Contents` 関数は、ウェブ上のデータやリソースにアクセスするために使用される重要な関数です。この関数を使用すると、HTTPリクエストを介してURLに接続し、レスポンスを取得することができます。

## 11.2.1 基本的な使いまた

```:構文
Web.Contents(
    url as text, 
    optional options as nullable record
) as binary
```

### シンプルなリクエスト

ウエブ上のJsonを取得します。

```
let
    Source = Web.Contents("https://jsonplaceholder.typicode.com/posts/1")
in
    Source
```

## 11.2.2 オプションを指定したリクエスト

### Headers カスタムヘッダーを追加する

HTTPリクエストにカスタムヘッダーを追加します。認証や特定のフォーマットでの要求に使います。

```
let
    Source = Web.Contents("https://api.example.com/data", [
        Headers = [
            Authorization = "Bearer YOUR_ACCESS_TOKEN",
            Accept = "application/json"
        ]
    ])
in
    Source
```

### Contents リクエストボディを設定

リクエストボディを設定します。主にPOSTリクエストで使用されます。

```
let
    Source = Web.Contents("https://api.example.com/data", [
        Headers = [#"Content-Type" = "application/json"],
        Content = Text.ToBinary("{""key"":""value""}")
    ])
in
    Source
```

### RelativePath パス指定

基本の URL（ベース URL）に対して相対的なパスを指定するためのオプションです。

```
let
    baseUrl = "https://api.example.com",
    result = Web.Contents(baseUrl, [RelativePath="users"])
in
    result
```

結果として、`https://api.example.com/users` からデータが取得されます。

### ApiKeyName APIキーを指定

`Web.BrowserContents`の`ApiKeyName`と同様です。

### Query クエリパラメータを指定

URLにクエリパラメータを追加します。

先の `https://api.openweathermap.org/` へのアクセスで、クエリパラメータがそのまま記述されていましたが、`Web.Contents` ではパラメータを分けて記述できます。

```
let
    Source =
        Web.Contents(
            "https://api.openweathermap.org/data/2.5",
            [
                RelativePath = "weather",
                Query = [
                    lat = "35.6809591",
                    lon = "139.7673068",
                    mode = "xml"
                ],
                ApiKeyName = "appid"
            ]
        ),
    Result = Xml.Document(Source)
in
    Result
```

### その他のオプション

- Timeout:この値を期間として指定すると、HTTP 要求のタイムアウトが変更されます。 既定値は 100 秒です。
- ExcludedFromCacheKey:この値をリストとして指定すると、これらの HTTP ヘッダー キーが、データをキャッシュする計算から除外されます。
- IsRetry:この論理値を true に指定すると、データをフェッチするときにキャッシュ内の既存の応答が無視されます。
- ManualStatusHandling:この値をリストとして指定すると、応答にこれらのいずれかの状態コードが含まれる HTTP 要求に対するビルトイン処理が回避されます。

# 11.3 REST APIからのデータ取得

Power Query では、`Web.Contents` 関数を使用して REST API にアクセスし、JSON や XML 形式のデータを取得できます。

しかし、REST APIを利用する場合、URIを動的に変更して利用する場面が多くみられます。

## 11.3.1 GET リクエストの基本例

以下の例では、`GET` リクエストを使用して REST API からデータを取得し、JSON をテーブルに変換します。

```
let
    Source = Web.Contents(
        "https://data.gov.uk/api",
        [
            RelativePath = "3/action/package_search",
            Query = [
                q = "cows"
            ]
        ]
    ),
    JsonResponse = Json.Document(Source)
in
    JsonResponse
```

## 11.3.2 POST リクエストの基本例

POST リクエストでは、リクエストのボディを指定する必要があります。以下は、JSON データをリクエストボディに含めて送信する例です。

```
let
    Source = Web.Contents(
        "https://api.example.com/data",
        [
            Headers = [#"Content-Type" = "application/json"],
            Content = Text.ToBinary("{""key"":""value""}")
        ]
    ),
    JsonResponse = Json.Document(Source)
in
    JsonResponse
```

---

## 11.3.3 認証が必要な API の利用

REST API の多くは認証が必要です。以下に、主な認証方式とその使用例を示します。

### API キー認証
- API キーをヘッダーに埋め込む

```
let
    Source =
        Web.Contents(
            "https://api.openweathermap.org/data/2.5",
            [
                RelativePath = "weather",
                Headers = [
                    #"X-API-KEY" = "YOUR-API-KEY" // APIキーをここに入れる
                ],
                Query = [
                    lat = "35.6809591",
                    lon = "139.7673068",
                    mode = "xml"
                ]
            ]
        ),
    Result = Xml.Document(Source)
in
    Result
```

- APIキーをコード中に埋め込まない

```
let
    Source =
        Web.Contents(
            "https://api.openweathermap.org/data/2.5",
            [
                RelativePath = "weather",
                Query = [
                    lat = "35.6809591",
                    lon = "139.7673068",
                    mode = "xml"
                ],
                ApiKeyName = "appid"  // APIキーの名前
            ]
        ),
    Result = Xml.Document(Source)
in
    Result
```

２番目の例では、実行時にAPIキーの値を尋ねてきます。セキュリティ的にはこちらをお勧めします。

### ベーシック認証
- ユーザー名とパスワードを使用。

```
let
    Source = 
        Web.Contents(
            "https://reqres.in/api", 
            [
                RelativePath = "login",
                Headers = [
                    Authorization = "Basic " &
                    Binary.ToText(
                        Text.ToBinary("eve.holt@reqres.in:cityslicka"),  // username:password
                        BinaryEncoding.Base64
                    )
                ]
            ]
        ),
    Response = Text.FromBinary(Source) 
        

in
    Response
```

Basic認証は認証情報が平文で送信されるため、セキュリティ上の懸念があります。そのため、HTTPSを併用して通信を暗号化することが推奨されます。

近年では、セキュリティ強化のためにトークンベースの認証（例：OAuth）が一般的に使用されています。これらの方式は、より高度なセキュリティを提供します。

したがって、REST APIサービスでBasic認証を使用することは可能ですが、セキュリティ要件に応じて適切な認証方式を選択することが重要です。

### OAuth 認証
- トークンを使用。

```
Web.Contents(
   "https://api.example.com/data", 
   [
       Headers = [
           Authorization = "Bearer YOUR_ACCESS_TOKEN"
       ]
   ]
)
```

## 11.3.4 使用例

REST APIを利用する場合、URLを動的に変更して利用する場面が多くみられます。また、連続してデータを取得したい場合もあります。

```
let
    Source = List.Generate(
        ()=> 1,
        each _ <= 2,
        each _ + 1,
        each 
            Json.Document(
                Web.Contents("https://reqres.in/api/users?page=" & Text.From(_))
            )
    )
in
    Source
```

しかし、お勧めはパラメータを使用することです。

```
let
    Source = List.Generate(
        ()=> 1,
        each _ <= 2,
        each _ + 1,
        each 
            Json.Document(
                Web.Contents(
                    "https://reqres.in/api/",
                    [
                        RelativePath = "users",
                        Query = [
                            page = Text.From(_)
                        ]
                    ]
                )
            )
    )
in
    Source
```

　この記述は、１行で記述するより読みやすく、メンテナンス性に優れます。また、URLパラメータはエンコードされて渡されるため、SQLインジェクションの危険性が減ります。
 
　特に注意したいのは、URLパラメータの値を変化させて取得したい場合、最初の方法ではSaaSのPower BI サービスでは以下の表な表示が出て取得できません。

![https___qiita-image-store.s3.ap-northeast-1.amazonaws.com_0_864075_ac75448f-c996-0737-8cfe-d6e6f599379d.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/0b1cfe0b-9d63-2370-35d0-3d3631a8df28.png)

## 11.3.5 `Web.Contents` を利用する際の注意点

1. **API 制限**
   - 無料 API には、1 分あたりや 1 日あたりのリクエスト数に制限があることが多いです。
   - 必要に応じて、リクエスト間隔を調整してください。

2. **エラーハンドリング**
   - API がエラーを返す場合（例: 404 や 500）、適切なエラーハンドリングを実装する必要があります。
   ```powerquery
   try Web.Contents("https://api.example.com/data") otherwise null
   ```
   - 特に、連続してデータを取得する場合、エラーハンドリングを行わないと無限ループになってしまう危険性があります。

3. **セキュリティ**
   - API キーや認証情報をハードコーディングするのは避け、適切に管理することを推奨します。


上記の注意点を取り入れたコードは以下の様になります。

## 11.3.6 関数を使ってYoutubeのビデオデータを取得

Youtube Data API を使って、登録されているビデオの一覧を作成します。

### パラメータの作成

取得対象となるチャンネルのIDを `Param_ChannelId` に設定します。

複数ページにわたる場合、2回目のアクセスにはページトークンが必要となります。最初のクエリを作成するために、`Param_PageToken` をパラメータとし、「必須」のチェックボックスを外しておきます。１ページ目にはトークンが必要ないので、現在の値には何も入れません。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/eef70c43-e17f-1010-f357-81f985b91891.png)

### １ページ目を取得するクエリを作成

```:GetVideos
let
    // Youtube Data API でビデオ一覧を取得
    Source = 
        Json.Document(
            Web.Contents(
                "https://www.googleapis.com/youtube/v3/search",
                [
                    ApiKeyName = "key",
                    Query = [
                        part = "id,snippet",
                        type = "video",
                        maxResults = "50",
                        channelId = Param_ChannelId,
                        // Param_PageTokenがnullの時、pageTokenのパラメータは付加されない
                        pageToken = Param_PageToken ?? {}  
                    ]
                ]
            )
        ),

    // トークンを取得 トークンがない最終ページの場合は "END"
    Token = Record.FieldOrDefault(Source, "nextPageToken", "END"),

    // ビデオ一覧が入っている列をリストにする
    items = Source[items],

    // ビデオIDとタイトル、登録年月日をレコード形式にする
    Records = 
        List.Transform(
            items,
            each [
                Id = [id][videoId], 
                Title = [snippet][title], 
                PublishTime = [snippet][publishTime] 
            ]
        ),

    // ビデオ一覧をテーブルに変換
    Table = 
        Table.FromRecords(
            Records,
            type table [Id = text, Title = text, PublishTime = text]),

    // トークンとテーブルをレコード型に
    Result = [NextPageToken = Token, Data = Table]
in
    Result
```

取得したJSON形式のデータは、以下のような構成になっています。

```
YouTube API Response
├── kind (string)
├── etag (string)
├── nextPageToken (string)
├── regionCode (string)
├── pageInfo (object)
│   ├── totalResults (number)
│   ├── resultsPerPage (number)
├── items (array)
    ├── item (object)
        ├── kind (string)
        ├── etag (string)
        ├── id (object)
        │   ├── kind (string)
        │   ├── videoId (string)
        ├── snippet (object)
            ├── publishedAt (string)
            ├── channelId (string)
            ├── title (string)
            ├── description (string)
            ├── thumbnails (object)
            │   ├── default (object)
            │   │   ├── url (string)
            │   │   ├── width (number)
            │   │   ├── height (number)
            │   ├── medium (object)
            │   │   ├── url (string)
            │   │   ├── width (number)
            │   │   ├── height (number)
            │   ├── high (object)
            │       ├── url (string)
            │       ├── width (number)
            │       ├── height (number)
            ├── channelTitle (string)
            ├── liveBroadcastContent (string)
            ├── publishTime (string)
```

この中から`videoId` と `title`、`publishTime`でテーブルを作成しています。`nextPageToken`も返さなけらばならないので、レコード形式に詰め込んで返すようにしています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/ccc8ac77-7dd8-88a2-5d4c-b25a8b2f5daa.png)

`Web.Contents`のパラメータで `pageToken = Param_PageToken ?? {}` と書かれている部分は、 `Pram_PageToken` が `null` の場合、`pageToken` パラメータはURLに付加されません。

### 関数に変換

先ほどのクエリの１行目に以下のコードを追加し、関数 `FxGetVideos` を作成します。

```:FxGetVideos
(Param_PageToken as nullable text) as record =>

（以下は GetVideos と同じ）
```

`Param_PageToken` を引数としますが、形式は `nullable text` となっています。これは、`text` 形式で `null` も許容するという意味です。

### 主クエリの作成

この関数を連続して呼び出すクエリを作成します。`List.Generate`を使うのが便利です。

```:主クエリ
let
    Source = 
        List.Generate(
            ()=> [
                count = 0,
                Result = 
                    try 
                        Function.InvokeAfter(
                            ()=> FxGetVideos(null),
                            #duration(0,0,0,1)  // １秒遅延
                        )
                    otherwise null
            ],

            each [Result] <> null and [count] < 10,

            each [
                count = [count] + 1,
                Result =
                    // 前回の結果でトークンが END の場合は最終ページなので終わり
                    if [Result][NextPageToken] = "END"
                    then null
                    else
                        try
                            Function.InvokeAfter(
                                ()=> FxGetVideos([Result][NextPageToken]),
                                #duration(0,0,0,1)
                            )
                        otherwise null
            ],

            each [Result][Data]
        ),
    
    Table = Table.Combine(Source)
in
    Table
```

`Function.InvokeAfter`の中で`FxGetVideos`を呼び出しています。`Function.InvokeAfter`は、`duration`で決められた時間を待ってから呼び出しを行います。連続して呼び出す場合の遅延を発生させます。

また、その前に`try`をつけており、エラーが発生した場合は、`Result`に`null`が入ります。

停止条件となる`each [Result] <> null and [count] < 10,`は、エラーが発生して`Result`が`null`になるか、`count`が１０以上になれば終了します。ループの最大回数とエラー発生時の処理を書くことで、永久ループにならないようにしています。

また、前回の結果に入っているトークンが `END` の場合は、最終ページなので null を返して処理を停止するようにしています。

最後に、`Table.Combine`で、全てを１つのテーブルにまとめています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/965175c3-e7e6-0aa2-8cf3-4361e783f0d7.png)

残るは、`PublishTime`のタイムゾーンの変換が必要でしょう。

----

https://qiita.com/spumoni/items/419ffd1f07947a07cad0#3youtube%E3%81%AE%E5%8B%95%E7%94%BB%E3%83%AA%E3%82%B9%E3%83%88%E3%82%92%E5%8F%96%E5%BE%97

https://qiita.com/spumoni/items/cd5843e0104fed9b4874