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

Power Queryで神エクセル分解1

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

対象

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
案件名 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

前提として全枠にデータが入っている場合
image.png
このように22マスにデータが入ってループしていることを使って処理します

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

このような感じで処理できる
image.png
不要な行を消してヘッダを調整すれば完成

利点はシンプルですが、
難点は枠に入力がない場合、ピボット解除時に「null」データが入ったマスは削除され
ループが破綻してずれます
image.png
回避方法としては消える可能性がある列にnullをほかのスペース等で置換し
削除されないようにする方法等があります

解法2

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

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

結果はこのようになります
image.png
解法1と違い、指定された座標に対応する列にデータが入っていくため
未入力があっても問題なく使えます

列のピボットの解説

列のピボットは分かりにくいので解説すると
image.png
UI上の操作は1行選択して実行するもので
選択行している列が、変換後の列のヘッダに、値列で選択した列がが表の中身のデータに変形される
image.png
列方向へは確か昇順で並ぶので昇順前提で列のヘッダを作ると後の処理が楽になります

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

コードの使用法

上記の表をexcelにコピペしてテーブル化した後
image.png
空クエリの作成か、対象テーブルを選択してテーブル範囲からを選択して
image.png
Power Query開いて「表示>詳細エディタ」を開いてコードをすべて上記のものに入れ替えると動きます

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

解法2の利点

解法2に関して、行列ともに座標はコード上の指定はなく
0行1列の「案件名」を基準に自動で生成されています
image.png
そのため行を追加して更新をかけると
image.png
このように自動で処理されます

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

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
ユーザーは見つかりませんでした