DAX Studioによるデータのエクスポート

ExcelやPower BIからデータをCSVやText形式に出力する方法を解説します。

概念図

f:id:marshal115:20200606154244p:plain

Power QueryでExcelのワークシートに出力したものを別途Excelファイルに貼り付け、CSVとして保存する方法よりも柔軟性が高く、かつ、以下のようなメリットがあります。

  • データボリュームが100万行以上の場合でも出力できる
  • 複数のテーブルを一括で出力できる
  • 基データがExcelである場合に比べ、Power Queryでの処理速度が約7も早くなる

最後の処理速度ですが、CSVが構造的データ(Structured Data)*1であるのに対して、Excelは非構造的データ(Unstructured Data)*2であることから、Excelファイルに接続してPower Queryで処理を行う場合は、速度が遅くなりがちです。

そのような場合、必要なデータを一度整備(あるいは結合)して、CSVやTextファイルとして出力した後、Power Queryに再度接続すると殆どのケースにおいてパフォーマンスが大きく改善されます。

データをCSVTextファイルにエクスポートする方法

出力データの準備
  1. DAX Studioをインストール

    https://daxstudio.org/

    DAX StudioはDAXの操作やパフォーマンス・チューニング用に開発された無償ツール。BI上級者が使用するツールですが、データをフラットファイル形式に出力するための機能を持っており、今回の使用ツール。

  2. Power BI、あるいはExcelでデータセットを作成(以下、サンプル用画像)
    f:id:marshal115:20200606164843p:plain

    f:id:marshal115:20200606165129p:plain

  3. Data Model(リレーション)を構築
    ExcelであればPower Queryで作ったクエリをシートに出力しないでデータモデルにそのまま読み込みます(このステップは必ず必要)。なお、リレーションはなくても良いが、組んでおいたほうが無難。

    f:id:marshal115:20200606165438p:plain

    f:id:marshal115:20200606165457p:plain

Power BIからデータをCSVとして出力する場合
  1. Power BIを立ち上げた状態に(データモデル構築済という前提)
  2. DAX Studioを立ち上げPower BIを選択f:id:marshal115:20200606165751p:plain
  3. 左側のメタデータウィンドウで各テーブルがあることを確認f:id:marshal115:20200606165900p:plain
    ※ Power BIでもリレーションが設定されていなくても大丈夫ですが、下図のように、Power BIのフィールドにあるテーブルがDAX Studioに表示されます(読み込みをしないテーブルはDAX Studioには出現しない)
    f:id:marshal115:20200606170009p:plain
  4. 構文ウィンドウにEVALUATE必須記述)を冒頭に記述し、Sales(出力するテーブル)を下に記述(下図①~②)
  5. Home > Output > Fileに設定(下図③~④)f:id:marshal115:20200606170533p:plain
  6. 構文をハイライトしてF5キーをクリック(⑤)、出力先を選択(⑥)、ファイルの種類を選択(⑦)、保存(⑧)f:id:marshal115:20200606170653p:plain※なお、ステップ⑦の「ファイルの種類」は以下4つから選択可能f:id:marshal115:20200606170738p:plain
  • Tab separated text file: タブ区切り形式で保存
  • Comma separated text file -UTF8: CSV形式で保存(アルファベットしかない場合使用)

  • Comma separated text file -UTF8: CSV形式で保存(日本語がある場合にこちらを使う)

  • Custom Export Format (Configure in Options): カスタム設定したフォーマット(使用しない) なお、ファイルサイズが最も大きいのはUnicode形式、最も小さいのはTab区切りとなる。f:id:marshal115:20200606171058p:plain
    Tab区切りを使ったほうが良いう場合は、例えばコードが「+0567431」のような場合(ExcelCSVを開くと数字扱いになってしまうため)

  • CSV、かつ、Unicode時のPower Queryソースコード(Delimiterは省略可能)Csv.Document(Source, [Delimiter = ",", Encoding = 932])
  • Tab区切り時のPower Queryソースコード
    Csv.Document(Source, [Delimiter = "#(tab)", Encoding = 932])
    ※文字化けの可能性あり(下図参照&後述)

    f:id:marshal115:20200606171402p:plain

  • 出力されたファイルを確認。これでPower Queryから爆速でデータに接続できるようになるf:id:marshal115:20200606171624p:plain

ExcelからデータをCSVとして出力する場合
  1. DAX Studioがインストールされていることを確認(まだ立ち上げない)
  2. Excelを立ち上げる(必ずExcelの中からDAX Studioを立ち上げる)
  3. 出力したいクエリをデータモデルに出力(下記①~④)
    ※リレーションを組まなくてOK(but組んでおいたほうが後々便利)

    f:id:marshal115:20200606181013p:plain

  4. CSV/Textとして出力したいクエリを全てデータモデルに追加
  5. アドイン(Addin)タブよりDAX Studioを立ち上げる(ここがミソ!

    f:id:marshal115:20200606181104p:plain

  6. PowerPivot Modelに接続する

    f:id:marshal115:20200606181136p:plain

  7. 「Power BIからデータをCSVとして出力する場合」の3の手順以降と同じ
複数テーブルをCSVやTextファイルにエクスポートする方法

上記紹介したやり方はファイルを1つずつしか出力できないため、複数ファイルを既にPower Queryで結合した場合には有効です。一方で、複数のファイルをまとめて出力する場合には以下の手順を踏むことなります。

  1. DAX Studioでモデルに接続した状態にする
  2. ファイル > Options > Advanced > Preview Featuresを全て

    f:id:marshal115:20200606181717p:plain

  3. DAX StudioのリボンタブにAdvancedが出現し、①Export Data > ②CSV Files

    f:id:marshal115:20200606181740p:plain

  4. ③Output Path(出力先) > ④区切り形式を選ぶ

    f:id:marshal115:20200606181938p:plain

  5. ⑤出力ファイルを全て選択し、Export

    f:id:marshal115:20200606182100p:plain

  6. ⑥出力が成功したことを確認しClosef:id:marshal115:20200606182139p:plain
留意点

CSVやTextとして出力されたデータをPower Queryから参照する場合、日本語が文字化けになる場合があり、その場合にはEncodingのパラメーターを変更する必要があります(下図)。

f:id:marshal115:20200606182423p:plain

Encoding = 65001はUTF-8形式となり、932は日本語(シフトJIS)形式となる。

f:id:marshal115:20200606182448p:plainf:id:marshal115:20200606182456p:plain

*1:CSV/Text/SQL/Access

*2:ExcelやWord文章、emailの本文、画像、動画等。ただし、Excelスプレッドシートこそ構造的な特性を有するものの、データを自由に入力することができることから非構造的データとして扱う