Power Queryで神エクセル分解2

その２としてもう少し複雑なものを変形していく形の紹介

対象

契約が複数回あって分解したい列が複数回連続している場合

列1	列2	列3	列4	列5	列6
案件名	A1			着手	2019/12/12 0:00:00
取引先	b1
担当者	ee1			見積金	8
受注		設計		着工
2020/01/01 0:00:00		2020/01/02 0:00:00		2020/01/03 0:00:00
納入		請求		入金
2020/01/04 0:00:00		2020/01/05 0:00:00		2020/01/06 0:00:00
受注		設計		着工
2020/01/01 0:00:00		2020/01/02 0:00:00		2020/01/03 0:00:00
納入		請求		入金
2020/01/04 0:00:00		2020/01/05 0:00:00		2020/01/06 0:00:00
案件名	A2			着手	2020/01/12 0:00:00
取引先	b2
担当者	ee2			見積金	8
受注		設計		着工
2020/02/01 0:00:00		2020/02/06 0:00:00		2020/02/11 0:00:00
納入		請求		入金
2020/02/16 0:00:00		2020/02/21 0:00:00		2020/02/26 0:00:00

これを適当に変形して
テーブルの名前は「テーブル1」にしてお使いください

解法1(クエリを分解させずに処理する方法)

PowerQuery

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"列1", type any}, {"列2", type text}, {"列3", type any}, {"列4", type any}, {"列5", type any}, {"列6", type any}}),
    追加されたインデックス = Table.AddIndexColumn(変更された型, "インデックス", 0, 1),
    ピボット解除された他の列 = Table.UnpivotOtherColumns(追加されたインデックス, {"インデックス"}, "属性", "値"),
    追加された条件列1 = Table.AddColumn(ピボット解除された他の列, "カスタム.1", each if [値] = "案件名" then "x" else if [値] = "受注" then [インデックス] else null),
    下方向へコピー済み = Table.FillDown(追加された条件列1,{"カスタム.1"}),
    // x→nullへ変換
    置き換えられた値 = Table.ReplaceValue(下方向へコピー済み,"x",null,Replacer.ReplaceValue,{"カスタム.1"}),
    // 上フィルで案件名の所まで広げる
    上方向へコピー済み = Table.FillUp(置き換えられた値,{"カスタム.1"}),
    挿入された引き算 = Table.AddColumn(上方向へコピー済み, "減算", each [インデックス] - [カスタム.1], type number),
    列に加算済み = Table.TransformColumns(挿入された引き算, {{"減算", each _ + 3, type number}}),
    結合された列 = Table.CombineColumns(Table.TransformColumnTypes(列に加算済み, {{"減算", type text}}, "ja-JP"),{"減算", "属性"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"結合済み"),
    削除された列 = Table.RemoveColumns(結合された列,{"インデックス"}),
    ピボットされた列 = Table.Pivot(削除された列, List.Distinct(削除された列[結合済み]), "結合済み", "値")
in
    ピボットされた列

こんな感じで処理すると

こうなるのであとは並べ替え・ヘッダ付け下フィルで完成

途中の条件列の処理は

こんな感じで2項目のスイッチ的使い方を使用
適当にxを入れているのは必要としないところまで下フィルが漏れるのを防止しています
下フィルまで済ます床のようになります

このあとx→nullに変換後、上にフィルをして行番を調整しています

解法2(②分割で処理して、マージする方法)

前半

PowerQuery

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"列1", type any}, {"列2", type text}, {"列3", type any}, {"列4", type any}, {"列5", type any}, {"列6", type any}}),
    追加されたインデックス = Table.AddIndexColumn(変更された型, "インデックス", 0, 1),
    ピボット解除された他の列 = Table.UnpivotOtherColumns(追加されたインデックス, {"インデックス"}, "属性", "値"),
    追加された条件列 = Table.AddColumn(ピボット解除された他の列, "カスタム", each if [値] = "案件名" then "x" else if [値] = "受注" then [インデックス] else null),
    下方向へコピー済み = Table.FillDown(追加された条件列,{"カスタム"}),
    追加された条件列1 = Table.AddColumn(下方向へコピー済み, "カスタム.1", each if [値] = "案件名" then [インデックス] else null),
    下方向へコピー済み1 = Table.FillDown(追加された条件列1,{"カスタム.1"}),
    // ここから別操作
    フィルターされた行 = Table.SelectRows(下方向へコピー済み1, each ([カスタム] = "x")),
    挿入された引き算 = Table.AddColumn(フィルターされた行, "減算", each [インデックス] - [カスタム.1], type number),
    結合された列 = Table.CombineColumns(Table.TransformColumnTypes(挿入された引き算, {{"減算", type text}}, "ja-JP"),{"減算", "属性"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"結合済み"),
    削除された列 = Table.RemoveColumns(結合された列,{"インデックス", "カスタム"}),
    ピボットされた列 = Table.Pivot(削除された列, List.Distinct(削除された列[結合済み]), "結合済み", "値")
in
    ピボットされた列

後半

PowerQuery

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"列1", type any}, {"列2", type text}, {"列3", type any}, {"列4", type any}, {"列5", type any}, {"列6", type any}}),
    追加されたインデックス = Table.AddIndexColumn(変更された型, "インデックス", 0, 1),
    ピボット解除された他の列 = Table.UnpivotOtherColumns(追加されたインデックス, {"インデックス"}, "属性", "値"),
    追加された条件列 = Table.AddColumn(ピボット解除された他の列, "カスタム", each if [値] = "案件名" then "x" else if [値] = "受注" then [インデックス] else null),
    下方向へコピー済み = Table.FillDown(追加された条件列,{"カスタム"}),
    追加された条件列1 = Table.AddColumn(下方向へコピー済み, "カスタム.1", each if [値] = "案件名" then [インデックス] else null),
    下方向へコピー済み1 = Table.FillDown(追加された条件列1,{"カスタム.1"}),
    // ここから別操作
    フィルターされた行 = Table.SelectRows(下方向へコピー済み1, each ([カスタム] <> "x")),
    挿入された引き算 = Table.AddColumn(フィルターされた行, "減算", each [インデックス] - [カスタム], type number),
    結合された列 = Table.CombineColumns(Table.TransformColumnTypes(挿入された引き算, {{"減算", type text}}, "ja-JP"),{"減算", "属性"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"結合済み"),
    削除された列 = Table.RemoveColumns(結合された列,{"インデックス"}),
    ピボットされた列 = Table.Pivot(削除された列, List.Distinct(削除された列[結合済み]), "結合済み", "値")
in
    ピボットされた列

これらを実行すると

このようになるので
このあと「カスタム.1」でマージをすると

このように目的のデータが取れます

こちらは前半・後半で分けて処理して
マージでくっつてるという方法
「ここから別操作」と書いてあるところで別操作となり
実際の使用方法ではそこで「参照」などを使って分岐させて使います