はじめに

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

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

作成するテーブル

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

パラメータ

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

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

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 と同じ結果になります。

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

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

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

型変更：Index を整数型に

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

先ほど作成した Index 列は計算に使うので、明示的に整数型に変更しておきます。型変更には、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 で、テーブルに列を追加します。

作成した各列の中身は、Excel の関数を Power Query(M言語) に置き換えた形になっています。

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

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

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

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

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桁にゼロ埋めすることが変換できます。

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

列削除：Index

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

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

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

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

おわりに

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

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

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

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