【PowerQuery】パワークエリ1年生～これは便利！と思ったもの

パワークエリ1年生の苦悩シリーズ。

エクセル関数・VBA使いが、パワークエリをやってみて、文化の違いに苦戦しつつ七転八倒の末なんとか中の下レベルまではマスター。（したと思っている）

で、やってる中で「パワークエリって便利だな～」「Excel関数やVBAでやるとしたら結構面倒なのに一発でできちゃうんだ～」等と感動した機能をここに記します。

なお、パワークエリに関しては、クリック操作でやる方法ではなく、関数（M言語）を書くほうが好みなので関数でやる方法を主に書いています。

ちなみになぜ関数（M言語）で書くほうを好むかというと
・クリック操作でやるよりステップ数を短くできる場合がある
・複雑な要求もパワークエリ内で処理できる場合がある
・クリック操作でやるよりなんかデキル人っぽい感じがする
ような気がする、からです。

１．これって便利だなと思ったパワークエリ機能
２．最後に
３．参考文献、記事

１．これって便利だなと思ったパワークエリ機能

（１）改行など削除　Text.Clean

パワークエリエディタで
変換＞書式＞クリーン
または
列の追加＞書式＞クリーン
で指定した列の文字列の改行等を取り除くことができます。

関数は Text.Clean 関数です。

なお、パワークエリでなくエクセル上で普通にセル内の改行を取り除く場合は、

置換：置換ボックスの検索する文字列のところで Ctrl+J を押す
セル内の改行を検索/置換する

関数：=SUBSTITUTE(A2,CHAR(10),””)
関数を使ってセル内の改行を消すには

これはお手軽さではパワークエリのほうに軍配が上がるなあ。

（２）数字（アルファベット）だけ取り出す

【Power Query】Excelで文字列から数字だけ抽出する方法

Text.Select で文字列から数字（またはアルファベット）のみを取り出しすることができます。

構文
Text.Select(text as nullable text, selectChars as any) as nullable text
バージョン情報
selectChars に含まれていないすべての文字を削除したうえで、テキスト値 text のコピーが返されます。
引用元：https://learn.microsoft.com/ja-jp/powerquery-m/text-select

数字のみ取り出し
= Text.Select(“12345abc”,{“0”..”9″})
→12345

カスタム列で使用する例
= Table.AddColumn(ソース, “数値取り出し”, each Text.Select([文字列],{“0”..”9″}))

アルファベットだけ取り出しは、以下のように書きます。
= Text.Select(“12345abc”,{“a”..”z”})

これ、エクセル関数でやろうとすると結構大変。パワークエリだとこんなに簡単にできるとは。

（３）列のピボット解除

これ、一番感動した。見るためにマトリクスにした表って、あれこれ分析したりするには不便。縦型の属性値を持った1データ1行の表が分析の起点の表としてよいのであるが、見るために横展開した表が世の中多すぎる。で、パワークエリだと一発変換できるので、感動した。

やり方は、属性値にしたい列をまとめて選択して、「変換」タブの「列のピボット解除」をクリックするだけ。

「列のピボット解除」と「その他の列のピボット解除」の違いは判る。「列のピボット解除」と「選択した列のみをピボット解除」の違いって何？と思ったところ、こちらの記事を見たらすっきりわかりました。
列のピボット解除ってなんだ (Power Query)

列のピボット解除：非選択の列以外をピボット解除する
その他の列のピボット解除：選択した列以外をピボット解除する
選択した列のみをピボット解除：指定した列をピボット解除する→つまり上記の例で行くと、次の月に10月の列が増えても10月はピボット解除の対象外で、あくまで今回選択した「4～9月」だけをピボット解除する、ということ。
「列のピボット解除」の場合、『非選択の列以外をピボット解除する』なので、非選択の列＝「店舗」以外をピボット解除することになり、増えた10月もピボット解除してくれる。

なるほど。そうなると「選択した列のみをピボット解除」ってつかうことあるのかな？と思うけれども、使うと便利な場面があるのかもしれませんね。

（４）グループごとに集計する

パワークエリを使ううえで、グループ化してなんちゃら。。。は使えると使えないで段違い、と思います。

グループ化を使ううえで、「すべての行」を利用することで、けっこういろいろできます。

たとえば、です。下図のような表があったとします。店舗ごとの平均や最大値、データの個数を集計し、さらに元の表のよこっちょに集計結果を表示したい、なんていうとき。

①集計したいクエリでグループ化したい列を（今回は「店舗」）を選択した状態で、ホームタブ＞グループ化をクリック。（グループ化したい列は後から変更することもできます）

②「グループ化」には「店舗」をセット
テーブル全体を保持しておくための列「tmp」をセットします。列名は何でもいいです。
新しい列名「tmp」、操作は「すべての行」とします。
上部の「詳細設定」をオンにすると、集計が追加できるようになります。
「集計の追加」ボタンをクリックし、平均や最大、行数のカウントの列を追加します。

③店舗ごとにまとまって、店舗ごとの平均や最大値、データの個数が集計されました。
これでオッケーな場合はこれで終わりでよいのですが、今回は最初の表の横っちょに付随情報として店舗ごとの平均などを追加したかったのです。つまり、グループ化の基準とした「店舗」名だけでなく、地域やなんかも表示したうえで、その横にその店舗の売り上げ実績の平均などを表示したかったのです。

そこで！作成しておいた「tmp」列の展開ボタンをクリックします。

④元のテーブルのすべての行が入っているので、表示したい列にチェックし、不要な列（すでに表示されている「店舗」とか）のチェックを外します。好みですが「元の列名をプレフィックスとして使用します」のチェックは外します。「OK」ボタンをクリックします。

⑤あら素敵。こんなに簡単にグループごとの集計を横っちょに表示した表ができました！

これって、エクセルでやるとしたら別に集計して、VLOOKUPで平均値を持ってくるとか、
アクセスでやるとしたら、サブクエリを使ったり、結構面倒。

ちなみにアクセスでやる場合のサブクエリはこんな感じか。（SQL勉強中）

SELECT T1.店舗, T1.地域, T1.月, T1.実績, 
(SELECT Avg(T2.実績) from data As T2 where T1.店舗 = T2.店舗 Group By T2.店舗) As 店舗平均,
(SELECT Max(T3.実績) from data As T3 where T1.店舗 = T3.店舗 Group By T3.店舗) As 店舗最大,
(SELECT Count(T4.実績) from data As T4 where T1.店舗 = T4.店舗 Group By T4.店舗) As 店舗個数
FROM data AS T1;

（５）グループごとに連番を振る

グループごとに連番を振りたい、というときがありますよね。私はあります。

しょっちゅうあるのですが、そのやり方を毎回忘れるのでここに記します。

こちらで勉強させていただきました。

Power Query：グループごとに連番を振る

【Power Query】グループごとに連番をつける方法

勉強はしたものの、何が起きているのか理解できない。コピペするしかない。

下図の表でやってみます（すでにパワークエリエディタに取り込んだ状態です）
同一「店舗」内で日付順に連番を振りたいと思います。
①「店舗」「日付」列で並べ替えをしておきます。

②「店舗」列でグループ化します。
店舗列を選択して、ホームタブ＞グループ化をクリック。

③グループ化＝店舗
新しい列名＝tmp（なんでもいい）
操作＝すべての行
として「OK」クリック

tmp列ができました。Tableをクリックすると中身が見えます。
A支店でまとまったサブテーブルを持っているイメージ、と考えます。

④列の追加タブ＞カスタム列をクリックします。
以下のコードを入力します。

= Table.AddIndexColumn([tmp],”店舗毎連番”,1,1, Int64.Type)

計算式の内容は下図のイメージです。

⑤こうなりました。
カスタム列を展開して、表示したい列を表示します。

⑥こうなりました。連番がふれました。
ｔｍｐ列は不要なので削除します。

以下が詳細コードです。（パワークエリエディタでホーム＞詳細エディタで表示されます）

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル15"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"店舗", type text}, {"販売日", type date}, {"販売実績", Int64.Type}}),
    並べ替えられた行 = Table.Sort(変更された型,{{"店舗", Order.Ascending}, {"販売日", Order.Ascending}}),
    グループ化された行 = Table.Group(並べ替えられた行, {"店舗"}, {{"tmp", each _, type table [店舗=nullable text, 販売日=nullable date, 販売実績=nullable number]}}),
    追加されたカスタム = Table.AddColumn(グループ化された行, "カスタム", each Table.AddIndexColumn([tmp],"店舗毎連番",1,1, Int64.Type)),
    #"展開された カスタム" = Table.ExpandTableColumn(追加されたカスタム, "カスタム", {"販売日", "販売実績", "店舗毎連番"}, {"販売日", "販売実績", "店舗毎連番"}),
    削除された列 = Table.RemoveColumns(#"展開された カスタム",{"tmp"})
in
    削除された列

ここがよく理解できない

追加されたカスタム = Table.AddColumn(グループ化された行, “カスタム”, each Table.AddIndexColumn([tmp],”店舗毎連番”,1,1, Int64.Type)),

グループ化せずに普通にインデックスをふるコードと見比べるとなんとなく納得できるようなできないような。