Power Query：型リストから型レコードへの変換

タイプリストからタイプレコードへの変換。あるいはタイプレコードからタイプリストへの変換。これってどうやるんだろうと色々試してみた。

    TypeList = {
        {"日付", Date.Type},
        {"テキスト", Text.Type},
        {"数値", Int64.Type}
    }

例えばこんなリストがあるとして、これをレコードに加工したい場合。

    TypeRecord = [
        a = List.Transform(TypeList, each _{0}),
        b = List.Transform(TypeList, each {_{1}, false}),
        c = List.Transform(b, each Record.FromList(_, {"Type", "Optional"})),
        d = Type.ForRecord(Record.FromList(c, a), false)
    ][d]

最初はこんな感じで考えてみた。ただなんかどうもしっくりこない。変換しまくりで不格好な気がする。List.Transoformだらけなのも気に食わない。

あきらめが悪いので色々考えてみた結果、いっそ空のテーブルに一度変換してからレコードを取り出したらどうだろうかと思い付いた。

    TypeRecord = [
        a = #table(List.Transform(TypeList, each _{0}),{}),
        b = Table.TransformColumnTypes(a, TypeList),
        c = Type.TableRow(Value.Type(b))
    ][c]

こっちのほうが幾分かすっきりする。

次は反対に、レコードからリストに変換してみる。

    TypeList = [
        a = Type.RecordFields(TypeRecord),
        b = Record.FieldNames(a),
        c = List.Transform(Record.FieldValues(a), each [Type]),
        d = List.Zip({b, c})
    ][d]

なんとなくまだ納得できてない。

Power Query：VLOOKUP的な検索をする（アイテムアクセス）

Poewr Queryで２つのクエリを紐づけする場合は「クエリのマージ」を使うのが一般的。そっちのほうが処理負担も少ない。

売上データの「ID」を使って、名前マスタの「名前」を結合する（ついでに集計する）場合、

    ソース = 売上データ,
    クエリのマージ = Table.NestedJoin(
        ソース, {"ID"}, 名前マスタ, {"ID"}, "マージ"
    ),
    列の展開 = Table.ExpandTableColumn(
        クエリのマージ, "マージ", {"名前"}
    ),
    値の置換 = Table.ReplaceValue(
        列の展開, null, "【マスタ未登録】",
        Replacer.ReplaceValue,{"名前"}
    ),
    行のグループ化 = Table.Group(
        値の置換, {"ID", "名前"}, 
        {"金額計", each List.Sum([金額] & {0}), type number}
    )

ただ VLOOKUPとか XLOOKUPとか、INDEX/MATCHみたいに直接検索値を指定して値を検索できないのかと思う向きもあるだろう。そういう時はアイテムアクセスを使えばいい。

    ソース = 売上データ,
    名前列の追加 = Table.AddColumn(
        ソース, "名前", each
            try 名前マスタ{[ID = [ID]]}[名前]
            otherwise "【マスタ未登録】",
        type text
    ),
    行のグループ化 = Table.Group(
        名前列の追加, {"ID", "名前"},
        {"金額計", each List.Sum([金額] & {0}), type number}
    )

「こりゃ便利！」と思った人は、データが何万件もあるテーブルでこれをやってみるといい。激重くなること請け合いである。いちいちデータを読み込んでから検索するので当然処理は重くなる。データ量が少なければ特に負担はないだろうから、横着技としては重宝するかもしれない。

因みに「複数条件の時はどうするの？」といわれそうなので一応書いておくと、

    クエリ{[条件1="A", 条件2="B"]}[列名]

のように「,」で連結すれば AND条件で複数条件検索できる。OR条件には対応しない。というか一致するものが複数あるとエラーになるのでご注意。「try/otherwise」との組み合わせが必須かもしれない。

近似値検索がしたい時は別のアプローチが必要になる。

Power Query：置換リストを使って文字列を一括で置き換える

置換リストを使ってデータを置き換えする場合、方法としては色々考えらえるけどリストから変換するのがいいかと思う。

画像のようなテーブルがある場合、ひとまず「氏名テーブル」と「置換テーブル」は読み込み済みとして、［ホーム］→［詳細エディタ］に下記を記述。

List.Generateを使った場合
    置換リスト = Table.ToRows(置換テーブル),
    値の置換 = Table.AddColumn(
        氏名テーブル,
        "変換後",
        each List.Reverse(List.Generate(
            ()=> [i=0, name=[氏名]],
            each [i] <= List.Count(置換リスト),
            each [
                i = [i]+1,
                リスト = 置換リスト{[i]},
                前 = リスト{0},
                後 = リスト{1},
                name = Text.Replace([name], 前, 後)
            ],
            each [name]
        )){0}, type text
    )

List.Accumulateを使った場合
    置換リスト = Table.ToRows(置換テーブル),
    置換後列の追加 = Table.AddColumn(
        氏名テーブル,
        "置換後",
        each List.Accumulate(
            置換リスト,
            [氏名],
            (x, y)=> Text.Replace(x, y{0}, y{1})
        ), type text
    )

速さは特に変わらないっぽい。わざわざ書いたけど、List.Accumulateでやるのが基本かなとは思う。

そしてここまで書いといてなんだけど、Microsoft365なら数式でやるのも一つの手ではある。

Microsoft365は便利だね
=REDUCE(
    [@氏名],
    SEQUENCE(ROWS(置換リスト)),
    LAMBDA(
        x,
        y,
        SUBSTITUTE(
            x,
            INDEX(置換リスト[前],y),
            INDEX(置換リスト[後],y)
        )
    )
)

便利な時代になったもんだ。

Poewr Query：論理値を数値に変換する

パワークエリで論理値（Logical型の「true」「false」）を数値化するのってどうやるんだろうと思って色々試していた。ワークシートの数式だったら「(A1="あ")*(B1="A")」みたいなことが簡単にできるのに。論理演算はできないみたい。

だめなやつ
    計算 = ([Column1]="あ") * ([Column2]="A")

かといって、ifを使うのもなんか嫌だ。

やりたくないやつ
    計算 = (if [Column1]="あ" then 1 else 0)
        * (if [Column2]="A" then 1 else 0)

いちいちこんなことをやるとも思えない。と思って論理関数のヘルプを見ていたら「Logical.From」なんて関数があるではないか。ということはひょっとして……

    計算 = Number.From([Column1]="あ") * Number.From([Column2]="A")

できた。「Number.From」を使えば良かったのか。分かってしまえばなんてことなかった。

そもそもでいうなら、Value.Compareや Value.Equalを使ってもいいのかもしれない。

Power Query：生年月日から年齢を計算する

年齢計算は初日を参入するので（年齢計算ニ関スル法律の第一項）、誕生日の前日には歳が繰り上がる必要がある。なので正確に計算する前に、起算日を生年月日の前日にしてしまったほうがいい。Excelでいうと、DATEDIF関数が初日不算入なので「開始日-1」か「終了日+1」するのと同じである。まぁこの関数でこれやると色々不備があるんだけど……

画像のように「生年月日」列があるとすると、カスタム列の追加で「カスタム列の式」に

= let 起算日 = Date.AddDays(Date.From([生年月日]), -1), 本日 = Date.From(DateTime.LocalNow()), 年 = Date.Year(本日)-Date.Year(起算日), 月日 = (d as date)=>Date.ToText(d, "MMdd"), チェック = 月日(本日)<月日(起算日) in 年 - Number.From(チェック)

と入れてやる。エディタに書くなら整理して、

    年齢列の追加 = Table.AddColumn(ソース, "年齢", each
        let
            起算日 = Date.AddDays(Date.From([生年月日]), -1),
            本日 = Date.From(DateTime.LocalNow()),
            年 = Date.Year(本日)-Date.Year(起算日),
            月日 = (d as date)=>Date.ToText(d, "MMdd"),
            チェック = 月日(本日)<月日(起算日)
        in 年 - Number.From(チェック)
    )

こんな感じ。

DATEDIF関数みたいに「YM」とか「MD」とかを求めたいならカスタム関数を作ったほうがいいんじゃないかと。

nandemo-xl.hatenablog.com

Power Query：シートで分かれたデータに区分名を入れて縦結合する

Excel使う人の多くは「シート」で同じ形式の表を分けてしまう。これやると大半が失敗に繋がるんだけど、どうしてもこれが止められない人が多い。色々理由があるのは分かるんだけど、あえて「それは間違っている」と断言しておく。

とはいえやってしまったものは仕方がない。なんとか一つにまとめてみよう。

これを、

こうしたい時。

画像では１シートに各地方のテーブルが並んでいるけど、テーブル書式が設定してあるなら地方別にシートが分かれていてもやることは同じ。それぞれのテーブルは名前の管理で、

画像のように「分類したい名称」でテーブル名が設定されているものとする。

とりまとめるにあたっては、区分するものの並びを指定する情報が必要。何もしないと音読み順になってしまうので、「北海道・東北」よりも「関東」のほうが先にきてしまう。このくらいのリストなら手入力で作っても問題ないけど、今回は順番テーブルを用意してからやることにする。

今回の場合、必要なのは「区分」列のみなので、

    区分テーブル = [
        ブック = Excel.CurrentWorkbook(),
        ソース = ブック{[Name="順番"]}[Content],
        列の選択 = Table.SelectColumns(ソース, {"区分"}),
        型の変更 = Table.TransformColumnTypes(
            列の選択, {"区分", type text}
        )
    ][型の変更],

「列の選択」でいらない列を省いてしまった。住所から都道府県名を抜き出して、そこから地方区分を入れるとなると隣りのリストが必要になるんだろうけど、今回はパス。

ではいよいよ、テーブル情報を読み込んで縦に結合してしまおう。

    ファイルパス = "C:\……\…….xlsx",
    ソース = Excel.Workbook(File.Contents(ファイルパス), null, true),
    行の抽出 = Table.SelectRows(ソース, each ([Kind] = "Table")),
    クエリのマージ = Table.NestedJoin(
        区分テーブル, {"区分"}, 行の抽出, {"Name"}, "テーブル"
    ),
    テーブル列の編集 = Table.TransformColumns(
        クエリのマージ, {"テーブル", each [Data]{0}}
    ),
    見出し = Table.ColumnNames(テーブル列の編集{0}[テーブル]),
    テーブル列の展開 = Table.ExpandTableColumn(
        テーブル列の編集, "テーブル", 見出し
    ),
    型リスト = List.Transform(見出し, each {_, type text}),
    型の変更 = Table.TransformColumnTypes(テーブル列の展開, 型リスト)

作った「区分テーブル」に、該当する区分名のテーブルをマージしてしまう。このやり方だと、元のブックに地方名以外のテーブルがあっても関係なく引き当てできる。

「見出し」と「型のリスト」は読み込んだテーブルから作成したけど、細かい指定がある場合は自分でやってね。

Power Query：縦方向のデータを横並びで出力する

テーブルに縦方向に入力したデータをグループ分けして横に並べたい時。

Excelを使っている人の多くは、この逆パターンで悩むことが多いんだけど、そっちは Power Queryだと「列のピボット解除」で一発解決するので、敢えて逆パターンを取り上げる。

横並びの列が固定の場合は、グループ化の際に Text.Combineで結合しておいて「列の分割」で分ける方法もあるにはある。ただこれだと列数が増えた時に対応が利かないのと、データがテキストデータでないとデータ型が変わってしまって面倒なことになる。

やはり基本は「行のグループ化」→「グループ列にインデックス列を追加」→「展開して列のピボット」かと思う。なるべくマウス操作だけでできる方法を書いてみる。

ひとまずテーブルを読み込んでクエリを作成し、Power Query エディタを開いたところからで。

品番列の見出しを右クリック［グループ化］

新しい列名：グループ
操作：すべての行
→［OK］

作成されたグループ列内のテーブルにインデックス列を追加する。ここだけは手作業が必要。

数式バーを広げて「each _」となっているところを
「each Table.AddIndexColumn(_, "連番", 1, 1)」に変更
「type table [……]」の中に「, 連番=number」を追加

これをやっておくと新規列を追加して、後で削除する手間が減るのでお勧め。

作成されたグループ列の見出し右にあるボタンをクリック

「店舗名」「連番」にだけチェックを入れ
［元の列名をプレフィックスとして使用します］のチェックを外す
→［OK］

先の操作で「, 連番=number」を追加しておかないと、展開する時、見出しリストの中に「連番」が表示されないので注意。

ここまででこの状態。この後、連番列は見出しとして使うので「店舗1」「店舗2」……となるように加工する。

連番列を選択した状態で
［編集］→［書式］→［プレフィックスの追加］
値に「店舗」と入力して［OK］

前に文字列を追加した結果。後ろに付けたい場合は「サフィックスの追加」で。ここまできたら後は列のピボットを実行するだけ。

値列：店舗名
値の集計関数：集計しない
→［OK］

これで完成。後は「閉じて次へ読み込む」で好きなところに出力すればＯＫ。

尚、詳細エディタで直接記述するならこんな感じ。

列数固定の場合
let
    列数 = 3,
    ブック = Excel.CurrentWorkbook(),
    ソース = ブック{[Name="テーブル1"]}[Content],
    型の変更 = Table.TransformColumnTypes(
        ソース, {{"品番", type text}, {"店舗名", type text}}
    ),
    行のグループ化 = Table.Group(
        型の変更, {"品番"},
        {"グループ", each Text.Combine([店舗名], ","), type text}
    ),
    列の分割 = Table.SplitColumn(
        行のグループ化, "グループ",
        Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
        List.Transform({1..列数}, each Number.ToText(_, "店舗0"))
    )
in
    列の分割

列数不定の場合
let
    ブック = Excel.CurrentWorkbook(),
    ソース = ブック{[Name="テーブル1"]}[Content],
    型の変更 = Table.TransformColumnTypes(
        ソース, {{"品番", type text}, {"店舗名", type text}}
    ),
    行のグループ化 = Table.Group(
        型の変更, {"品番"}, {"グループ",
        each Table.AddIndexColumn(_, "連番", 1, 1),
        type table [連番=number, 店舗名=text]}
    ),
    グループ列の展開 = Table.ExpandTableColumn(
        行のグループ化, "グループ", {"連番", "店舗名"}
    ),
    連番列の編集 = Table.TransformColumns(
        グループ列の展開,
        {"連番", each Number.ToText(_, "店舗0"), type text}
    ),
    列のピボット = Table.Pivot(
        連番列の編集,
        List.Distinct(連番列の編集[連番]), "連番", "店舗名"
    )
in
    列のピボット