20221126パワークエリ備忘録(接続専用は更新できない!?)
素人です。よく使うエクセルは「エクセル2016」です。
よく本などで、「パワークエリ」を使うと、読み込み先として、
「接続専用」にすると、(結果の)テーブルが新たに作られないで、
データサイズも節約できていいように、勧められている。
(多分、複数のデータを使う場合とか接続専用にしたくなる。)
でも、単に、非表示というだけではない。多分。
**************現象など
接続専用の状態で、
しかし、「クエリと接続」のペインを出して、右の更新アイコンをクリックしても
「接続専用の更新できません」となる。
(なんで接続専用だと更新できないのと焦るところ。)
(下の画像は接続専用にしてません。)
(これは、操作者のミスやバグではなくて、
そもそも接続専用のクエリは更新できませんよという
ことのようだ。)
この場合でも、パワークエリエディタを使って
(その時点でエディタにはデータが読み込まれ反映している)
閉じて読み込めば接続専用でも更新が可能。
(結果は、接続専用からテーブルに読み込み先を変更すれば見られる。)
↑ここが仕組みのややこしいところではないか。
ちなみに、接続専用にしても「データモデルに追加する」と更新アイコンで更新できます。
(注:エクセル2016はデータモデルのタブがないので分かりにくいです。)
かなめだが非常にややこしい。
*****************分かったこと・ヒント
officeTANAKA様のHPで、「クエリを削除する」というページにヒントがあった。感謝。
(削除についてなので、直接ではないが)
記事の中に、クエリの関係について記載があった。
officeTANAKA様も本当に正しいかどうか分からないようだが、そのページに
「元データ(CSV)」→①→「クエリ」→②→「エクセルデータ」
のような橋渡しの図があり、
①を接続
②をリンク
と呼んでいた。
自分は、
「ア:元テーブル」→①→「イ:クエリ」→②→「ウ:できたテーブル」
とします。
TANAKA様の解説で考えると、この関係図で、「イ:クエリ」を削除すると
「ア:元テーブル」と「ウ:できたテーブル」は、無関係のテーブルとして
独立して残ります。
さらに、TANAKA様の解説では、「ウ:できたテーブル」のデザインタブで、
リンクを解除すると、
「ア:元テーブル」と「ウ:できたテーブル」は残り、②が切れるとあります。
(「シートからクエリ定義は削除されます。続けますか?」のメッセージが出る。)
この操作はパワークエリと関係ありません(テーブルの操作)が、
「ウ:できたテーブル」は、独立しているので、これはこれで便利かも。
(この場面ではないが、ピボットテーブルなど、直接作れない場合とかあったような・・・。)
上記②のない状態で更新しようとしても、「クエリの接続はありません」と出る。
(これは、クエリのリンクはありませんと読み替えた方がいい。)
そのまま続けると、「ウ:できたテーブル」にまたリンクがつながり、復活。
*****************まとめ
ここで、改めて「接続専用」について考える。
「ア:元テーブル」→①→「イ:クエリ」→②→「ウ:できたテーブル」
接続専用は単純に②を表示しないイメージだが、
実際は①を停めるようだ。
①を停めて②も表示しない(接続専用)
だから、「ア:元テーブル」に対して、クエリと接続のペインの更新アイコンからはできない。
(クエリエディタからは可能)
*************まとめ追加・データモデル
接続専用でも「データモデルに追加すると、簡単に更新ができる」理由を考える。
そもそもデータモデルは約100万行のシートを使わず約20憶行のデータを保持できる領域を直接使う。
つまりそもそも「ア:元テーブル」を使わない。
「エ:データモデル」→③?→「イ:クエリ」→②→「ウ:できたテーブル」
多分アや①の仕組みが根本から違うのだろう。
分からないが、心配ならデータモデルに入れてもいいかもしれない。
データモデルに入れないなら接続専用は、元データとも切れた存在で、非表示なだけでなく、
非表示なら、元データにも更新できなくていいという考えなのか・・・ややこしい。