Go to Qiita Advent Calendar 2024 Top
LoginSignup
36
35

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@olt_yt

Power Queryで神エクセル分解１

Last updated at Posted at 2020-02-15

よく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マスにデータが入ってループしていることを使って処理します
###テーブル使用版

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

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

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

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

##解法2(座標と、表の周期性をつかう)
image.png
処理の感じとしてはこんな感じに、個別に行番と列番を振ってやって
それらを使い並べ替えていきます
###テーブル使用版

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

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

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

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

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

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

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

#補足)コードの使用法
上記の表をexcelにコピペしてテーブル化した後
image.png
「空クエリの作成」か、対象テーブルを選択して「シートから(旧・テーブル範囲から)」を選択して
image.png
Power Query開いて「表示＞詳細エディタ」を開いてコードをすべて上記のものに入れ替えると動きます
image.png
各ステップの動作は、コード上や、ステップを選択して右クリックプロパティで説明が出るようにしておきましたので
確認してみてください

36
35
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
36
35

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?