いつもは週末土曜日の午後にみっちりねっとりと開催している "PBIJP Power Query 秘密特訓「虎の穴」炎の復活編" なんだけど、年末だし、たまにはやってみようかなと思って平日夜に開催。その時に話してもいいかなと想定していたけれども、時間に限りがあることだし、当日に触れることがなかったことについて。
今夜はどんな話をしようかなぁと考えるものの、多くのことは参加している人次第なので、"どんな話をしようかなぁ" から進捗ありません。
— Takeshi Kagata / Power Platform 部 Power BI 科 🛌💤 (@PowerBIxyz) December 8, 2021
夜の Power BI 勉強会 第壱夜 - Power Query https://t.co/MU6UJWvGLf #PBIJP
まぁ、聴くだけ見るだけの参加スタイルを許容していた回なので仕方がないこととは言え、もう少しコミュニティイベントへの参加するという意識を持ってもらえたらよいのにねと思ったなど。発表する、質問する、ディスカッションで意見を述べることは、コミュニティイベントに参加して勉強するスタイルで抜群の効果があるのではないかと思うけどね。
さて、ここから本題。
Power Query を使おうとするとき、もしくは、Power Queryを勉強をしようとするとき、思っている以上にブロック要素になっていることがありそうだなと思うことがあって、うーむと整理をしていた。偶然いくつかの出来事が重なってということでもあり、イベントを振り返って思ったことを含めたい気持ちが行方不明。
Power Query は Excel じゃぁないんだぜ
あえての煽り気味ヘッダタイトル。えっ冗談だろwww大草原 といえなくなることがあったことも事実。多少の皮肉をマイルドにしてつぶやいていたなど。
優秀な Excel ユーザなのに Power Query をいざ使ったときにつまずく。なぜ？どんなこと？みんなで揉んでみたいねと思うのです。話を聞くだけでも別によいんだけどさ、まぁ、会話に参加してよね。
— Takeshi Kagata / Power Platform 部 Power BI 科 🛌💤 (@PowerBIxyz) December 6, 2021
夜の Power BI 勉強会 第壱夜 - Power Query https://t.co/MU6UJWvGLf #PBIJP
愚痴を言ってても誰も得しないので、引き続き煽り気味でケースを挙げつつ。
有名なケースから思うこと
例えば、Excel ワークシート上に Year / Category / Product で集計された Sales があるとき、Year / Category ごとで 最も Sales が高い Product に絞り込むという集計処理。
ここで多くの Excel ユーザは テーブルのソートから処理を仕掛かり始めるのではないかと思うのです。では、なぜ最初にソートすることが必要なのでしょうね？と考えると、答えは "Excel だから"。ソート後に得られるワークシート上の結果に対し、さらに重複の削除することで期待する結果を得られるだろうという認識をしているから。Excel で集計したいのだから "ピボットテーブルを使う" ということが正解なのかもだけど、それが常に最適か否かついてはここでは言及しない。踏まえて、なぜソートから始めるのかを言い換えると Excel には手軽なところに "集計" する機能がないということではないかなと。
で、グループ化して集計する ということを ソートしてから重複を削除 という Excel だけに通用するかもしれない言葉に翻訳してしまっているのでは？と、思うわけ。優秀な Excel ユーザではあるけれども不運なユーザは、このことを認識せず Power Query でも同じことをしてしまうのかもしれないね。
Power Query でソースデータに適用したかったプロセスを Excel だけに通用する言葉で表現したため、思ってもいない結果を得てしまうのだ。Power Query をイイ感じに使えるように勉強するなら いったん Excel のことは忘れた方がよいのでは？とも思うのです。Excel の知識はちゃんと生かされますよ。だって、Power Query を使ってもクエリの結果は Excel に戻ってくるのですから。それでも、Power Query は Excel ではないから、どのように処理をしようかなと考えるときは Excel のことは忘れた方がよいのかもね。
Power Query で考えるなら
グループ化して集計する そのままでよいのだ。なので、Year / Category ごとで 最も Sales が高い Product に絞り込む とういう集計処理でよいのだ。
let
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
ChangedType = Table.TransformColumnTypes(
Source,
{
{"Year", Int64.Type}, {"Category", type text},
{"Product", type text}, {"Sales", Currency.Type}
}
),
// "Year", "Category" でグループ化して、
GroupedRows = Table.Group(
ChangedType,
{"Year", "Category"},
{
"Rows",
// グループ化したテーブルの中で最大値を持つ1行に絞り込み、
each
Table.MaxN(
Table.SelectColumns( _, { "Product", "Sales" } ),
"Sales", 1
),
type table [Product= Text.Type, Sales = Currency.Type]
}
),
// グループ化し最大値を持つ行のみ絞り込んだテーブルを列として展開する。
ExpandedRows = Table.ExpandTableColumn(
GroupedRows,
"Rows",
{"Product", "Sales"},
{"Top product", "Sales"}
),
// 出力にソートが必要なら最後にすればよいんだぜ
SortedRows = Table.Sort(
ExpandedRows,
{
{"Year", Order.Ascending},
{"Category", Order.Ascending}
}
)
in
SortedRows
"ちょっと待って、Power Query エディタ ボタンポチポチでこれできねぇじゃん"って言われたことあるけど、ワークシート関数複雑に組み合わせて使えたり、VBA 読み書きすらできると聞いてるのに なんで Power Query だけ ボタンポチポチなんだよプンスカ💢と軽く逆ギレしたことをふと思い出した。Power Query を勉強しましょうというなかには、Power Query エディタの使い方は含まれる。けれども、Power Query の勉強は Power Query エディタの使い方よりももっと先にあるということを誰もが気づいているはずよね💢誰もがそれを知っているよね💢
気を取り直すと、Power Query エディタボタンポチポチだけで複雑な処理を定義するのは最も難度高い縛りプレイだと思うのです。ボタンポチポチだけでも別に構わないよ。でも、ひとポチごとに行われることすべてを理解し解決するって、かえって大変じゃない？
そういえばで付け加えるけど、使用するデータソースによっては別の記述にしなければならないことはありますよ。同じ順位で評価された場合どうなるのとか。それらも勉強するの重要な項目だ。
よくないなと思うケース
ソートしてから重複を削除 ということことを Power Query でも忠実に再現することは可能なのかもしれない。けど、結果についてはそもそも保証されていないのでは？と思うのだ。それよりも先にパフォーマンスが著しく低下させる記述なので、気持ちはわかるけれども使わないほうがよいよと話すようにしている。わざわざ遅くなるようなことしませんよね💢と。
// テーブルから重複を除外
Table.Distinct(
// なんかわからんけどソートが効かないから
Table.Buffer(
// 一番最初にソート
Table.Sort( ... )
)
)
これには、3つの検討すべき事項があって、適用されるだろうと思いこんている順番でいうと、Table.Sort / Table.Buffer / Table.Distinct それぞれで得られる結果と得られるまで必要な対価や起きる副作用をよく理解していないことがとてもよくない。勉強したらよいねと思うポイントだ。
Power Query 理解したかもというときに起きること
引き続き、優秀な Excel ユーザがよくやってしまうもったいないなと思うことについて。
Power Query という新たな道具を手にしたのに、なぜ、ワークシート上で実現しているロジックをそのまま Power Query で実現したままなのですか？
例えばさ Excel ワークシート上で実現している処理やそのアプローチをPower Queryでも実現可能なことは多い。でもさ、それだと道具の特徴特性を生かしきれないだろうし、Power Queryじゃなくてもよくね？ってなるわけ。
— Takeshi Kagata / Power Platform 部 Power BI 科 🛌💤 (@PowerBIxyz) December 7, 2021
夜の Power BI 勉強会 第壱夜 - Power Query https://t.co/MU6UJWvGLf #PBIJP
手習いのはじめとして、現在使用しているアプローチやロジックを Power Query でも試してみるというのはよいケーススタディだと思う。期待する結果と比べることが簡単ですからね。みんなもやっていることだと思うし、繰り返すだけよい結果につながると思う。で、そこから先に勉強や探求を進めない/進まない/続けないことがもったいないなと思うのです。
Power Query で できること/できないこと、向いてること/向いてないこと、メリット/デメリット のような整理をしたいという気持ちはわかるけれども、Excel から Power Query へという勉強の始まりでいきなりだとちょっと大変だよね。だって、勉強して得られた知識や結果を整理するときのものだもの。
そこで一息入れてよーく考えてみて。Power Query でも同じアプローチ同じロジックが必要なの？と。
手にした新しい道具をもっとイイ感じに使えるようになりたいのなら、
- これまでになかったアプローチがないかも探す
- そのときに必要なロジックを検討する
- アプローチ/ロジックいずれももっとシンプルにできないか検討する
- パフォーマンスもチェックする
- 問題がでたら原因を整理し理解する
この繰り返しだと思うのよ。ロジックを検討する過程で、ちょうどよい関数や使い方はないかな？と探して、試して、どのように動作しているのかな？と研究すればいい。問題があるときは、その理由 とか どのようなことがわからないのかを整理することが大事だよね。
その他