PowerQueryでデータ更新する時に時間がかかる時の対処法

URLをコピーしました！

仕事でExcelでPowerQueryを使ってデータの取り込みをして、集計、加工をしています。VBAを使ってその処理をしているのですが、更新に異常に時間がかかることがあり困っていました。

抽出後のサイズは400列でデータ数は18,000から40,000件程度です。システムからダウンロードした100万件以上のCSVを一旦10万件ぐらいに絞んで抽出。メモリ8GBでCorei5のパソコンを使っています。かからない時は1分程度で終わるのに日によって更新に10分以上かかることがありました。右下に「セル」という表示がパカパカと出たり消えたりして、なかなか終了しない。困りました。調べてもなかなか答えが見つからない。

列数が多い場合に起きるようです。

色々試して、解決法が分かりました。しばらく運用して調子よく使えているのでご紹介します。

対処法

手動の場合

更新のまえに、テーブルの範囲内にカーソルをおき、デザインメニューから「テーブルのサイズ変更」でテーブルの行数を予想される数以上に広げておきます。

VBAの場合

「Refresh前に、テーブルのデータベース領域を更新後のレコード数より多めの数に拡張する」

Worksheets("シート名").Listobjects("テーブル名").Resize Range("A1:pz"&myRow)

の部分です。これで劇的に改善されました。多めに拡張しても、Refresh後にはリストの最下行まで縮小されます。

色々試してみて分かったのですが、私のケースではどうやらデータ数が１件でも増える場合に異常に時間がかかるようなのです。書式の設定をして値を埋め込み直すのに時間がかかっているのでしょうか。真の原因はよく分かりませんが、何にせよ解消されたのでホッとしました。

注意点

ただしVBAからクエリと接続を更新する場合の注意点として、データタブ⇨各接続を右クリックして表示されるプロパティで「バックグラウンドで更新する」のチェックを外すようにしてください。そうしないとデータの処理が完了していないのに次の処理が走ってしまい、元も子もない状態になります。

余談ですが「列幅を調整する」のチェックも外しています。折角調整した列幅が変更されるのが、都合が悪いからです。こちらは、データタブのすぐ下あたりの「プロパティ」から変更できます。

コード

参考として私が作成したサンプルコードを掲載します。

Sub Sample()
  Dim myRow As Long
  Dim myList As ListObject
  myRow=Worksheets("シート名").Range("A1048576").End(xlUp).Row+500
  Worksheets("シート名").Listobjects("テーブル名").Resize Range("A1:pz"&myRow)
  Set myList=worksheets("シート名").ListObjects("テーブル名")
  myList.Refresh
  Application.Wait Now()+TimeValue("00:00:05")
  DoEvents
  ’テーブル更新後に実行したい処理
End sub

備考

実際のコードから抜粋して必要な部分だけ簡略化しました。
Office365のExcel2016を使っています。
A1048576の部分は、使用可能最大行がExcelのバージョンによって異なるのでそこは環境によって変更が必要かと思います。このデータはpz列までのデータなのでpzとしていますが、使用するデータによってご変更ください。
私の場合500件を超える増分は無いので＋500としましたが、多すぎても拡張するのに時間がかかるのでケースに応じて更新時の増分が収まるぐらいで少なめの数字を入れるとよいでしょう。ほどほどに良さそうな数値を探ってみてください。
WaitとDoEventsを入れているのは「VBAから更新した場合にデータ⇨クエリと接続から更新時刻を確認した時に、リストは更新されているのに最終更新時刻の表示が更新されていない」問題を解消するためです。
PowerQueryを挿入すると自動的に設定されるテーブル範囲が挿入されます。テーブル名はデザインタブ⇨右端の方の名前を確認してください。

お気に入りのExcel書籍・サイト

最近は仕事でVBAをひたすら使うことが多くて通勤中や休日に勉強をすることが多くなりました。いつもお世話になっているお気に入りの本やサイトをご紹介します。

ExcelVBA実践のための技術

この本は電車の中で何度も読み返しました。ブックカバーを綺麗にしておきたかったので、ダサいですがA3用紙で自作のカバーを作って持ち歩いていました。オススメの本です。
オブジェクトの考え方やクラス、関数の使い方について一歩つっこんで解説されています。入門書はたくさんでていますが、このような中級者、実践のための本はなかなかありません。ExcelVBAに慣れてきた方向けの本だと思います。初めての方は入門書を１、２冊やってみてそれから読んで見られるのがでしょう。