数式/関数/Power Query：グループごとに分けて並べる

Microsoft365に追加された関数を使うと、今まで数式では手間がかかっていたことが簡単にできるようになった。

左のテーブルから右のようにグループで分けて名前を表示したい場合、UNIQUE関数で見出しを作って、E2に FILTER関数を入れて右にコピーして……とやりたいところだが、それだと見出しが横に伸びた時に下の値はコピーしない限り勝手には増えてくれない。

=LET(
    x,テーブル1[住所],
    y,テーブル1[名前],
    hd,UNIQUE(x),
    map,MAP(hd,LAMBDA(a,TEXTJOIN(",",,FILTER(x,y=a)))),
    bd,IFERROR(TEXTSPLIT(TEXTJOIN(";",FALSE,map),",",";"),""),
    TRANSPOSE(HSTACK(hd,bd))
)

こうやっておけば、見出しが伸びた分に合わせて数式の結果も伸びてくれる。MAP関数のところは BYROW関数にしてもいい。

因みに、Power Queryでやるなら下記の通り。予め「テーブル1」は読み込んでいるものとして、

let
    ソース = テーブル1,
    行のグループ化 = Table.Group(テーブル1, {"住所"}, {"グループ", each Table.AddIndexColumn(_, "番号", 1, 1),type table [名前=text]}),
    グループ列の展開 = Table.ExpandTableColumn(行のグループ化, "グループ", {"番号", "名前"}),
    列のピボット = Table.Pivot(グループ列の展開, List.Distinct(グループ列の展開[住所]), "住所", "名前"),
    番号列の削除 = Table.RemoveColumns(列のピボット, {"番号"})
in
    番号列の削除

テーブルとして結果を返したいなら、クエリを使ったほうがいいかな。