テクテク日記

テクテク=テクノロジー&一歩ずつ(テクテク)

Power Queryにおけるデータ型の考察

Excelユーザーで普通にデータ集計を行い、ピボットテーブルを作って分析を行う人は特に"データ型"に付け使う必要はないでしょう。今迄であれば、"データ型"はAccessやデータベース等を扱うヘビーユーザー、あるいはDBA*1等の専門家が取り扱うトピックでした。しかしここにきて、ExcelやPower BIにおけるETL*2機能であるPower Queryが登場し、そこで変換されたデータはExcel Power Piovtデータベース(=データモデル)やPower BIのデータモデルに出力され、より高度な分析ができるようになったことから、"データ型"に対する理解は必須となってきました。

データ型とは

データ型が重要である理由は、

データモデルにロードされたデータは必ずそのデータ型に沿った役割を持つ

ことです。言い換えれば、数字型のデータが計算に使用され、テキスト型のデータは集計に使用できない、あるいは日付型のデータは時系列分析に使用するための役割を担うことになるわけです。数字で構成された列ではあるが、テキスト型となっていた場合、例えばDAXで下記のようなケースの場合、数字型の計算結果は問題ないが、テキスト型ではエラーが発生してしまうのです。

f:id:marshal115:20210926154517p:plain

これに対処するためには、

Power Queryの最後のステップでデータ型を変更

することがベストプラクティスとなります。なぜ最後のステップで変更するかというと、データ型変更の処理はPower Queryにおいて最も負荷がかかる作業であるからです。データ型の変更を行うと、Power Queryは最初の200行の選択列に対してスキャンを行うが、列数が多いほど当然、処理速度が遅くなります。

docs.microsoft.com

直前のパフォーマンスを最大化させるため、一般的にPower Queryステップの最後のほうでデータ型の変換を行ったほうが行数が少なくなっていることが多いため、パフォーマンス的に良いとされるわけです。

データ型の種類

Power Queryで使用されるデータ型は基本的に以下の3つがメインとなります。

  • numeric(数字型)
  • text(テキスト型)
  • date(日付型)

通常、データ分析を行う際は上記3つの型のいずれかを含むことになります。なお、numericは正確には小数点を含む10進数型(=type number)と整数型(=Int64.Type)、そして通貨型(Currency.Type = 小数点第四位まで)等があり、データモデルのサイズや集計後の数値精度に従って使い分けます。

例えば、整数ベースの結果しか要求されない場合、Int64.Typeを使用したほうが、データモデルに列が読み込まれた場合のサイズ軽減(理由は後述)に繋がります。一方、小数点まで全て合わせる必要がある場合、type numberやCurrency等を使用することになります。

下記を例に見てみると、ProductKeyという列に対して、様々なデータ型を設定できるのですが、ここでProductKeyが整数型、もしくはテキスト型のどちらに設定するかは自分が決めることになります。

※ 何も設定されていない状態の列は下図のように「ABC123」という記号(ProductKeyの左側)で表記され、正式にはtype anyとして定義されます。テキストであれば問題ないですが、DAXでメジャーを算出するための数値列である場合は計算できなくなるため、数値列の場合は必ずnumeric型に変更してあげる必要があります

f:id:marshal115:20210926154904p:plain

ProductKeyの中に文字列(A~Z等)が含まれていた場合、”整数”として設定するとエラーが起こりますので、自ずと”テキスト型”として設定せざるを得なくなります。一方、テキストが入っていないことが分かっている場合、”整数型”として設定したほう好ましい場合が多い。

整数型(Int64.Type) vs 10進数(number)

整数型に変更するとデータサイズの圧縮に繋がる場合があると上記言及しましたが、以下の例が分かりやすいでしょう。Power BIで作ったデータモデルがあり、元々のデータサイズは20.8MB。

f:id:marshal115:20210926161306p:plain

このモデルの中から、10進数の列を全て整数に変更しただけで、▲1.5MBの19.3MBになりました。

f:id:marshal115:20210926161553p:plain

ここで重要なことは、モデル内のテーブルや列を削除したりではなく、単純に列のデータ型を変更しただけという点です。この変更により、小数点まで一致させる必要がある計算は当然正しく算出できなくなりますが、それと引き換えにデータサイズの軽減を実現できたわけです。

なぜ整数型に変更しただけでファイルサイズが軽減されたかというと、Power BIやExcel Power Piovtの分析エンジン(Vertipaq)が列データベーステクノロジーを根源としており、この中には圧縮テクノロジーが織り込まれているからです。Vertipaqの特徴については下記記事を参考にするとして、列データベースの特徴の1つとして、同じ数字・文字列は自動的に並べ替えられ、圧縮アルゴリズムにより全て圧縮保存され、それが理由でデータサイズが小さくなっている、というのが簡単な解説です。

marshal115.hatenablog.com

言い換えれば、カーディナリティが高い列(データの濃度が高い列)はメモリ占領が多くなり、カーディナリティが低い列ほど、メモリ占領が少なくなることです。先ほどの例でいえば、SalesQuantity_Planという列は本来10進数であり、ユニークな数値は65,524個あったが、整数列に変更したところ、127個の値となったことが最大の要因となります(下図)。

f:id:marshal115:20210926163123p:plain

当然、ここまでくると10進数と整数の計算結果が大きく異なる可能性があり、小数点まで合わせるというニーズ以外に、そもそも小数点以下を全て集計した階層で見ても間違った数字になる可能性が高くなってきます。このような場合、整数型に変更するのは間違ったやり方であり、やるべきではないことになります。しかしながら、仮にSalesQuantity_Planという列を使用しなくても良いというのが分かれば、この列をそもそも読み込ませない(=削除してしまう)ことが選択肢の一つであると言えます。

この部分はデータモデリングに関する領域であり、DAXに関する基礎とともに押さえておきたいところです。なお、留意して頂きたいのは、データモデルのサイズはPower Queryからデータモデルへデータが読み込まれてから初めて決まるものであり、Power Queryの処理をいくら行ったとしてもデータが読み込まれない限り、データサイズについて知ることができないのです。

marshal115.hatenablog.com

データ型の設定 vs データの見た目の書式変更

よく勘違いされがちな概念の1つに、①「データ型の設定」と②「データの見た目の書式変更」が挙げられます。Power BIやExcelのPower Queryエディタでは見た目の変更はできませんが、Power BIでは一度データモデルへ読み込まれたデータは、①と②の両方を実現することができます。

Power Queryエディタでデータ型の変更を行った場合、上述の通り、小数点を有する数値が整数に変わり、場合によっては間違った計算結果を提示してしまいます。一方で、読み込まれたデータの見た目の書式を変更する場合、元々のデータは変更が加えられていない状態として残ります。

例えば、年月日の書式を変更する場合、以下のように変更すれば、見た目だけが変化し、計算結果等に変化はありません。

f:id:marshal115:20210926172519p:plain

しかしながら、例えば、以下のように、書式の変更ではなく、データ型の変更を行った場合、①小数点ありの10進数 → ②整数 → ③10進数と元に戻しても、オリジナルのデータとは異なる数字になってしまいます。

f:id:marshal115:20210926172922p:plain

これは言い換えれば、データ型の変更をPower BIのリボンで行うのではなく、常にPower Queryエディタ内で行い、書式の変更を行いたい場合のみ、Power BIのリボンで調整を行うことが良いということになります。

旧ブック(.xls)にて発生する抽出エラー

私がPower Queryを使い始めた2017年から本日まで解決されていない仕様が1つあります。それは、旧ブック(Excel 2000 - 2003ブック)からデータを抽出しようとすると、

抽出された数値はセル内にて表記されたままの書式で抽出される

ことです。

以下のようなデータセットがxlsxとxlsの両方に存在するとします。「表示あり」と「表示なし」の違いは、数字はまったく同じ but セル内で小数点まで表示されているかどうかの違いとなります。

f:id:marshal115:20210926175701p:plain

このデータセットをPower Query(ExcelでもPower BIのどちらでもOK)から抽出しようとすると、以下のようになります。

  • xlsxのブックから抽出した場合

    f:id:marshal115:20210926175840p:plain

    この場合、抽出された結果は、Excelのセル内の書式表示に関係なく、正しく小数点まで抽出できています。通常であればこれが普通だと思われるかもしれませんが、旧ブック(xls)から抽出しようとすると以下のように不思議なことが起こってしまいます。

  • xlsのブック(旧ブック)から抽出した場合
    まず、xlsが開かれていた場合、表示なしは正しく抽出できていることが確認できます。ただし、xlsxと異なるのは、xlsxがテーブルとして値を抽出できていたのに対して、xlsではテーブルではなく範囲として抽出されていることです。そのため、xlsでは1つのクエリに横並びでデータが存在しているのが分かります。

    f:id:marshal115:20210926180020p:plain

    次に、xlsブックが開かれていない状態で抽出した場合を見ると、以下のようになります。

    f:id:marshal115:20210926180425p:plainこの結果は非常にショッキングな結果です。xlsではファイルが閉じられていた場合、抽出できる値はその表示された書式のままの数字になってしまうのです。言い換えれば、旧ブックから正しい数字をPower Queryで抽出しようとすると、常に抽出元のExcelを開いておかないと正しく抽出できないことを意味しますので、これは現実的ではないし、xlsに対してデータを正しく抽出することが難しいことの裏返しになっています。

ちなみに、私が本格的にPower Queryを使い始めたのは2017年1月以降ですが、初めて使い始めたのは2016年8月頃です。期間が約4ヵ月離れていた理由は、上述のエラー(仕様)が発生していたためであり、当時はこの原因が分からず、

Power Queryは使えそうで、使えないテクノロジー

だと決めつけていたからです。言うまでもなく、この原因が判明したことでこのような考えはなくなりました。知ってしまえばなんてことない話ですが、当時は参考資料もなく、ひたすら試行錯誤で試していたので、非常に時間が掛かったと記憶しています。

本件はデータ型が整数ではなく、小数点まで正しく抽出されていることが重要であったため、Power Queryに対する考え方だけでなく、データ型に対する考え方を改めるきっかけとなりました。

まとめ

  • Power Queryでデータ型を設定することは必須である
  • データ型の変更を行わなかった場合、データモデルに読み込まれた結果に対して計算できなくなる可能性がある
  • BI用に使用するデータ型は主に数字型、テキスト型、日付型の3つである
  • 数字型には小数点(10進数)、通貨(小数点4桁まで)、整数の3つがあり、用途別に設定することになる
  • 小数点ありのデータ型から整数型に変更するだけで、モデルのデータサイズが変わることがある(データモデルが大きくなってきた場合の最適化の手法の1つ)
  • 「データ型の設定」と「データ書式の見た目の変更」の違いは押さえておくこと
  • Excelブックのバージョンによって、値が正しく抽出されない可能性がある

*1:Database Administrator

*2:Extract, Transform, Load、データの取得と変換