Help us understand the problem. What is going on with this article?

Power Queryで神エクセル分解2

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

対象

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

列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

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

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
    ピボットされた列

こんな感じで処理すると
image.png
こうなるのであとは並べ替え・ヘッダ付け下フィルで完成

途中の条件列の処理は
image.png
こんな感じで2項目のスイッチ的使い方を使用
適当にxを入れているのは必要としないところまで下フィルが漏れるのを防止しています
下フィルまで済ます床のようになります
image.png
このあとx→nullに変換後、上にフィルをして行番を調整しています

解法2

前半

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
    ピボットされた列

後半

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
    ピボットされた列

これらを実行すると
image.png
このようになるので
このあと「カスタム.1」でマージをすると
image.png
このように目的のデータが取れます

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした