Excel の "取得と変換"機能を用いて、データを収集し集計に適した加工、そして、ピボットテーブルやピボットグラフなどを作成する。データの更新をサポートする仕組みなので、取得したデータも含めた Excel ワークブックを保存するというのもよいでしょう。では、作成した "クエリ" だけを保存するにはどうしたらよいか。
詳細エディターと記述した/された Power Query(M言語)をコピペするでもよいのだけど、それなりの機能が用意されているので使えばいいじゃない？使いまわすというか何度も同じクエリを作ることもなく、必要な時に取り出して、組合せて使えるようにしときましょうねといういう話です。
サンプルのクエリ
例えば、地方や都道府県の一覧になるクエリを作成したとして
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"bdVdcqJAEAfwu/icBwERPIuVu+yW+dCVqMmuxpQQEhOMmxgjuFmVDfEy9ODcYochzoxV/TBVgL/qnvrbDPV6CZxr8r6i33+CuyCDTelIPmHXGl/HR/US8UL2g0DUuyIP88x12LXOF4Ig+k1abYkMFM3n4HckqmAoe2pnvxYSmWilMIRkLFEVrTTpwvKvRBZHdDDO3R7tnKnYk8GWjSFy1yDuVKIahrLPRzqZSaSVMQX+R9ZpKUpD1UVjd6kqHd2WF6bxjDaSvTLQfQW3EDRh5SnliuTT9SttTGW5wYIkfqEquTIxBW8OS19RVUxl/lJ05MpC1aSbxn1F2WjHMCTjqaJqmKL9FT3vSKWX0VpRjw5dRWloLe8GontF6WheJ6PMDxRVxL/bXmb9rVDpukXPLwpl5qqCKfKvvVt+U5SJ1uL/NsTuXlUxBY9PdPisKAtVp+8QvygdbVQFzV3rTVE1VF055FWORQ4NET+MEhnsMoDuoFDVXGmYYm8tudsoSkdVdC86cmWgKm6IQ4CrCqrYedJ9UFQRP4xGB+pzKWpZuapiik5uxOhzZWGKDQ88jxRlo7VehmLEuPoa/Y0Hq9uDg24/rnY+jGVMpUlPjBhXGqbYawR/OorS0Y5nP4g7U5SBKTaI0DxTVAVV7Ktw0NFE97XZwmlPxM/h16kfDbL1iQyW3xaqlisW//F/",
BinaryEncoding.Base64
),
Compression.Deflate)
),
let _t = ((type text) meta [Serialized.Text = true]) in type table [地方 = _t, 都道府県 = _t, idx地方 = _t, idx都道府県 = _t]
),
ChangedType = Table.TransformColumnTypes(Source,{{"地方", type text}, {"都道府県", type text}, {"idx地方", Int64.Type}, {"idx都道府県", Int64.Type}})
in
ChangedType
クエリ エディター で "空のクエリ"を作成し、詳細エディターへまるっとコピペすればこうなる。
そして、クエリ名を設定して[閉じて読み込む]
※ちなみに、
このクエリはデータソースへの接続がなく、データ自体を埋め込んでいるのでいつでも使えるのです。
謎の文字列は展開していくとJSON形式で記述されたデータテーブルになり、Power BI Desktop の "データの入力","テーブルの作成" 機能を使って記述したもの。Excel のクエリ エディターにはない機能なんだけど、コードを移植する体でExcelでもテーブルの編集ができるようになるんですよね。
Office データ接続ファイル
Office データ接続ファイル(.odc) は データソース接続情報などが含まれる定義ファイル。
この接続ファイルには、クエリ エディターで記述 された/した すべてのステップが埋め込まれるのでこれを管理すればよい。
接続ファイルのエクスポート
[接続ファイルのエクスポート] で接続ファイルをエクスポートする。
既定では "My Data Sources" フォルダに保存することになる。どっか他に保存してもよいのだけど、使うときにショートカットが作成されるなどするので既定の場所を使うのがよいかと。
[データカタログに送信] は Power BI データ カタログ(Power BI Pro)こと Microsoft Azure Data Catalog へ発行する。
インポート
Office データ接続ファイル(.odc)として保存したクエリを使うときは、[データ] - [データの取得と変換] - [既存の接続] から。Office の機能更新によっては表記が異なることがあるけど外れたところにはない。
こんなときも大丈夫
クエリが参照しているときはどうなるか
クエリが別のクエリを参照しているとき、参照元のクエリもエクスポートした Office データ接続ファイル(.odc)に含まれる。含まれた複数のクエリのインポートもされる。
関数化したクエリ
ユーザ定義関数(UDF)としたクエリもエクスポートできるので、機能や役割で分割しておけばよいかと。インポート後関数を呼び出し、パラメータを投入するなどしてクエリ結果を得ることもできる。
異なる環境でも使いたい
Power BI データ カタログ
Power BI Pro に付帯する共有機能。本来というか製品/サービス的にはこちらを使うべきなのでしょう。
機能自体は Azure Data Catalog が使われているので組織内メンバーに共有することができるし、自分用にすることもできる。アセットの管理が統合されたようなのでそのうちに。
OneDrive
いつでもどこでもというわけでもないんだけど、作ったクエリは席に戻ったとき別の環境でも使いたいなと思ったので。Office データ接続ファイル(.odc) はテキストエディタで開くことができるシンプルなものだし、ファイルをコピーしてするということでもよいのだけど、なんか面倒なので OneDrive で解決する。
OneDrive に Office データ接続ファイル(.odc)を保存するフォルダを作成して、そこに放り込んでおきたい。そして、そのフォルダは [このデバイス上で常に保持する / Always keep on this device] にしておけば勝手に同期してくれる算段。OneDrive に作成したフォルダを 既定のフォルダ "My Data Sources" 内に ジャンクション とした。
フォルダのショートカットを使っても期待する使い方はほぼできるけれども、クエリ インポートのたびにショートカットが作成されてしまってスマートじゃないから。