[Power BI] Power Queryのカスタム関数を作成して、JAPAN RUGBY LEAGUE ONE 選手の身長・体重を可視化する。

# はじめに

![RugbyLeagueONE_LOGO](https://league-one.jp/assets/img/about/logo-about-leagueone.svg)

2022年、それまでの「TOP LEAGUE」が解消され、**「JAPAN RUGBY LEAGUE ONE」** が開幕しました。Jリーグのように各チーム名に地域名がはいるようになり、企業スポーツとして発展してきたラグビーが、より地域やファンとともに成長していく存在になるのではと思っています。

コロナの影響で中止になる試合もでてきていますが、開催できた試合は熱い試合が多くとてもおもしろいです。

[「JAPAN RUGBY LEAGUE ONE」公式サイト](https://league-one.jp/)

<iframe width="560" height="315" src="https://www.youtube.com/embed/FqJHVlVh4fQ" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>

## やってみること

「JAPAN RUGBY LEAGUE ONE」開幕を記念して、各チーム所属選手の身長・体重を可視化し、
ラグビー選手の体の大きさを実感していきたいと考えています。
Power Query を使用してWEBから情報をとってきて、カスタム関数を使用して実装しています。

**↓完成したレポートはこちら**

https://app.powerbi.com/view?r=eyJrIjoiOGMzYTNmMTAtMGY1MC00OGEwLWExOGQtOTgyZDdhNDdiNTU5IiwidCI6IjdkMDZmZWVlLWI3MzYtNDE3Zi1hMGUyLTE3NTZjOTdiMDU5YyJ9&pageName=ReportSection

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/781c410f-8693-a48f-40d4-25e31ff38ac0.png)


## 作業開始

Power BI Desktop を起動して、もととなるデータを取得していきます。

1. 「データを取得」ボタンを押す
1. 「Web」を選択

チームごとの情報がのっているページURLは

`https://league-one.jp/team/` + `Team_Number（１～２４）`

となっているのがわかっています。（事前に調べてます。）

選手の身長体重も画面表示上はタブで分けられていますが、同一HTMLファイル上にあります。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/faaf9091-740f-bf48-8ec4-cac958e67f08.png" width=50%>

### WEBから取得

最終的には全チームを巡回する仕組みを作るのですが、
まずは「NECグリーンロケッツ東葛」の選手一覧を取得するPower Queryを作成していきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/882a1701-aabe-d912-9054-123c70fe0ee6.png)

https://league-one.jp/team/1

Power BI Desktop が自動的にHTMLを解析してくれて、HTML内のテーブル要素などを抜き出してくれます。今回でいうと一番上のテーブルが選手の身長体重一覧を抜き出しています。

### データの変換

該当のテーブルをチェックして**読み込みを押す** ・・・の前に、**データの変換** を押します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/2c49abd6-6b76-359f-c56b-744ff8efa9ea.png)

Power Query エディターが開いたら、不要な列を削除したり、型の変換を行います。
「選手名」列は不要なので削除します。また、「身長」「体重」は`cm`や`kg`が入って文字列となっているので、単位を削除して数値のみを持ちたいです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/8b71bc18-1d53-06a6-d3f8-34f257ba95b4.png)


GUI上での操作はいろいろなやり方が考えられますが、今回は下記のやり方でデータ変換を進めていきます。

1. `身長`カラムはそのセルに入っている1番目の数字から3文字を取得する。（抽出ボタンの**範囲**）
1. `体重`カラム（抽出ボタンの**区切り記号の前のテキスト**）

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/0479963c-5382-c9a9-d174-c27b419c4e98.png)

## チームごとのパラメーターとなるテーブルを作成する

同じく`データの取得`から**Web**を選び、今度は

**https://league-one.jp/team/**

このURLを指定し、画面左下の **"例を使用してテーブルを追加"** を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/cb97937d-db34-4335-8fc8-d9853e95fdba.png)

### 例を使用してテーブルを追加

この画面で列名を **Team_URL** とした上で

- 1行目に`/team/1`
- 2行目に`/team/2`

と入力していくと、またまたHTML・CSSを解析して候補をだしてくれます。
`/team/6`までしか候補が出なかったので、`/team/7`をさらに追加で入力してあげるとテーブルが完成します。同じ要領でチーム名も取得しちゃいましょう。

:::note
team/1,team/2 の次はteam/7 をいれないといけないのは、HP上では6teamごとに"div"で区切られているからですね。それでもteam/7まで入力すると、「6teamごとに"div"で分かれている」ことを理解して、team/24まで自動で入力してくれるのはやっぱり便利！
:::

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/1da0c229-55ca-9b29-6c05-4349af153c57.png)

## カスタム関数の定義 （Power Query）

次に、最初に取得した`Players`テーブルは、

`https://league-one.jp/team/1`

というURLを指定して取得しましたが、この指定したURLの `/team/1` をいま作成した`Team_List`の中身で順次置き換えてテーブルにいれていくと全選手のリストが完成します。

`Players` テーブルの詳細エディターを開き、Power Query を関数化します。

関数化するためにはlet式の外側に引数を定義（**1行目**）して、let式の内側で引数を使用（**3行目**）します。

```datastudio
(team_number as text) =>
let
    Source = Web.BrowserContents("https://league-one.jp" & team_number),
    Extracted_Table_From_Html = Html.Table(Source, {{"Column1", "TABLE.team-table > * > TR > TH[colspan=""2""]:not([rowspan]):nth-child(1):nth-last-child(5), TABLE.team-table > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6)"}, {"Column2", "TABLE.team-table > * > TR > TH[colspan=""2""]:not([rowspan]):nth-child(1):nth-last-child(5), TABLE.team-table > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5)"}, {"Column3", "TABLE.team-table > * > TR > TH[colspan=""2""]:not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4), TABLE.team-table > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(4)"}, {"Column4", "TABLE.team-table > * > TR > TH[colspan=""2""]:not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(3), TABLE.team-table > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3)"}, {"Column5", "TABLE.team-table > * > TR > TH[colspan=""2""]:not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(3) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(2), TABLE.team-table > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(2)"}, {"Column6", "TABLE.team-table > * > TR > TH[colspan=""2""]:not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(3) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(2) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(1), TABLE.team-table > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(1)"}}, [RowSelector="TABLE.team-table > * > TR"]),
    Promoted_Headers = Table.PromoteHeaders(Extracted_Table_From_Html, [PromoteAllScalars=true]),
    Changed_Type = Table.TransformColumnTypes(Promoted_Headers,{{"選手名", type text}, {"選手名_1", type text}, {"ポジション", type text}, {"身長（cm）", type text}, {"体重（kg）", type text}, {"生年月日", type date}}),
    Add_height_column = Table.AddColumn(Changed_Type, "Height", each Text.Middle([#"身長（cm）"], 0, 3), Int64.Type),
    delete_kg = Table.TransformColumns(Add_height_column, {{"体重（kg）", each Text.BeforeDelimiter(_, "kg"), Int64.Type}}),
    Remove_column = Table.RemoveColumns(delete_kg,{"選手名","身長（cm）"}),
    Renamed_Columns = Table.RenameColumns(Remove_column,{{"ポジション", "Position"}, {"選手名_1", "Name"}, {"生年月日", "Birthday"}, {"体重（kg）", "Weight"}}),
    Reordered_Columns = Table.ReorderColumns(Renamed_Columns,{"Name", "Position", "Height", "Weight", "Birthday"})
in
    Reordered_Columns
```

関数化すると、下のようなパラメーターを入力できる画面になります。
このパラメーターに`Team_URL`を入力すると、任意のチームのメンバーリストが取得できる関数となっています。
試しに`/team/5`を入力してみます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/110ab2ac-fc9c-d939-e516-aa39f6d7a8e4.png)


期待したとおり、**埼玉パナソニックワイルドナイツのメンバーリスト**が取得できました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/cdd6b79e-492a-3d62-46ac-0bfd1623d297.png)

この関数を`Team_List`テーブルで使用します。
カスタム関数の呼び出しをクリックし、項目を設定していきます。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/7c77a429-ba98-a5c8-41a5-c84ba3e46951.png" width=70%>

画像にも書いてある通り、各行ごとにさきほど定義した関数に`Team_URL`列の項目を引数に入力した上でを呼びだしていきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/5f876e88-e49f-8293-6770-65acb7c89c49.png)

見事テーブルで取得できました。
このテーブル列を展開すると各チームの選手が取得できます。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/40e2b512-a1a4-2d7e-f794-92d5c0bb7894.png" width=70%>

ボタンを押すと・・できた！

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/54d8b111-b055-fa30-bc0e-0d169856f1d3.png)

### 追加作業

- `Team_URL`カラムは不要なので削除
- 代わりに`Team_ID`カラムを追加
- WEBからチームロゴも取得

など細かい調整も行いました。今回は省略。

## 可視化（レポート作成）

簡単にレポートを作成してみました。

**最高身長** 東京サントリーサンゴリアス	ハリー ・ホッキングス選手 206cm	118kg:muscle:
**最高体重** 清水建設江東ブルーシャークス	モツアプアカ ・アライモアナ選手	192cm	130kg:astonished:

**平均身長** 180cm
**平均体重** 97kg

でけぇ・・・:sweat_smile:

**↓公開レポートはこちら**

https://app.powerbi.com/view?r=eyJrIjoiOGMzYTNmMTAtMGY1MC00OGEwLWExOGQtOTgyZDdhNDdiNTU5IiwidCI6IjdkMDZmZWVlLWI3MzYtNDE3Zi1hMGUyLTE3NTZjOTdiMDU5YyJ9&pageName=ReportSection

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/781c410f-8693-a48f-40d4-25e31ff38ac0.png)



## 参考資料

https://docs.microsoft.com/ja-jp/power-query/custom-function