PowerPivotで100万件超えのデータを取り出す

 EXCEL のワークシートの仕様上,100 万件を超えるデータは扱えない.これは大規模なデータを扱う際の制約である.180万件のデータをPower Queryで処理してEXCELがオーバーフローした話 でも述べたが,この制約を乗り越えてデータをインポートするにはデータモデルに読み込むほかはない.

 SQL Server で PowerQuery が使えればこういった制約を回避できるのだが,ないものは仕方がない.今回は PowerPivot を用いてデータモデルに蓄積したデータを取り出す方法を見つけたので備忘録として記す.

謝辞

 Twitter のフォロワーの中に EXCEL の達人がいるが,ちょっと困っている旨呟いたらヒントを頂けた.

 やってみるとできたので,お礼方々この記事を書くことにした次第である.

Power Query での処理

ピボット解除

 「01204旭川市」などのように,市区町村コードと市区町村名が接続された列がピボット解除の対象である.

 最初に札幌市の列を選択し,シフトキーと右矢印キーを押しっぱなしにして札幌市より右側の列をすべて選択する.

 次に「変換」タブの「列のピボット解除」から「選択した列のみをピボット解除」する.

列のピボット解除
列のピボット解除

列の分割

 「銘柄(平成27年基準)」および「属性」が列分割の対象となる.

区切り記号による分割

 「銘柄(平成27年基準)」はスペースを挟んで銘柄コードと銘柄が接続されている.これを分割したい.

「列の分割」「区切り文字による分割...」
「列の分割」「区切り文字による分割…」

 「区切り記号」はスペースがデフォルトである.「分割」は「一番左の区切り記号」をチェックする.詳細設定オプションは今回使用しない.

区切り記号による列の分割
区切り記号による列の分割

 「銘柄1」という列ができるが,今回は不要であり「削除」する.

不要な列を削除する
不要な列を削除する

文字数による分割

 「属性」は市区町村コードと市区町村が接続されており,これを分割したい.

「列の分割」「文字数による分割」
「列の分割」「文字数による分割」

 「文字数」は市区町村コードの列長の 5 とし,「分割」は「できるだけ左側で1回」をチェックする.詳細設定オプションは使わない.

文字数による列の分割
文字数による列の分割

データ型の変更履歴を削除

 Power Query のおせっかい機能の一つにデータ型を勝手に変換する機能がある.右側のパネルに「適用したステップ」があるが,市区町村コードが数値型に変更されている.これは不要なステップなので削除して履歴を一つ戻す.

「適用したステップ」で型の変更を削除してステップを戻す
「適用したステップ」で型の変更を削除してステップを戻す

列名の変更

 SQL Server にインポートする際には列名を英語に直したほうがよい.

旧列名 新列名
銘柄(平成27年基準)コード ItemCode
銘柄(平成27年基準) Item
時間軸 Date
属性1 CityCode
属性2 City
Price

オーバーフローへの対処は「データモデル」

 例によって 100 万件超えのデータはオーバーフローするので,「読み込み先…」から「データのインポート」を開き,「接続の作成のみ」をチェックし,「このデータをデータモデルに追加する」にチェックを入れる.

「データのインポート」
「データのインポート」

 「クエリと接続」で読み込んだデータ件数がカウントアップされ,最終的に下図のように 1,820,823 件が読み込まれた.

クエリと接続
クエリと接続

DAX式 M 言語

 DAX 式 M 言語は下図のとおりである.

PowerPivot の有効化

 EXCEL の「ファイル」メニュー下部に「オプション」があるのでクリックする.

「ファイル」「オプション」
「ファイル」「オプション」

 EXCEL のオプション左パネルの「アドイン」をクリックする.

「Excelのオプション」から「アドイン」
「Excelのオプション」から「アドイン」

 「管理」から「COMアドイン」を選択して「設定…」ボタンをクリックする.

「管理」から「COMアドイン」を選択
「管理」から「COMアドイン」を選択

 Microsoft Power Pivot for Excel にチェックを入れて OK をクリックする.

Microsoft Power Pivot for Excelをチェック
Microsoft Power Pivot for Excelをチェック

 メニューに Power Pivot が現れる.「データモデル」の「管理」をクリックする.

「Power Pivot」タブ「データモデル」から「管理」
「Power Pivot」タブ「データモデル」から「管理」

ItemCode のデータ型が数値でないといけない理由

 数値フィルターのクエリで「次の値の間」を選択できるようにするため,ItemCode のデータ型は数値型でないといけない.文字列型だと完全一致しか選択できず,次の操作に支障が出る.

数値フィルターを使うためデータ型は整数型にしておく必要がある
数値フィルターを使うためデータ型は整数型にしておく必要がある

カスタムフィルター

 ItemCode に数値フィルターを適用するのは,コピーできるデータ件数にはメモリ上の制限があって,すべてのデータを一度にコピーすることができないためである.

データ件数の分布
データ件数の分布

 下図のように SQL で言うところの BETWEEN 句に該当するクエリを指定する.

カスタムフィルター
カスタムフィルター

すべてを選択してコピー

 セル範囲の左上のマークをクリックしてすべて選択し,右クリックして「コピー」する.

セル範囲の左上をクリックしてすべて選択しコピー
セル範囲の左上をクリックしてすべて選択しコピー

メモ帳に貼り付け

 メモ帳を起動して貼り付ける.もっとも,40万件ものデータをクリップボード経由でペーストすることはできず,端末はしばらく黙り込んだ後,何事もなかったかのように無視されてしまう.件数が多すぎたのだ.

 クエリを変更して件数を絞り込む.20万件くらいに絞り込むとクリップボード経由で貼り付けできるようだ.

メモ帳に貼り付けたところ
メモ帳に貼り付けたところ

ヘッダー付きでコピーされている

 下図はすべてのデータを貼り付け終えたところである.蛍光マーカーで強調したところをよく見てほしい.データ件数が実際よりも多くなっている.

データ件数が実際よりも多くなっている
データ件数が実際よりも多くなっている

 この差分はヘッダー行である.貼り付けた回数分だけ多くなっている.後で SQL Server にインポートする際に邪魔になるので,ここで削除しておこう.

検索でヘッダー行を探す
検索でヘッダー行を探す

 Delete キーを押すこと十数回で余計なヘッダーが削除できる.ファイル名をつけて保存しよう.ここでは ConsumerPrices.txt としておく.

SQL Server にインポート

データベースを右クリックして「データのインポート」

 よく間違えるのだが,直上の「フラットファイルのインポート」ではない.ウィザードのデータソースで Flat File Source を選択する.

「タスク」「データのインポート」
「タスク」「データのインポート」

ウィザードの注意点

 何度かインポートに失敗していくうちに,どこで失敗するか見えてくる.今回はヘッダー行を削除し忘れたのと,データの切り捨てが発生していたことである.

データの切り捨ては詳細設定で修正する

 データソースを指定した後,左側のパネルで「詳細設定」を選ぶ.Item を選ぶと OutputColumnWidth という項目がある.ここで入力側のフィルタリングを行っているらしい.デフォルトは 50 だが,100 にするとエラーが止まる.

「データソースの選択」の「詳細設定」で切り捨ての発生した項目のOutputColumnWidthを変更する
「データソースの選択」の「詳細設定」で切り捨ての発生した項目のOutputColumnWidthを変更する

列マッピングは最重要項目

 とにもかくにも,インポートにおいてはここが最も重要である.データ型,データ長,NULL の可否を決める.

列マッピング
列マッピング

 何度かトライアンドエラーを繰り返し,インポートは正常に終了した.

インポート終了画面
インポート終了画面

まとめ

 100 万件を超えるデータを EXCEL のデータモデルから取り出す方法を公開した.EXCEL 側では Power Pivot が必須であり,メモ帳や SQL Server も必要であった.

 手動でクリップボード経由のコピペというのが課題であり,他にスマートな方法が存在するなら知りたい.

“PowerPivotで100万件超えのデータを取り出す” への3件の返信

  1. ブログを検索していたらPower Queryについて書かれているここに辿り着きました。2点ほどコメントがあります。まず、Power Queryの言語はDAXではなく、M言語です。DAXはPower PivotやPower BIのネイティブ言語で、Excel関数のステロイド版のイメージですが、両者はまったく異なる存在です。もう一つは、Power Pivotからデータを抽出する場合、DAX Studioというツールを使えば簡単にできますので、調べてみてください。このツールであれば、Power Pivot内のすべてのテーブルを一括で出力できたりします。確か”180万件のデータをPower Queryで処理してEXCELがオーバーフローした話”のほうでもM言語をDAXと間違って記載されていたようですので、ご確認ください。
    https://docs.microsoft.com/ja-jp/powerquery-m/

    1. 有益なコメントありがとうございます.
      M 言語が正式名称とのご指摘,ありがとうございます.また DAX Studio のご紹介もありがとうございます.
      調べてみます.ありがとうございました.

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください