パワークエリ1年生の苦悩シリーズ。
エクセル関数・VBA使いが、パワークエリをやってみて、文化の違いに苦戦しつつ七転八倒の末なんとか中の下レベルまではマスター。(したと思っている)
パワークエリはクリック操作でいろいろできます。クリック操作であれこれやったものはM言語というもので自動的に記述されており、数式バーまたは詳細エディタをみると中身を見ることができます。
このM言語というものを理解できるようになりたくて、けっこうジタバタしました。そして、そこそこ理解できるようになった今、「M言語わかるとかなり便利だ」ということを実感しています。
ここに、パートのおばちゃんがM言語をふんわりと理解した軌跡、および、利点などを記します。
ふんわりと理解することを目的としているため、あんまり細かいことはやりませんのであしからず。
1.M言語を使える利点
M言語使える利点を記します。
- コピー、ペーストでの操作の再利用が可能
- ステップ数を短くできる場合がある
- クリック操作ではできない複雑な処理も可能になる
- ワークシートの値をパワークエリ内で利用したり、多様な要求を処理できる
- M言語を読むことでステップの内容が理解できる
- なんか「パワークエリ デキル人」感がマシマシになる
私は一番最後の「パワークエリ デキル人」感がマシマシになりたい欲望のために頑張りました。
ほかの人が作ったパワークエリの内容を読み解く必要が出てきた場合、ステップ名だけで内容を読み解くことは不可能だと思うので、M言語を少し読めるようになっておくことは重要なことだとも思います。
2.M言語はどこにあるか
パワークエリエディタ>ホーム>詳細エディター で選択しているクエリの中身が見れます。
これがM言語です。

または、数式バーでも1ステップ分は見れます。
3.M言語と仲良くなったきっかけ
M言語を眺めてみても、なんだこりゃ、さっぱりわからん。だったんです。
let in ってなんなの????
イコールがあって、関数っぽい。しかし、いわゆる関数って式に引数を放り込んで、戻り値を返すものだよね?なんか全然違うんだけど、私の知ってる関数じゃない。
と、さっぱり親しめませんでした。
しかし、ある時、
「そうか、これはテーブルを引数にとって、何やかややって、なんやかんややった結果のテーブルを戻り値として返すのか」
と、気が付いた。
それからは、なんか一気に「全然知らない人」から「ちょっとした知り合い」になった感じ。
たとえば、上の図の「変更された型」のステップを見てみると、下図の解釈ができます。

関数 Table.TransformColumnTypes に引数として前のステップのテーブル「昇格されたヘッダー数」を渡しています。そして、第二引数に「この列をこうして、この列をこうして。。。」という指示をリスト形式で渡しています。
Microsoft learn を見てみると、以下の構文となっています。
Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table
引用元:https://learn.microsoft.com/ja-jp/powerquery-m/table-transformcolumntypes
Table.TransformColumnTypes(テーブル名, 変換リスト) と指定します。
変換リストは {{列名1,型},{列名2,型},{列名3,型}} と書きます。
もうひとつ。上の図の「変換された型」の次の「フィルターされた行」のステップをみてみます。
下図のように解釈しています。

関数 Table.SelectRows に引数として前のステップのテーブル「変更された型」を渡しています。
そして、第二引数は each [販売個数] >= 50 としています。
each は「テーブルのすべての行に1行ずつなんやかんや処理するよ」という意味でとらえています。
今回の場合はすべての行に対して一つずつ「50以上かどうか」という判定をしています。
50以上であればTrueとなり、行を残します。50未満であればfalseとなり、行は削除されます。
そして、取捨選択した後のテーブルが戻り値として返されます。
返されたテーブル(左辺)には「フィルターされた行」という名前が付けられています。
どうでしょうか。なんとなーくふんわり分かったような気になりませんか?
4.M言語の基本
①Let で始まり in で終わる。
これってなんなの?
Power Query の M 式言語のクエリは、マッシュアップ クエリを作成する式の手順で構成されています。 式は評価 (計算) されて、値を生成することができます。 let 式には、計算され、名前を割り当てられた後、in ステートメントに続く後の式で使用される、一連の値がカプセル化されています。 たとえば、let 式には、Text.Proper の値と等しく、適切な大文字と小文字のテキスト値を生成する、Source 変数を含めることができます。
引用元:https://learn.microsoft.com/ja-jp/powerquery-m/expressions-values-and-let-expression
読んでもよくわからん。
が、ともかく決まり文句と思って let で始まり in で最終結果を出す!これがないとエラーになる。
let~inの間が一塊の処理で、その処理に名前が付けられる(=クエリ名)。
let~inの間のひとつずつの処理はそのクエリを構成する「ステップ」であり、ステップ名が付けられる。
inの後に書かれたものが一塊の処理の最終結果として表示されるテーブルである、ということですね。(テーブルではない場合もある)

②ステップの終わりにはカンマがないと怒られる。ただし、inの直前のステップはカンマがあると怒られる

③大文字と小文字の違いに厳格
VBAや関数みたいに、大文字と小文字間違えてたら気を利かせて直してくれることなく、一発退場エラーになる。
例えば、上記の例の一番最後のステップの Table.SelectRows を table.SelectRows とすると、エラーとなります。大文字小文字には気を付けましょう。

③カラム(列)は [] 、行は {} で表される
なんか、これ覚えておくと、いいです。何かと使えます。
例えば下図のように 前のステップ名[列名]{行番号} で一つの値を取り出しすることができます。
なお、行番号は0から始まります。1番上の行が0行目、2番目の行が1行目となります。

5.M言語を操作すると便利だという例
(1)処理の再利用
他のクエリで同様の処理をしたいとき、コピーペーストで簡単に処理を再現することができます。複数ステップを同時に移植できるので、便利です。
たとえば「売上実績2月」のクエリで、①カスタム列if式でランク列を追加②ランクでグループ化③ランク列昇順で並べ替え、の3つのステップを作成しました。

「売上実績3月」クエリでも同様の処理を行いたい。そんな時は「売上実績2月」クエリで詳細エディターを開いてその部分のステップをコピーし、「売上実績3月」クエリで詳細エディターに張り付けします。右クリックのコピーでも、ctrl+Cでも大丈夫です。詳細エディターは「完了」で閉じます。

次に「売上実績3月」の詳細エディターを開きます。
inの前に改行で1行分のスペースを空け、そこに先ほどコピーしたコードを貼り付けします。

このままだとエラーが出ているので、調整します。
つなぎ目にカンマを追加。inの後のステップ名を最終ステップ名に差し替えます。


コピペして、前後を調節だけで複雑な処理も再利用できるので。これ、ほんと便利。
(2)ソースが変わったときにコピペで差し替え
(1)と同じではあるのですが、前回までデータソースがエクセルだったのに、急に「今月からCSVになりまーす」などというとき。データ取り込みして、クエリで加工して、ほかのクエリと結合したり、ピボットで集計してとか一連の流れをくんでいたのに、最初っから作り直しかよ~、と絶望。。。しなくていいんです!これもコピペで対処できます。
これまでエクセルで吐き出されていた元データが、システムの都合でcsvで吐き出されるようになった。
先月までファイルを入れ替えるだけで更新できたのに、ファイル種類が変わったためエラートなってしまった。

気を取り直して、csvファイルを読み込みします。

読み込んだCSVファイルのクエリ「売上実績ver2」を選択し、ホーム>詳細エディター をクリックします。
csvファイルを読み込んだ部分、ソース~変更された型 までのコードをコピーします。

エラーとなっている「売上実績」クエリの詳細コードを開き、ソース~変更された型までの部分を先ほどコピーしたコードを貼り付けして差し替えます。
すると、エラーとなっていた「売上実績」クエリが何事もなかったかのように立ち直ります。
※カンマが足りなかったり、前後のステップ名が違っていたりしたら修正します。
csvを読み込んだだけの「売上実績ver2」は不要になるので削除します。
これでちょっと元データの状況が変わったからと言って、いままで作り上げてきたクエリがおじゃんという状況から回避できます。これは便利です。
(3)ワークシートの値などを利用する
たとえばクエリ内でフィルターするとき、ワークシートの値を参照出来たら便利だなあと、思ったりします。そんなときもM言語をちょっといじれると、サクサクできるようになります。
たとえば、シートに入力したランクだけを表示するクエリを作成したい、と思ったとします。
絞り込みするランクを入力するエリアを作成し、テーブル変換して、そのテーブルをクエリに読み込みします。

読み込みしたテーブル内で、右クリック>ドリルダウン をクリックします。
するとテーブルが単純な値のみになります。

ドリルダウンとは。。。
ドリルダウンとは、データの集計レベルを1つずつ掘り下げて集計項目をさらに詳細にする操作のこと。またはその機能。
例えば、国別に見ていたデータを都道府県別に集計し直すなどの操作がこれに当たる。引用元:マーケティング用語集ドリルダウン
「日本」で見ていたものをクローズアップして「東京」で見てみる、とかそんな感じ?
今回のようなパワークエリでのドリルダウンの操作は『テーブルの中の一つのセルに着目して取り出す』といった感じかと捉えています。
さて、本題に戻って。
絞り込み処理をしたい「販売実績」のクエリで、クリック操作で[ランク]列を「A」でフィルタします。

数式バーを確認します。
each [ランク] = “A”
となっています。
ここの ”A” の部分を、ドリルダウンした「ランク」クエリ名に差し替えます。
数式バーの左の✓をクリックして数式を確定します。

ホーム>閉じて読み込む をクリックしてエクセルに戻ります。
ランクの部分に「B」と入力し、データ>すべて更新 でクエリを更新します。すると、売上実績クエリの結果が変わります。

エクセルシートの値の利用については、こちらで詳しくやっていますので、よろしければこちらもどうぞ。
【PowerQuery】エクセルシートの値をクエリの中で利用したい
6.最後に
そんな感じで、M言語をちょっとさわれると、かなりパワークエリの操作の時短ができます。
私自身、完全にM言語を理解できているかというと、まだまだなのですが、今後あれこれトライしてみてもっと理解を進めたいですね。
お読みいただきありがとうございました。M言語を理解する一助となったら幸いです。
7.参考文献、記事
Microsoft Learn より
Table.TransformColumnTypes
Table.SelectRows
式、値、および let 式
M言語を調べていたら、パワークエリの問題集に当たりました。面白そう。
内部リンク
【PowerQuery】エクセルシートの値をクエリの中で利用したい
パワークエリはこちらの書籍で勉強しました。お勧めです!
コメント