【Power Query】Power BI 知育アプリのデータテーブルを作る

# はじめに

**以前、Power BI での知育アプリ作成について投稿**しました。

https://qiita.com/AnPanda/items/edec1746a2ba1a1e69bb

そちらの「おわりに」で言及していた、**データ元のテーブルを Power Query で作成することにチャレンジした**ので共有します。

# 作成するテーブル

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/464218/cc106492-b227-ee8b-b07f-74de5074e850.png)

**前回の投稿では、Excel 関数を使ってテーブルを作成**していました。実際の関数については、前回の投稿を参照ください。

# パラメータ

**先ほどのキャプチャは、各色(RGB)8段階ずつ、8×8×8=512 パターン**を表現できるテーブルでした。

**Power Query で作成するテーブルでは、パラメータ機能を使って、2の1乗(2)～2の7乗(128) の間から選択**できるようにしてみました。パラメータ名は `bit` としています。このあとのクエリでは、`bit` を参照して各種計算を行っています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/464218/b97f4ff2-c981-a1aa-bf4e-3ff7342b9bb9.png)



# Power Query

**作成した Power Query の全文を掲載**します。「お作法」がわかっておらず、見づらいところがあるかもしれませんがご容赦を。

```
let
    ソース = Table.FromList( {0..Number.Power(bit, 3) - 1}, Splitter.SplitByNothing(), {"Index"} ),
    変更された型 = Table.TransformColumnTypes(ソース, { {"Index", Int64.Type} } ),
    列作成_Red = Table.AddColumn(変更された型, "Red", each Number.RoundDown([Index] / Number.Power(bit, 2) ), Int64.Type),
    列作成_Green = Table.AddColumn(列作成_Red, "Green", each Number.Mod(Number.RoundDown([Index] / bit), bit), Int64.Type),
    列作成_Blue = Table.AddColumn(列作成_Green, "Blue", each Number.Mod(Number.RoundDown([Index]), bit), Int64.Type),
    列作成_Hex = Table.AddColumn(列作成_Blue, "Hex",
        each "#" &
        Number.ToText([Red] * (256 / bit) + ( (256 / bit) / 2), "X2") &
        Number.ToText([Green] * (256 / bit) + ( (256 / bit) / 2), "X2") &
        Number.ToText([Blue] * (256 / bit) + ( (256 / bit) / 2), "X2"),
        Text.Type
    )
    列削除_Index = Table.RemoveColumns(列作成_Hex, {"Index"})
in
    列削除_Index
```

以下、1行ずつ解説していきます。

### ソース：テーブル作成

```
ソース = Table.FromList( {0..Number.Power(bit, 3) - 1}, Splitter.SplitByNothing(), {"Index"} )
```

**`Table.FromList` を使って、まずは Index となる列を作成**します。1行目に0、2行目に1、3行目に2… といった形になるようにしました。返される値は、Excel 関数における `ROW()-1` と同じ結果になります。

https://learn.microsoft.com/ja-jp/powerquery-m/table-fromlist

**`{ a..b }` で、a から b までの連続する整数値を作成**できます。開始値は 0 で固定、終了値は `bit` の 3乗から 1 を引いた値(0 始まりなので)になります。

https://learn.microsoft.com/ja-jp/powerquery-m/m-spec-values#list

**累乗の計算には、`Number.Power`** を使います。Excel や PowerFX とは異なり、演算子 `^` を使った `bit ^ 3` ではダメでした。

https://learn.microsoft.com/ja-jp/powerquery-m/number-power

**`Table.FromList` の第2引数を `Splitter.SplitByNothing` とすると、列挙したリストをテーブルに**するような動きになります。

https://learn.microsoft.com/ja-jp/powerquery-m/splitter-splitbynothing

### 型変更：Index を整数型に

```
変更された型 = Table.TransformColumnTypes(ソース, { {"Index", Int64.Type} } )
```

**先ほど作成した Index 列は計算に使うので、明示的に整数型に変更**しておきます。型変更には、`Table.TransformColumnTypes` を使います。

https://learn.microsoft.com/ja-jp/powerquery-m/table-transformcolumntypes

### RGB値の列作成

```
列作成_Red = Table.AddColumn(変更された型, "Red", each Number.RoundDown([Index] / Number.Power(bit, 2) ), Int64.Type),
列作成_Green = Table.AddColumn(列作成_Red, "Green", each Number.Mod(Number.RoundDown([Index] / bit), bit), Int64.Type),
列作成_Blue = Table.AddColumn(列作成_Green, "Blue", each Number.Mod(Number.RoundDown([Index]), bit), Int64.Type)
```

**`Table.AddColumn` で、テーブルに列を追加**します。

https://learn.microsoft.com/ja-jp/powerquery-m/table-addcolumn

作成した各列の中身は、**Excel の関数を Power Query(M言語) に置き換えた形**になっています。
* `INT` → `Number.RoundDown`
* `POWER` → `Number.Power`
* `MOD` → `Number.Mod`
* `ROW` → Index 列を参照する
※Excel のテーブルでは 1行目にヘッダーがあり 2行目が 0番目のデータになるため、`ROW() - 2` として利用。これを Index 列に置き換えています。

https://learn.microsoft.com/ja-jp/powerquery-m/number-rounddown

https://learn.microsoft.com/ja-jp/powerquery-m/number-mod

Excel では定数8 だったところを、**Power Query では変数 `bit` を参照する**ようにしています。

また、**`Table.AddColumn` のガイドを見てみると、第4引数で型を指定できる**ようです。`Int64.Type` を指定して、整数型として作成しました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/464218/988fb370-9863-3839-8aab-d9f97f672266.png)

なお、**GUI 操作で列を追加する場合、型を明示的に指定できません**。もちろん、列追加後に型変更をすることはできますが、同じステップ内で完結させてしまった方がシンプルかなと。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/464218/6693805f-9ee1-7404-82b5-1c3bc266aa36.png)

### Hex値の列作成

```
列作成_Hex = Table.AddColumn(列作成_Blue, "Hex",
    each "#" &
    Number.ToText([Red] * (256 / bit) + ( (256 / bit) / 2), "X2") &
    Number.ToText([Green] * (256 / bit) + ( (256 / bit) / 2), "X2") &
    Number.ToText([Blue] * (256 / bit) + ( (256 / bit) / 2), "X2"),
    Text.Type
)
```

Hex 列は、**16進数に変換した RGB の各値をつなげて、頭に `#` をつけたもの**です。

**256 を変数 `bit` で割ったものが、スライサー 1段階あたりのRGB変化量**です。値を中央に寄せるために、1段階の変化量の半分を加えています。`bit` が 8のとき、スライサーの値が最小の 0だったら 16、最大の 7だったら 240になります。

こうして得た **RGB の各値を、`Number.ToText` を使って 10進数から 16進数に変換**します。第2引数に `X2` と入力すると、アルファベット大文字の 16進数表記で、2桁にゼロ埋めすることが変換できます。

https://learn.microsoft.com/ja-jp/powerquery-m/number-totext

https://learn.microsoft.com/ja-jp/dotnet/standard/base-types/standard-numeric-format-strings

※当初、ゼロ埋めには `Text.PadStart` を使っていましたが、`Number.ToText` の第2引数の値でゼロ埋めも可能だとわかったので修正しました。

https://learn.microsoft.com/ja-jp/powerquery-m/text-padstart

### 列削除：Index

**ここまでで、ビジュアルに必要なデータテーブルは完成**しています。

**RGB・Hex の計算に使った Index 列はもう不要なので、削除してしまって問題ありません**。他の用途で Index 列を使う予定がなければ、削除しておきましょう。

```
列削除_Index = Table.RemoveColumns(列作成_Hex, {"Index"})
```

**Index 列が再度必要になったら、この列削除のステップを削除すれば OK** です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/464218/8cd9a46c-6247-71aa-7125-4dbfe9840f76.png)


# おわりに

**外部の Excel を参照せずに、Power Query でデータテーブルを作る**ことができました。ETL ツールである Power Query でデータを作成するのは、本来の使い方ではないんですが、Power Query の関数や記載方法を学ぶことができました。

**関数をネストすればひとつのステップで複数の処理を実行できますが、処理がわかりにくくなるおそれも**あります。型変更や列名変更など、列作成の引数で指定できるものは、列作成のステップの中でやっておいた方がよいのかなと思いました。

ちなみに、**外部のExcelからインポートする方法と、Power Query で作成する方法、処理速度に差はなかった**です。`bit` を 64(262,144レコード) にしてパフォーマンス アナライザー を動かしてみたんですが、大きな差は見られず。テスト方法が悪かったのかもしれないので、再学習したいところです。