Power Query：セル内改行された値を行単位で分割する

表計算の世界で「やったら後で後悔することランキング」をとったら、絶対上位３以内に入ってくるのが「セルに複数の情報を入力する」だと思う。因みに後の２つは「同じ形式の表をシート単位で量産する」と「セル結合で入力を省略する」で間違いない。

都道府県マスタが左のような形で入力されている場合、右の表の形に組み替えしないといけない。

左の表が「テーブル1」として読み込み済みとして、まずはマウス操作中心でやってみる。

［ホーム］→［列の分割］→［区切り記号による分割］

特に指定しなければ、この時点で文字列の中でもっとも左側にある記号が勝手に区切り文字として指定される。今回の場合は強制改行記号の「#(lf)」が入る。「lf」は「Line Feed（改行）」の意味。修正するのは分割する列の数。

［区切り記号］→「カスタム」「#(lf)」
［分割後の列数］を多めの数（ここでは「10」）に変更する
→［OK］

すると改行記号が列方向に分割される。

ここまでくれば後は簡単。先頭列を指定して、その他の列をピボット解除すればいい。

「区分」列を選択して右クリック
［その他の列のピボット解除］

後は「属性」列を削除するだけ。以上。

これでいいといえばいいんだけども、M言語で記述すればもっとすっきりする。

別案
let
    ソース = テーブル1,
    リスト変換 = List.Transform(
        Table.ToRows(ソース),
        each List.Zip({
            {_{0}},
            Text.Split(_{1}, "#(lf)")
        })
    ),
    テーブル再変換 = Table.FromRows(
        List.Combine(リスト変換),
        type table Type.TableRow(Value.Type(ソース))
    ),
    下方向にフィル = Table.FillDown(
        テーブル再変換, {"区分"}
    )
in
    下方向にフィル

こんな感じ。このやり方だと、複数列にも対応させやすい。

こういう形の表だと、最初の方法では面倒なことになるけど、

別案2

let
    ソース = テーブル1,
    リスト変換 = List.Transform(
        Table.ToRows(ソース),
        each List.Zip({
            {_{0}},
            Text.Split(_{1}, "#(lf)"),
            Text.Split(_{2}, "#(lf)")
        })
    ),
    テーブル再変換 = Table.FromRows(
        List.Combine(リスト変換),
        type table Type.TableRow(Value.Type(テーブル1))
    ),
    下方向にフィル = Table.FillDown(
        テーブル再変換, {"料理名"}
    )
in
    下方向にフィル

これで済む。「リスト変換」のリストに列を増やしただけ。

誰もこんな作り方しないって？　いやいや、ありえなような表を作っちゃうのが Excelユーザーだから。

追記。数式でも一応やってみた。

数式版
=LET(
    _div,テーブル1[区分],
    _prf,テーブル1[都道府県名],
    _prf2,TEXTSPLIT(TEXTJOIN(CHAR(10),,_prf),,CHAR(10)),
    fn_hstck,LAMBDA(x,y,XLOOKUP(y&"*",_prf,_div,x,2)),
    HSTACK(SCAN(0,_prf2,fn_hstck),_prf2)
)