Excel Power Queryで1レコードが複数行、複数列に分かれている表の整形方法
Yahoo!知恵袋に1レコードが複数行、複数列に分かれている表をデータベース形式に変換する質問がありました。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q12311497522
関数での回答が付いており、このケースでは関数の方が簡単です。
ファイルやシートが増えるケースの場合にも[すべて更新]だけで集計できるように、Power Queryでの整形方法をメモしました。
1. 元の表と整形後の表
| 1 | a:あ | b:い | c:う | d:え | h:く | |
| e:お | f:か | g:き | ||||
| 2 | a:け | b:こ | c:さ | d:し | h:た | |
| e:す | f:せ | g:そ | ||||
特徴
1) 1レコード2段で構成されている。
2) 項目名と内容が":"で区切られて各フィールドに入っている。(見出し行に項目名がある場合は、最後の参考の#2を見てください。)
これを1レコード1行に整形します。
| No. | a | b | c | d | e | f | g | h |
|---|---|---|---|---|---|---|---|---|
| 1 | あ | い | う | え | お | か | き | く |
| 2 | け | こ | さ | し | す | せ | そ | た |
2. Power Queryの詳細エディタのコードと作成の方法
Power Query Mのコードの行頭//で始まるコメントの（）内が操作方法です。Power Query Mのコードは自動的に生成されるので入力不要です。殆どクリック操作で作成できます。
1) Sheet1クエリ
データを取り込み、2段のインデックスを付ける。
let
// 元データの取込み（Excelの[データ]-[データの取得]-[ファイルから]-[Excelブックから]でファイルを選択し、[インポート]-[Sheet1]-[データの変換]）
ソース = Excel.Workbook(File.Contents("C:\Users\Public\Documents\250227xl2row.xlsx"), null, true),
Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
// 左端のNo.列の2段目のnullを埋める（Power Queryエディタの[変換]-[フィルv]-[下へ]）
下方向へコピー済み = Table.FillDown(Sheet1_Sheet,{"Column1"})
// 段の連番を付ける為"Column1"のグループ化を行いその他のすべての情報をTEMP列に格納（"Column1"を選択し[ホーム]-[グループ化]で新しい列名に"TEMP"を入力、操作に"全ての行"を選択し、[OK]）
グループ化された行 = Table.Group(下方向へコピー済み, {"Column1"}, {{"TEMP", each _, type table [Column1=number, Column2=text, Column3=nullable text, Column4=nullable text, Column5=nullable text, Column6=text, Column7=nullable text]}}),
// TEMPに格納した各段に連番を追加（[列の追加]-[カスタム列]で式に下記の赤字の式を入力し[OK]）
追加されたカスタム = Table.AddColumn(グループ化された行, "カスタム", each Table.AddIndexColumn([TEMP], "インデックス", 1)),
// 連番を追加したカスタム列の情報を展開する（"カスタム"列の右の矢印アイコンをクリック、さらに読み込むをクリック、"Column1"と元の列名をプレフィックスとして使用しますのチェックを外し[OK]）
#"展開された カスタム" = Table.ExpandTableColumn(追加されたカスタム, "カスタム", {"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "インデックス"}, {"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "インデックス"}),
// 作業列を削除（"TEMP"を右クリックし[列の削除]）
削除された列 = Table.RemoveColumns(#"展開された カスタム",{"TEMP"})
in
削除された列
[*1]グループ化された行~追加されたカスタムの2ステップは、下記の1ステップに置き換えることもできます。
グループ化された行 = Table.Group(
下方向へコピー済み ,
"Column1",
{"カスタム", each Table.AddIndexColumn(_, "インデックス", 1)} // _が上記クリック操作のコードのTEMPに相当
),
2) Line2dataクエリ
2段目のデータを抽出する。
let
//（"Sheet1"クエリを右クリックし[参照]、プロパティの名前を"Line2data"に変更）
ソース = Sheet1,
// 2段目のみに絞り込み（"インデックス"列の[▼]-[数値フィルタ]-[指定の値に等しい]で値の入力または選択欄に"2"を入力し[OK]）
フィルターされた行 = Table.SelectRows(ソース, each [インデックス] = 2),
// 2段目に値が無い列を削除（"Column3","Column5","Column7","インデックス"を選択し、右クリックで[列の削除]）
削除された列 = Table.RemoveColumns(フィルターされた行,{"Column3", "Column5", "Column7", "インデックス"}),
// 2段目の内容を抽出（"Column2"~"Cplumn6"を選択し、[変換]-[抽出]-[区切り記号後のテキスト]で区切り記号に":"を入力し[OK]）
区切り記号の後の抽出されたテキスト = Table.TransformColumns(削除された列, {{"Column2", each Text.AfterDelimiter(_, ":"), type text}, {"Column4", each Text.AfterDelimiter(_, ":"), type text}, {"Column6", each Text.AfterDelimiter(_, ":"), type text}})
in
区切り記号の後の抽出されたテキスト
3) Line1dataクエリ
1段目のデータを抽出する。
let
//（"Sheet1"クエリを右クリックし[参照]、プロパティの名前を"Line1data"に変更）
ソース = Sheet1,
// 1段目に絞り込み（"インデックス"列の[▼]-[数値フィルタ]-[指定の値に等しい]で値の入力または選択欄に"1"を入力し[OK]）
フィルターされた行 = Table.SelectRows(ソース, each [インデックス] = 1),
// 1段目の値が無い列を削除（"Column4","インデックス"を選択し、右クリックで[列の削除]）
削除された列 = Table.RemoveColumns(フィルターされた行,{"Column4", "インデックス"}),
// 1段目の内容を抽出（"Column2"~"Cplumn7"を選択し、[変換]-[抽出]-[区切り記号後のテキスト]で区切り記号に":"を入力し[OK]）
区切り記号の後の抽出されたテキスト = Table.TransformColumns(削除された列, {{"Column2", each Text.AfterDelimiter(_, ":"), type text}, {"Column3", each Text.AfterDelimiter(_, ":"), type text}, {"Column5", each Text.AfterDelimiter(_, ":"), type text}, {"Column6", each Text.AfterDelimiter(_, ":"), type text}, {"Column7", each Text.AfterDelimiter(_, ":"), type text}})
in
区切り記号の後の抽出されたテキスト
4) Line2titleクエリ
2段目の項目名を抽出し、2段目のデータを追加する。
let
//（"Sheet1"クエリを右クリックし[参照]、プロパティの名前を"Line2title"に変更）
ソース = Sheet1,
// 1レコード目を抽出（"Column1"列の[▼]-[数値フィルタ]-[指定の値に等しい]で値の入力または選択欄に"1"を入力し[OK]）
フィルターされた行 = Table.SelectRows(ソース, each [Column1] = 1),
// 2段目を抽出（"インデックス"列の[▼]-[数値フィルタ]-[指定の値に等しい]で値の入力または選択欄に"2"を入力し[OK]）
フィルターされた行1 = Table.SelectRows(フィルターされた行, each [インデックス] = 2),
// 2段目の値が無い列を削除（"Column3","Column5","Column7","インデックス"を選択し、右クリックで[列の削除]）
削除された列 = Table.RemoveColumns(フィルターされた行1,{"Column3", "Column5", "Column7", "インデックス"}),
// 2段目の項目名を抽出（"Column2"~"Cplumn6"を選択し、[変換]-[抽出]-[区切り記号前のテキスト]で区切り記号に":"を入力し[OK]）
区切り記号の前の抽出されたテキスト = Table.TransformColumns(削除された列, {{"Column2", each Text.BeforeDelimiter(_, ":"), type text}, {"Column4", each Text.BeforeDelimiter(_, ":"), type text}, {"Column6", each Text.BeforeDelimiter(_, ":"), type text}}),
// 2段目のデータを追加（[ホーム]-[クエリの追加]でLine2Dataを選択し[OK]）
追加されたクエリ = Table.Combine({区切り記号の前の抽出されたテキスト, Line2data}),
// 2段目の項目名をヘッダに昇格（[変換]-[1行目をヘッダーとして使用]）
昇格されたヘッダー数 = Table.PromoteHeaders(追加されたクエリ, [PromoteAllScalars=true]),
// 型の変更（自動生成されるので必要に応じて修正）
変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"1", Int64.Type}, {"e", type text}, {"f", type text}, {"g", type text}}),
// 左端の列名を設定（左端の項目名をWクリックして"No."に変更）
#"名前が変更された列 " = Table.RenameColumns(変更された型,{{"1", "No."}})
in
#"名前が変更された列 "
5) Line1titleクエリ
1段目の項目名を抽出し、1段目のデータを追加し、2段目のデータをマージする。これが整形後の表になる。
let
//（"Sheet1"クエリを右クリックし[参照]、プロパティの名前を"Line1title"に変更）
ソース = Sheet1,
// 1レコード目を抽出（"Column1"列の[▼]-[数値フィルタ]-[指定の値に等しい]で値の入力または選択欄に"1"を入力し[OK]）
フィルターされた行 = Table.SelectRows(ソース, each [Column1] = 1),
// 1段目を抽出（"インデックス"列の[▼]-[数値フィルタ]-[指定の値に等しい]で値の入力または選択欄に"1"を入力し[OK]）
フィルターされた行1 = Table.SelectRows(フィルターされた行, each [インデックス] = 1),
// 1段目の値が無い列を削除（"Column4","インデックス"を選択し、右クリックで[列の削除]）
削除された列 = Table.RemoveColumns(フィルターされた行1,{"Column4", "インデックス"}),
// 1段目の項目名を抽出（"Column2"~"Cplumn7"を選択し、[変換]-[抽出]-[区切り記号前のテキスト]で区切り記号に":"を入力し[OK]）
区切り記号の前の抽出されたテキスト = Table.TransformColumns(削除された列, {{"Column2", each Text.BeforeDelimiter(_, ":"), type text}, {"Column3", each Text.BeforeDelimiter(_, ":"), type text}, {"Column5", each Text.BeforeDelimiter(_, ":"), type text}, {"Column6", each Text.BeforeDelimiter(_, ":"), type text}, {"Column7", each Text.BeforeDelimiter(_, ":"), type text}}),
// 1段目のデータを追加（[ホーム]-[クエリの追加]でLine1Dataを選択し[OK]）
追加されたクエリ = Table.Combine({区切り記号の前の抽出されたテキスト, Line1data}),
// 項目名をヘッダに昇格（[変換]-[1行目をヘッダーとして使用]）
昇格されたヘッダー数 = Table.PromoteHeaders(追加されたクエリ, [PromoteAllScalars=true]),
// 型の変更（自動生成されるので必要に応じて修正）
変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"1", Int64.Type}, {"a", type text}, {"b", type text}, {"c", type text}, {"d", type text}, {"h", type text}}),
// 左端の列名を設定（左端の項目名をWクリックして"No."に変更）
#"名前が変更された列 " = Table.RenameColumns(変更された型,{{"1", "No."}}),
// 2段目のデータをマージ（[ホーム]-[クエリのマージv]-[クエリのマージ]でLine2titleを選択し、両方のNo.を選択して[OK]）
マージされたクエリ数 = Table.NestedJoin(#"名前が変更された列 ", {"No."}, Line2title, {"No."}, "Line2title", JoinKind.LeftOuter),
#"展開された Line2title" = Table.ExpandTableColumn(マージされたクエリ数, "Line2title", {"e", "f", "g"}, {"e", "f", "g"})
//（"h"列を右クリック、[移動]-[末尾に移動]）
並べ替えられた列 = Table.ReorderColumns(#"展開された Line2title",{"No.", "a", "b", "c", "d", "e", "f", "g", "h"})
in
並べ替えられた列
3. Excelへの読み込み
Power Queryエディタの[ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"接続のみ"を選択し、[OK]
Excelの[データ]-[クエリと接続]で右のLine1titleを右クリック、[読み込み先]でテーブルと新規ワークシートを選択し、[OK]
4. サンプルファイル
C:\Users\Public\Documentsに格納して開いてください。
■ 参考（外部リンク）
【Power Query】グループごとに連番をつける方法
https://blog.truestar.co.jp/microsoft/power-query/20210707/45919/
#2 表の変換にマクロなんかいらない【エクセル パワークエリ PowerQuery】1レコード複数行・複数列の表をリスト形式に変換する方法
https://www.youtube.com/watch?v=VleTkjBmaCA
【Excel パワークエリ】これを見ても、まだコピペでいいなんて言うヤツ……いねぇよなぁ
https://www.youtube.com/watch?v=UapvFSxv2mE
Power Queryで表をリスト形式に整形する方法（まとめ）
https://bigarrow.tea-nifty.com/blog/2025/11/post-4925ca.html
1レコードが1シート複数行複数列から成る複数シートを1つのデータベース形式にする
https://bigarrow.tea-nifty.com/blog/2025/09/post-4beb66.html
最近のコメント