Power Queryで神エクセル分解１

よくPower Query案件として名の上がる
神excel類をデータベース型に分解する方法について書いていきたい
検索等で調べやすいので日本語のUIで出てくる日本語名称をそのままを使用しております

対象

話題になったこの表への対応法について解説します

列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
案件名	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(データ数の規則性を使う)

前提として全枠にデータが入っている場合

このように22マスにデータが入ってループしていることを使って処理します

テーブル使用版

PowerQuery

let
    Source = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    ピボット解除された列 = Table.UnpivotOtherColumns(Source, {}, "属性", "値"),
    // 行列の番号の元とするためにインデックス追加　
    追加されたインデックス = Table.AddIndexColumn(ピボット解除された列, "インデックス", 0, 1),
    // mod(インデックス,22)の数値な新列を挿入
    挿入された剰余 = Table.AddColumn(追加されたインデックス, "剰余", each Number.Mod([インデックス], 22), type number),
    // インデックスを22で割った整数部分へ変換
    整数除算済みの列 = Table.TransformColumns(挿入された剰余, {{"インデックス", each Number.IntegerDivide(_, 22), Int64.Type}}),
    // 不要な列を削除
    削除された列 = Table.RemoveColumns(整数除算済みの列,{"属性"}),
    ピボットされた列 = Table.Pivot(Table.TransformColumnTypes(削除された列, {{"剰余", type text}}, "ja-JP"), List.Distinct(Table.TransformColumnTypes(削除された列, {{"剰余", type text}}, "ja-JP")[剰余]), "剰余", "値")
in
    ピボットされた列

単独クエリ版

空クエリ⇒詳細エディタ⇒全文コピペで入れ替え
で使えるデータ梱包版も作ってみました

PowerQuery

let
    X0 = "1VTLUsQgEPwVi/NW7TAMj+S23yEeNoGctrzlZPnvMq4bgyQo6kGrApUJk6G7h+b+SfhZo7RS9OnNOHJ+pjiZ9JUgiMPbMqblk1xCJfrH+XK5hZSH+rWWtQSpItKw/GbSAoLs/FEijzvogR/xfMhxaBUYwSR1miW5DMfQgGMdmmv4cS+DI+8yaeVnB6CzvWJsI+06NyTqZwp+7mSQGXW3xdMmBGPMGK7LqmvZMwae4T2vorkOUYsqawQEfwTJY2lCBcEqH4v8DSSrfFXk7/TBBgjcbaM/02IcWDPCuhZcqWxCVQtq1EI3amG+qkXdh/g7PrzB+rYPG3D83IdtpP+XD7HRh7h5lvbPHl+2Zf2/6UPGWnKraoElt6oWuO/Dhxc=",
    //テーブル変換部
    X1 = Binary.FromText(X0),
    X2 =Binary.Decompress(X1,Compression.Deflate),
    X3 =Table.FromRecords(Json.Document(X2,932)),
    ピボット解除された列 = Table.UnpivotOtherColumns(X3, {}, "属性", "値"),
    // 行列の番号の元とするためにインデックス追加　
    追加されたインデックス = Table.AddIndexColumn(ピボット解除された列, "インデックス", 0, 1),
    // mod(インデックス,22)の数値な新列を挿入
    挿入された剰余 = Table.AddColumn(追加されたインデックス, "剰余", each Number.Mod([インデックス], 22), type number),
    // インデックスを22で割った整数部分へ変換
    整数除算済みの列 = Table.TransformColumns(挿入された剰余, {{"インデックス", each Number.IntegerDivide(_, 22), Int64.Type}}),
    // 不要な列を削除
    削除された列 = Table.RemoveColumns(整数除算済みの列,{"属性"}),
    ピボットされた列 = Table.Pivot(Table.TransformColumnTypes(削除された列, {{"剰余", type text}}, "ja-JP"), List.Distinct(Table.TransformColumnTypes(削除された列, {{"剰余", type text}}, "ja-JP")[剰余]), "剰余", "値")
in
    ピボットされた列

このような感じで処理できる

不要な行を消してヘッダを調整すれば完成

利点はシンプルですが、
難点は枠に入力がない場合、ピボット解除時に「null」データが入ったマスは削除され
ループが破綻してずれます

回避方法としては消える可能性がある列にnullをほかのスペース等で置換し
削除されないようにする方法等があります

X0~X3のステップは下の狸さんのjson関連のコードを使って作ってます

解法2(座標と、表の周期性をつかう)

処理の感じとしてはこんな感じに、個別に行番と列番を振ってやって
それらを使い並べ替えていきます

テーブル使用版

PowerQuery

let
    Source = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    // 行列の番号うちの元とするためにインデックス追加
    追加されたインデックス = Table.AddIndexColumn(Source, "インデックス", 0, 1),
    // インデックスを選択し、「その他の列をピボット解除」
    ピボット解除された他の列 = Table.UnpivotOtherColumns(追加されたインデックス, {"インデックス"}, "属性", "値"),
    // 「案件名」を区切り判定として、条件列でインデックス値を抜き出す
    追加された条件列 = Table.AddColumn(ピボット解除された他の列, "行ヘッダ", each if [値] = "案件名" then [インデックス] else null),
    // 抜き出した値を下フィル
    下方向へコピー済み1 = Table.FillDown(追加された条件列,{"行ヘッダ"}),
    // インデックス-行ヘッダで各繰り返しの行数を出す
    挿入された引き算1 = Table.AddColumn(下方向へコピー済み1, "減算", each [インデックス] - [行ヘッダ], type number),
    // 個別の行番号の”減算”と列番の”属性”をくっつけて、ピボット火曜の列ヘッダを作成
    結合された列 = Table.CombineColumns(Table.TransformColumnTypes(挿入された引き算1, {{"減算", type text}}, "ja-JP"),{"減算", "属性"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"結合済み"),
    // ピボット化で邪魔なインデックスを削除
    削除された列 = Table.RemoveColumns(結合された列,{"インデックス"}),
    // ”結合済み”を選択し、値列は”値”を選択
    ピボットされた列 = Table.Pivot(削除された列, List.Distinct(削除された列[結合済み]), "結合済み", "値")
in
    ピボットされた列

単独クエリ版

空クエリ⇒詳細エディタ⇒全文コピペで入れ替え
で使えるデータ梱包版も作ってみました

PowerQuery

let
    X0 = "1VTLUsQgEPwVi/NW7TAMj+S23yEeNoGctrzlZPnvMq4bgyQo6kGrApUJk6G7h+b+SfhZo7RS9OnNOHJ+pjiZ9JUgiMPbMqblk1xCJfrH+XK5hZSH+rWWtQSpItKw/GbSAoLs/FEijzvogR/xfMhxaBUYwSR1miW5DMfQgGMdmmv4cS+DI+8yaeVnB6CzvWJsI+06NyTqZwp+7mSQGXW3xdMmBGPMGK7LqmvZMwae4T2vorkOUYsqawQEfwTJY2lCBcEqH4v8DSSrfFXk7/TBBgjcbaM/02IcWDPCuhZcqWxCVQtq1EI3amG+qkXdh/g7PrzB+rYPG3D83IdtpP+XD7HRh7h5lvbPHl+2Zf2/6UPGWnKraoElt6oWuO/Dhxc=",
    //テーブル変換部
    X1 = Binary.FromText(X0),
    X2 =Binary.Decompress(X1,Compression.Deflate),
    X3 =Table.FromRecords(Json.Document(X2,932)),
    // 行列の番号うちの元とするためにインデックス追加
    追加されたインデックス = Table.AddIndexColumn(X3, "インデックス", 0, 1),
    // インデックスを選択し、「その他の列をピボット解除」
    ピボット解除された他の列 = Table.UnpivotOtherColumns(追加されたインデックス, {"インデックス"}, "属性", "値"),
    // 「案件名」を区切り判定として、条件列でインデックス値を抜き出す
    追加された条件列 = Table.AddColumn(ピボット解除された他の列, "行ヘッダ", each if [値] = "案件名" then [インデックス] else null),
    // 抜き出した値を下フィル
    下方向へコピー済み1 = Table.FillDown(追加された条件列,{"行ヘッダ"}),
    // インデックス-行ヘッダで各繰り返しの行数を出す
    挿入された引き算1 = Table.AddColumn(下方向へコピー済み1, "減算", each [インデックス] - [行ヘッダ], type number),
    // 個別の行番号の”減算”と列番の”属性”をくっつけて、ピボット火曜の列ヘッダを作成
    結合された列 = Table.CombineColumns(Table.TransformColumnTypes(挿入された引き算1, {{"減算", type text}}, "ja-JP"),{"減算", "属性"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"結合済み"),
    // ピボット化で邪魔なインデックスを削除
    削除された列 = Table.RemoveColumns(結合された列,{"インデックス"}),
    // ”結合済み”を選択し、値列は”値”を選択
    ピボットされた列 = Table.Pivot(削除された列, List.Distinct(削除された列[結合済み]), "結合済み", "値")
in
    ピボットされた列

結果はこのようになります

解法1と違い、指定された座標に対応する列にデータが入っていくため
未入力があっても問題なく使えます

補足)これらの利点

これらは1は周期性、2は位置を基準に作られていて
ピボット解除は分解しないところのみを指定する関係で
列数方向への弾力性があります

こういった行列方向へある程度弾力性を持った取り込みができるのも
Power Queryの利点なので試してみてくださいね

補足)列のピボットの解説

列のピボットは分かりにくいので解説すると

UI上の操作は1行選択して実行するもので
選択行している列が、変換後の列のヘッダに、値列で選択した列がが表の中身のデータに変形される

列方向へは確か昇順で並ぶので昇順前提で列のヘッダを作ると後の処理が楽になります

数値以外を使う場合、隠れている集計オプションの中の「集計しない」を選択
この設定の場合、同じ枠に複数のデータが入るとエラーを吐くので注意が必要です

余談ですがこのエラーは列のピボットの出力にリストが入ると起きるエラーで
UI上からいじれない第4引数に
・Combiner.CombineTextByDelimiter (指定された区切り記号を使用して、テキストのリストを 1 つのテキストに結合する関数)
といったリストから値へ変換できる関数を入れてやれば回避できるそうです

第四引数に関数を指定すればできますよ。 pic.twitter.com/DGUYk1e3k4

— 狸 (@tanuki_phoenix) February 24, 2020

補足)コードの使用法

上記の表をexcelにコピペしてテーブル化した後

「空クエリの作成」か、対象テーブルを選択して「シートから(旧・テーブル範囲から)」を選択して

Power Query開いて「表示＞詳細エディタ」を開いてコードをすべて上記のものに入れ替えると動きます

各ステップの動作は、コード上や、ステップを選択して右クリックプロパティで説明が出るようにしておきましたので
確認してみてください