Power Query へそのゴマAdvent Calendar 2024

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

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

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

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

サンプルページを使用して、ウエブコンテンツを取得します。

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

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

Power Queryエディターで「新しいソース」>「Web」を選択します。
URLを入力し、「OK」を押します。
初めてアクセスする場合は、「Webコンテンツへのアクセス」で接続方法が問われます。「匿名」のままで「接続」を押します。
ナビゲーターで取得したウェブページの中から、目的のテーブルを選択します。

この操作で、以下のクエリが作成されます。

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 ウェブページから例を使用してテーブルを取得

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

しかし、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>

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

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

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

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

また、ここで取得した時刻には所要時間も繋がってしまっています。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

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

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

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 サービスでは以下の表な表示が出て取得できません。

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

API 制限
- 無料 API には、1 分あたりや 1 日あたりのリクエスト数に制限があることが多いです。
- 必要に応じて、リクエスト間隔を調整してください。
エラーハンドリング
- API がエラーを返す場合（例: 404 や 500）、適切なエラーハンドリングを実装する必要があります。
```
try Web.Contents("https://api.example.com/data") otherwise null
```
- 特に、連続してデータを取得する場合、エラーハンドリングを行わないと無限ループになってしまう危険性があります。
セキュリティ
- API キーや認証情報をハードコーディングするのは避け、適切に管理することを推奨します。

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

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

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

パラメータの作成

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

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

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

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も返さなけらばならないので、レコード形式に詰め込んで返すようにしています。

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で、全てを１つのテーブルにまとめています。

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

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

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