【Power BI】Excel表→リスト形式に変更

⚠️ 注意

本記事に掲載している内容は、個人が学習・検証目的のものです。

業務情報、社外秘情報、特定の企業・組織に関する内容は一切含まれていません。
利用は自己責任でお願いします。

概要

Power BIでExcelマトリクス(セル結合あり)をリスト形式にするM言語です

特定の業務や環境での利用を想定したものではなく、
あくまで考え方・構造の参考用としてまとめています。

対象　##

こんな表を

最終的に以下のように落とし込む

M言語

let
    wb = Excel.Workbook(File.Contents("XXX\Format.xlsx"), null, true),
    raw = wb{[Item="Sheet1", Kind="Sheet"]}[Data],
   
    // 1行目（作業者名が入っている行）
    workerRowTable = Table.FirstN(raw, 1),
    workerRowRecord = workerRowTable{0},
    workerAll = Record.FieldValues(workerRowRecord),

    // 2行目（見出し行）を取得して、列位置を特定する
    headerRowTable = Table.FirstN(Table.Skip(raw, 1), 1),
    headerRowRecord = headerRowTable{0},
    headerAll = Record.FieldValues(headerRowRecord),

    // 「小項目」の列位置（0始まり）を探す
    idxSmall = List.PositionOf(headerAll, "小項目"),

    // 作業者名は「小項目の右隣」から、とみなす
    workerNames = if idxSmall >= 0 then List.Skip(workerAll, idxSmall + 1) else error "ヘッダー行に '小項目' が見つかりません",

    // 2行目をヘッダーとして昇格（1行目は捨てる）
    data2 = Table.Skip(raw, 1),
    promoted = Table.PromoteHeaders(data2, [PromoteAllScalars=true]),

    // セル結合対策
    filled = Table.FillDown(promoted, {"大項目","中項目","小項目"}),

    // 項目列
    itemCols = {"大項目","中項目","小項目"},

    // 作業者列（=それ以外）
    workerCols = List.RemoveItems(Table.ColumnNames(filled), itemCols),

    // アンピボット
    unpivoted = Table.Unpivot(filled, workerCols, "作業者列", "スキル"),

    // 列名順に応じて、1行目の作業者名を付与
    withWorker = Table.AddColumn(
        unpivoted,
        "作業者",
        each 
            let idx = List.PositionOf(workerCols, [作業者列])
            in if idx >= 0 and idx < List.Count(workerNames) then workerNames{idx} else null,
        type text
    ),

    //作業者を上の値で埋める
    filledWorker = Table.FillDown(withWorker, {"作業者"}),

    // 空白除外
    cleaned = Table.SelectRows(filledWorker, each [スキル] <> null and [スキル] <> ""),

    removed = Table.RemoveColumns(cleaned, {"作業者列"}),
    reordered = Table.ReorderColumns(removed, {"大項目","中項目","小項目","作業者","スキル"}),

    // ■ スキル（●）の個数を集計
    grouped = Table.Group(
        reordered,
        {"大項目","中項目","小項目","作業者"},
        {{"スキル", each Table.RowCount(Table.SelectRows(_, each [スキル] = "●")), Int64.Type}}
    )
in
    grouped