クエリのエクスポート/インポート
私はあまりやらないのですが、こんなこともできるよ的な機能です。Power Queryで作成したクエリは、そのブック内に保存されます。あるブックで作成したクエリと、まったく同じクエリを他のブックでも使いたいときは、作成したクエリを「エクスポート」し、新しく同じクエリを利用したいブック側で「インポート」できます。
これ、どんなとき便利なのかといえば、別にどんなときに使っていただいてもけっこうなのですが、たとえば、毎月とか定期的にCSVファイル形式でデータが提供されると。そのCSVデータに対して、毎回同じ処理をして保存する、などのケースで威力を発揮します。エクスポートやインポートの具体的な手順などは後述しますが、まずは全体の流れをご紹介します。
上図のようなデータがあったとします。こんな感じのデータを毎月など定期的に受け取るんです。一般的な操作では、まずこのCSVファイルをダブルクリックします。
上図が、Excelで開いたところです。このデータを、毎回次のように処理します。
今回行った処理は、
- A列の8桁数値(日付もどき)をシリアル値に変換する
- [数値2]列を削除する
- [数値3]列を[数値1]列の左に移動する
- C列のコードから、ハイフン(-)で挟まれた記号だけ抽出する
- 抽出した[記号]列を右端に移動する
です。これを毎回やるとします。では、始めましょう。手順に注意して行ってください。まず、クエリを作るために、最初のCSVをダブルクリックしてExcelに読み込みます。
上図のように、アクティブセルがセルA1(表の中)にあるはずですから、この表をテーブルに変換します。これは、Ctrl + Lか、Ctrl + Tを押すと便利です。
実行すると下図のようになります。
ここで重要なことは、テーブル名が「テーブル1」であるということです。後述しますが、既存のクエリをインポートして再利用するには、そのクエリが実行できる状況でなければいけません。まぁ、このへんの話は後述します。さて、テーブルが選択されているはずですから、[データ]タブの[テーブルまたは範囲から]を実行して、選択したテーブルをPower Queryエディタに取得します。
ここからの操作は、特に決まりはありません。面倒くさかったら、何か適当にやってください。ここでは、上記の4処理を行います。まず、日付もどきを日付に変換します。この手順は、下記ページをご覧ください。
最初にあった[日付]列を削除して、新しく作った[日付.1]列の名前を[日付]に変更します。
[数値2]列を削除して、[数値3]列を[数値1]列の左に移動しました。続いて、[コード]のうち、ハイフン(-)で囲まれたアルファベットを抽出します。
[変換]タブの[抽出]をクリックして[区切り記号の間のテキスト]を実行します。
[開始区切り記号]と[終了区切り記号]の両方にハイフン(-)を指定して[OK]ボタンをクリックします。最後に、アルファベットを抽出した列の名前を[記号]に変えて、右端に移動します。
完成です。繰り返しますが、ここで行ったPower Queryによる変換の内容は、あくまでサンプルです。操作などで悩むようでしたら、無理して同じことをしないでもいいです。さて、完成したデータをExcelに読み込みます。ここでは「新規ワークシート」に読み込んでみましょう。
クエリのエクスポートとインポート
さてさて、本題はここからです。いま作成したクエリを、独立したファイルとしてエクスポートして、別のCSVデータでも同じ処理をやってみます。まずは、エクスポートから。
今回作成したように、ブック内にクエリが存在していると、Excelの右端に「クエリと接続」というサイドペインが表示されます。
もし表示されていないようなら、[データ]タブの[クエリと接続]ボタンをクリックしてください。
今回作成したクエリは「テーブル1」という名前です。この名前は自由に変更できますが、今回はこのままいきます。クエリを右クリックして、表示されるメニューから[接続ファイルのエクスポート]をクリックします。
実行すると、[名前をつけて保存]ダイアログボックスが表示されます。
ダイアログボックスで最初に表示されるフォルダ(つまり、Excel的に"ここに保存してはどう？"というお勧めフォルダ)は、C:\Users\ユーザー名\OneDrive\ドキュメント\My Data Sourcesです。あるいは、環境によってはOneDriveではない、ローカルPCのフォルダが表示されるかも知れません。このへんの仕組みは、下記のコンテンツをご覧ください。
VBA Tips - WIndows 10でエラーになるマクロ
いずれにしても、ドキュメントフォルダの下にある[My Data Sources]が最初に開きます。これは、大昔からの仕様で、実際にはどのフォルダに保存してもOKです。ただ、この"お勧めフォルダ"に保存しておくと、あとで「インポート」するときに便利です。ファイル名は何でもいいですが、ファイル形式は「Officeデータベース接続(*.odc)」一択です。[保存]ボタンをクリックすると、クエリだけが独立したファイルとして保存されます。
これでエクスポートは完了です。では次に、このクエリをインポートしてみましょう。ここでは、新しく「7月.csv」を受け取ったとします。
この「7月.csv」に対して、先と同じ処理を行います。しかし、当然ですけど、CSVファイルには、あらかじめクエリは存在していません。まずは、「7月.csv」をダブルクリックしてExcel上に開きます。
ここで、大事なポイントがあります！このデータをテーブルに変換しなければなりません。さらに！クエリで操作したのと同じ名前のテーブルにします。この作業を忘れないようにしてください。
先にエクスポートしたクエリは「"テーブル1"という名前のセル範囲をPower Queryに読み込み～」という内容です。具体的には、次のように記録されています。
したがって、エクスポートしたクエリを、そのままインポートして使うには、処理するブック内に「テーブル1」という名前のテーブルが必須です。(毎回同じ名前のテーブルに変換するなんて、何だかちょっと面倒くさいかもぉ～)って感じた方、心配ありません。CSVファイルをExcel上に開くと、表は1つしか存在しません。その表に対して、Ctrl + L(またはT)を実行すれば、そのテーブルの名前は必ず「テーブル1」になります。
準備完了です。あとは、保存したクエリを「インポート」します。ただし「インポート」という名前のボタンはありません。ここ、すごく分かりにくいのですが、保存したクエリをインポートするときは、[データ]タブの[既存の接続]をクリックします。
実行すると、次のダイアログボックスが表示されます。
このダイアログボックスに表示されるのは、ドキュメントフォルダの下にある[My Data Sources]フォルダです。上記で、クエリをエクスポートするとき、"お勧めフォルダ"である[My Data Sources]に保存しておくと、インポートのときに便利ですと書いたのは、これが理由です。もちろん、[参照]ボタンをクリックして、任意のフォルダに保存したクエリを開くことも可能です。さて、インポートしたいクエリを選択して[開く]ボタンをクリックすると、どこに読み込むかを選択するダイアログボックスが表示されます。
このダイアログボックスはお馴染みですね。特に深い理由はありませんが、ここでは「新規ワークシート」を選択してみます。
完成です。「6月.csv」に対してPower Queryで行った変換処理が、一瞬で実現できました。なお、上図を見てお気づきの方もいるでしょう。Power Queryエディタで編集した結果を「新規ワークシート」に読み込むとき、新しく挿入されるシートの名前はクエリ名になります。対して、クエリのインポートによる「新規ワークシート」では、Sheet1・Sheet2…SheetX となります。もし「新規ワークシート」で挿入されるシートの名前を、何か特定の文字列にしたいのでしたら、手動操作またはマクロで変更してください。クエリで「新規ワークシート」の名前を指定することは不可能です。
今回は「クエリやマクロなどが含まれていないCSVファイルに対して、毎回同じ操作をするようなとき、クエリのエクスポート/インポートを使うと便利ですよ」というテーマでお話しました。しかし、本稿を執筆するにあたって、あれこれと試したところ、このエクスポート/インポートって、かなり便利な気がしてきました。フォルダ内の全ファイルを結合する「フォルダーから」をエクスポートしておけば、そのクエリを新規ブックにインポートすることで、常に最新の結合データを得られます。また、試してみて驚いたのですが、あるクエリに対して「クエリのマージ」などを行っていると、元のクエリをインポートするだけでマージする側のクエリも自動的に読み込まれます。これ、正直言って、できないと思っていました。いやぁ～便利な世の中になりましたね～長生きはするものですｗ