Power Query：行のグループ化 その１

名前だけでは分かりにくいけど、クエリ内で何かを集計したい時に使う機能。

Power Queryでよく使う機能といったら、「クエリのマージ」「列のピボット解除」「行のグループ化」「インデックス列の追加」「列のピボット」の５つ。今回はその３つ目。

Excelを使っている人がクエリに初挑戦して、まずとまどうのは「集計」に関する機能が見つからないことだろうと思う。まさか「グループ化」がそれだとは思うまい。

「列1」「列2」の２列のテーブルがあるとして、「列1」には区分に類する情報が入力されているものとする。今回は「列1」を指定してグループ化を実行する。

「列1」を選択した状態で右クリック［グループ化］

すると、グループ化ダイアログが表示されるので、まずは「列1」が表示されているのを確認する。もしグループ化する列を複数指定したい時は「グループ化の追加」をクリックすれば、順次追加できる。

「操作」は全部で８種類（＋２種類※）ある。

操作の名前

合計（List.Sum）： 列の合計を計算する。
平均（List.Average）：列の平均値を計算する。
中央（List.Median）：列の中央値を計算する。
最小（List.Min）：列の最小値を計算する。
最大（List.Max）：列の最大値を計算する。
行数のカウント（List.Count）：行数を計算する。
個別の行のカウント（List.Count/List.Distinct）：重複を除いた行数を計算する。
すべての行：集計せずにグループ化されたテーブルをそのまま出力する。

この中から実行したい操作を選択する。デフォルトでは「基本」が選択されているので、新しい列名の下に操作行は１つだけ表示されている。集計列を追加したい時は「詳細設定」を選択すればいくらでも増やせる。

※Power Query Onlineには、この他に「個別の値をカウントする」「パーセンタイル」がある。

合計～最大までの操作では集計対象の「列」を別途選択する必要がある。

操作の最後にある「すべての行」はテーブルを返すので、何もせずにそのまま読み込むと結果が何も表示されない。

なので、読み込む前には何らかの操作が必要になる。よく使われるのは「行のグループ化」→「グループ列にインデックス列を追加」→「列を展開」→「列のピボット」という流れ。この辺りの操作はマウス中心でやるなら多分必須になる。

nandemo-xl.hatenablog.com

nandemo-xl.hatenablog.com

Power Query：連続する数字をまとめた文字列を作る

例えば「1,2,3,5,6,8,10,11,12,17」みたいな書き方だと分かりにくいし、文字数も多くなる。「1-3,5-6,8,10-12,17」みたいにまとめたい時、どうすればいいか。いや数式でやってもいいんだけど（やる人いるかな）、あえてクエリでやる場合どうするかを考えてみる。

単純に並んだ数字を１つの文字列にまとめるだけなら、Text.Combineを使えばいい。ただ連続している数字はまとめて表示したい場合は少し面倒になる。

いきなりやると分かりにくいだろうから画像のようなリストで考えてみる。

    数値リスト = {1, 2, 3, 5, 6, 8, 10, 11, 12, 17}

こういうリストがあるとして、変換結果が「1-3」「5-6」「8」「10-12」「17」と並ぶようにすればいい。

まずは数値リストの最小値から最大値までの連番リストを作成する。

    最小値 = List.Min(数値リスト),
    最小値 = List.Min(数値リスト),
    連番 = {最小値..最大値}

これで「1」「2」「3」……「15」「16」「17」と並んだ連番ができる。

つづいて数値リストと連番の相違リストを作る

    相違 = List.Difference(連番, 数値リスト)

結果として、一致しない値「4」「7」「9」「13」「14」「15」「16」が得られる。

これを加工して「1」「5」「8」「10」「17」と「3」「6」「8」「12」「17」の２つのリストを作る。

    から = {最小値}
        & List.Intersect({List.Transform(相違, each _+1), 数値リスト}),
    まで = List.Intersect({List.Transform(相違, each _-1), 数値リスト})
        & {最大値}

これを一つのリストにまとめれば準備完了。

    結合 = List.Zip({から, まで})

あとはこれを文字列に加工して、テーブルに変換すればおしまい。

    リスト編集 = List.Transform(リスト結合, each [
        条件 = _{0}=_{1},
        結合 = Text.From(_{0}) & "-" & Text.From(_{1}),
        結果 = if 条件 then Text.From(_{0}) else 結合
    ][結果]),
    テーブル変換 = Table.FromColumns({リスト編集}, type table [変換=text])

「から」と「まで」の値が同じ時はそのまま表示、そうでなければ「?-?」に。１つにまとめるなら「Text.Comibne(リスト編集, ",")」みたいな形にすればいい。

まとめ
    let
    数値リスト = {1, 2, 3, 5, 6, 8, 10, 11, 12, 17},
    リスト結合 = [
        最小値 = List.Min(数値リスト),
        最大値 = List.Max(数値リスト),
        連番 = {最小値..最大値},
        相違 = List.Difference(連番, 数値リスト),
        から = {最小値}
            & List.Intersect({List.Transform(相違, each _+1), 数値リスト}),
        まで = List.Intersect({List.Transform(相違, each _-1), 数値リスト})
            & {最大値},
        結合 = List.Zip({から, まで})
    ][結合],
    リスト編集 = List.Transform(リスト結合, each [
        条件 = _{0}=_{1},
        結合 = Text.From(_{0}) & "-" & Text.From(_{1}),
        結果 = if 条件 then Text.From(_{0}) else 結合
    ][結果]),
    テーブル変換 = Table.FromColumns({リスト編集}, type table [変換=text])
in
    テーブル変換

こうなる。

Power Query：西暦から和暦を計算する

Excel上なら、TEXT関数で「"gggee年mm月dd日"」とすれば簡単に済む話なんだけど、Power Queryの Date.TeText関数は和暦には対応していない。

画像のような和暦表示の列を作りたい場合、カスタム列の追加で「= Date.ToText([日付], "gggee/MM/dd"」とすると、下のような表記になってしまう。

なんじゃこりゃ。

対応していなら直接計算するしかない訳で、手間をかければ計算はできる。

日付列がある「テーブル1」が読み込まれているものとして、

    和暦表記列の追加 = Table.AddColumn(テーブル1, "和暦表記", each [
        しきい値 = {18681023, 19120730, 19261226, 19890108, 20190501},
        補正値 = List.Transform(しきい値, each Number.RoundDown(_/1E4)-1),
        年月日 = Number.From(Date.ToText([日付], "yyyyMMdd")),
        件数 = List.Count(List.Select(しきい値, each _<=年月日)),
        元号 = {"明治", "大正", "昭和", "平成", "令和"}{件数-1},
        年 = Number.RoundDown(年月日/1E4)-補正値{件数-1},
        和暦表記 = 元号 & Number.ToText(年, if 年=1 then "元年" else "00年")
    ][和暦表記]),
    列の削除 = Table.RemoveColumns(和暦表記列の追加,{"日付"})

こんな形。せっかくなので初年は元年表記にしてみた。

因みに和暦の文字列から西暦を計算するのは簡単。

予め和暦が「gggee/MM/dd」の形式になるようにしておいて、カスタム列の追加で「= Date.From([和暦文字列])」とするだけ。

Power Query：列のピボット解除

マトリクス表（縦横配置の一覧表）を１列１情報の形に組み替えするには、クエリの「列のピボット解除」がとにかく便利。操作も簡単なので、クエリを使うなら絶対に覚えておきたい。

「テーブル1」があるとして（データ範囲を名前定義する形でもいい）、ここからクエリを作成する。

テーブル書式を設定したセル範囲を選択した状態で
［データ］→データの取得と変換の［テーブルまたは範囲から］

これでクエリが作成される。では早速ピボット解除を実行する。

「名前」列以外（「1月」～「6月」列）を選択した状態で
右クリック［列のピボット解除］

はい、これでおしまい。非常に簡単。

この時注目して欲しいのは数式バーで、選択した「1月」～「6月」列ではなく「選択しなかった列」だけが表示される。

つまり、この後で「7月」「8月」……と列を増やしたとしてもクエリを編集する必要がない。これ重要。

最後にこの結果を Excelのシート上に読み込めば終了。

［ホーム］→［閉じて読み込む］→［閉じて次に読み込む］
データのインポートダイアログで［テーブル］を選択し
［既存のワークシート］を選択し、表示させたいセルを指定して［OK］

後先考えずに表を作ってしまった場合にとても役立つので、むしろ Excelが苦手な人にこそ覚えて欲しい機能である。

Power Query：クエリのマージ

そもそも「クエリのマージって何」という人もいるはずなんで書いておくと、２つのクエリ（もしくはステップ）を、それぞれ対象となる列を指定して１つにまとめること。Excel的にいうなら「VLOOKUPで別テーブルのデータをひっぱってくること」だ。これいうとミもフタもないけど。

上の２つのテーブルを使って（いうまでもないけどダミー情報）、下のように情報のマージ（結合）を行う。

そのまんま「クエリのマージ」を実行すればいいんだけど、初めてパワークエリを触る人には、そこまでのハードルが高いみたい。なので順を追って説明していきたい。

今回は「氏名マスタ」と「住所マスタ」があるものとして、これらを１つにまとめてやる。まずは２つのテーブルから「接続のみ」でクエリを作成する。

テーブル書式を設定したセル範囲を選択した状態で
［データ］→データの取得と変換の［テーブルまたは範囲から］

こんな感じで、エディタ画面に読み込まれる。作成されたクエリの名前は、テーブル名（画像だと「テーブル1」）になるので、このままだと後々ややこしくなる。必ず「名前の変更」で適切なクエリ名に変更しておこう。

左ペインのクエリ名を右クリックして［名前の変更］（もしくは F2キー押し）
名前を適切な名称に変更

名前の変更が終わったら、作ったクエリを読み込む。接続のみで読み込みたい時は「閉じて次に読み込む」を選択する必要がある。

［ホーム］→［閉じて読み込む］→［閉じて次に読み込む］

データのインポートダイアログで［接続の作成のみ］を選択して［OK］

これで読み込み完了。

同じように住所マスタも接続のみで読み込む。

これで準備完了。マージできる状態になったので、さっそく作ったクエリを結合してみよう。

［データ］→データの取得と変換の［データの取得］
［クエリの結合］→［マージ］

上段のコンボボックスから「氏名マスタ」を選択
下段のコンボボックスから「住所マスタ」を選択
それぞれマージ対象列の「氏名コード」をクリックで選択
結合の種類はデフォルトの「左外部」のままで［OK］

「Table」と書かれた列が追加されればマージ成功。あとはこれを展開するだけ。

追加された列の見出しの右にあるアイコンをクリック
「郵便番号」「住所」にだけチェックを入れる
［元の列名をプレフィックスとして使用します］のチェックを外す
→［OK］

これで展開も完了。最後にこの結果を Excelのシート上に読み込めば終了。

［ホーム］→［閉じて読み込む］→［閉じて次に読み込む］
データのインポートダイアログで［テーブル］を選択
［既存のワークシート］を選択し、表示させたいセルを指定して［OK］

これでクエリのマージが終了。慣れるまでは少し手間取るかもしれないが、何度か練習すれば１～２分でできるようになると思う。

ちなみに作ったテーブルを選択した状態で、

［データ］→クエリと接続の［プロパティ］
データのレイアウトの［列の幅を調整する］の
チェックを外して［OK］

としておくと、読み込みの際に列幅が自動調整されるのを防ぐことができる。

Power Query：LOOKUP的な検索（近似一致検索）

先週、絶対一致検索にアイテムアクセスが使えることを書いたけど、それなら近似値検索（近似一致検索）がしたい時はどうするのかという話に当然なるかと思う。

数式でやるなら簡単で、画像のような採点表がある場合、同じテーブルに「評価」列を作って、

    =LOOKUP([@点数],採点基準[点数(から)],採点基準[評価])
    もしくは
    =XLOOKUP([@点数],採点基準[点数(まで)],採点基準[評価],"",1)

こんな感じで計算できる。

これをクエリで計算しようと思ったら一工夫が必要になる。「採点基準」「点数表」テーブルから、それぞれクエリを作成しているものとして、

    評価列の追加 = Table.AddColumn(点数表, "評価", each [
        点数 = [点数],
        行の抽出 = Table.FirstN(採点基準, each [#"点数(から)"]<=点数),
        評価 = List.Reverse(行の抽出[評価]){0}
    ][評価], type text),
    列の選択 = Table.SelectColumns(評価列の追加,{"氏名", "評価"})
in
    列の選択

こんな形で計算できる。採点基準テーブルの並びによっては「List.Reverse（リスト反転）」は必要ない。「Table.SelectRows」だとデータをいちいち読み込んで重くなるので、「Table.FirstN」を使うのがお勧め。

個人的にレコードを使うのが好きなだけで、「let～in」を使ってもカスタム関数を使っても特に問題はない。

Power Query：ランダムに並び替える

データをランダムに並べ替えるには「重複しないランダム値」が必要で、Excelのワークシート関数なら「=RAND()」を並べれば簡単に作れる（表示された RAND関数の結果は絶対に重複しない）。なので、Power Queryにも「Number.Random」があるから、同じくカスタム列を追加してやるだけでできるんだろうと高をくくっていた。

失敗例

    乱数列の追加 = Table.AddColumn(テーブル1, "乱数", each Number.Random())

見た目には上手くいっているように見える。ところが読み込んでみると乱数列には同じ値がずらりと並ぶ。

ならばと、今度は読み込む前にソートを掛けてみる。

見た目は同じ値が並んでいるものの、乱数列に昇順のソートをかけると

失敗例

    乱数列の追加 = Table.AddColumn(テーブル1, "乱数", each Number.Random()),
    行のソート = Table.Sort(乱数列の追加, {"乱数", Order.Ascending})

これまた一見するとランダムに並べ替えられているように見える。ところが何度更新をかけても結果が変化しない。

結果、このアプローチでは上手くいかないことが分かった。前振りが長い。

という訳で、一度リストにしてから、同行数の「List.Random」と結合して、テーブルに変換し直してやることにした。

learn.microsoft.com

予め「番号」「氏名」列を持つ、「テーブル1」クエリを作成しているものとする。

    タイプレコード = Type.TableRow(Value.Type(テーブル1)),
    タイプレコード2 = Type.ForRecord(
        Type.RecordFields(タイプレコード)
            & [乱数 = [Type=type number, Optional=false]],
        false
    ),
    リスト化 = Table.ToColumns(テーブル1),
    乱数リスト = List.Random(List.Count(リスト化{0})),
    テーブル変換 = Table.FromColumns(
        リスト化 & {乱数リスト}, type table タイプレコード2
    ),
    行のソート = Table.Sort(テーブル変換, {"乱数", 0}),
    列の削除 = Table.RemoveColumns(行のソート, {"乱数"})

これで一応、ランダムに並べ替えできた。ただタイプレコードを作るところが若干めんどくさい。

なので別案。テーブル1に連番列を追加し、乱数テーブルに連番列をマージしてやる。

    乱数テーブル = [
        乱数リスト = List.Random(Table.RowCount(テーブル1)),
        テーブル変換 = Table.FromColumns(
            {List.Positions(乱数リスト), 乱数リスト},
            type table [連番=number, 乱数=number]
        ),
        行のソート = Table.Sort(テーブル変換, {"乱数", 0})
    ][行のソート],
    連番列の追加 = Table.AddIndexColumn(
        テーブル1, "連番", 0, 1, Int64.Type
    ),
    クエリのマージ = Table.NestedJoin(
        乱数テーブル, {"連番"}, 連番列の追加, {"連番"}, "マージ"
    ),
    マージ列の展開 = Table.ExpandTableColumn(
        クエリのマージ, "マージ", Table.ColumnNames(テーブル1)
    ),
    不要列の削除 = Table.RemoveColumns(
        マージ列の展開,{"連番", "乱数"}
    )

パフォーマンスはあまり変わらないけど、分かりやすくはなった気がする。

最後に別案。

    型リスト = {
        {"番号", Int64.Type}, {"氏名", type text}
    },
    テーブル1 = [
        ブック = Excel.CurrentWorkbook(),
        ソース = ブック{[Name="テーブル1"]}[Content],
        型の変更 = Table.TransformColumnTypes(ソース, 型リスト)
    ][型の変更],
    ランダムリスト = List.Random(Table.RowCount(テーブル1)),
    乱数列の追加 = Table.FromColumns(
        Table.ToColumns(テーブル1) & {ランダムリスト},
        Table.ColumnNames(テーブル1) & {"乱数"}
    ),
    型の変更 = Table.TransformColumnTypes(
        乱数列の追加, 型リスト & {{"乱数", type number}}
    ),
    行のソート = Table.Sort(型の変更, {"乱数", Order.Ascending}),
    乱数列の削除 = Table.RemoveColumns(行のソート, {"乱数"})

マージしない分、こっちのほうが無駄が少ない。List.Sortをしてからテーブルに戻してもいいのかも。