Posted at

[Excel]PowerQueryを使って表をデータに変換する

PowerQueryでお題があったの、でできる限りボタンポチポチで変換してみました。

(もっとあっさりできるかもしれません。)

以下の表をデータに変換します。

この表は横や縦に拡張される可能性があるので、それを考慮した変形になります。

image.png


PowerQueryに取り込みます

[データ]-[テーブルまたは範囲から]で取り込みます。

image.png

image.png


転置します

[変換]-[入れ替え]で表を転置します。

image.png


インデックス列を追加します

[列の追加]-[インデックス列]-[0から]でインデックスを追加します。

image.png


ピボット解除します

インデックス列を選択して[変換]-[列のピボット解除]-[そのほかの列のピボット解除]でピボット解除します。

image.png

ここまでくるとなんとなくゴールが見えてきますね。


インデックス+1の列を追加します

[列の追加]-[カスタム列]で[インデックス]+1を指定します。

image.png

image.png


自分自身をマージします

[ホーム]-[クエリのマージ]でカスタム列とインデックス列と属性をキーに左外部結合します。

image.png

image.png


テーブルを展開し値を抽出します

見出しの展開ボタンを押して値だけ展開します。

image.png


インデックスが偶数の行だけに絞ります

マウス操作で偶数のみ抽出する方法が見当たらなかったので、一旦適当にフィルタします。0にしときます。

image.png

image.png

数式バーにて、[インデックス]=0Number.IsEven([インデックス])に書き換えます。

image.png


不要な列を削除し見出しを整えて完成です

image.png

let

ソース = Excel.CurrentWorkbook(){[Name="テーブル"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"製品番号", type text}, {"数量", Int64.Type}, {"製品番号2", type text}, {"数量3", Int64.Type}, {"製品番号4", type text}, {"数量5", Int64.Type}, {"数量6", type text}, {"数量7", Int64.Type}}),
転置されたテーブル = Table.Transpose(変更された型),
追加されたインデックス = Table.AddIndexColumn(転置されたテーブル, "インデックス", 0, 1),
ピボット解除された他の列 = Table.UnpivotOtherColumns(追加されたインデックス, {"インデックス"}, "属性", "値"),
追加されたカスタム = Table.AddColumn(ピボット解除された他の列, "カスタム", each [インデックス]+1),
マージされたクエリ数 = Table.NestedJoin(追加されたカスタム, {"カスタム", "属性"}, 追加されたカスタム, {"インデックス", "属性"}, "追加されたカスタム", JoinKind.LeftOuter),
#"展開された 追加されたカスタム1" = Table.ExpandTableColumn(マージされたクエリ数, "追加されたカスタム", {"値"}, {"追加されたカスタム.値"}),
フィルターされた行 = Table.SelectRows(#"展開された 追加されたカスタム1", each (Number.IsEven([インデックス]))),
削除された列 = Table.RemoveColumns(フィルターされた行,{"インデックス", "属性", "カスタム"}),
#"名前が変更された列 " = Table.RenameColumns(削除された列,{{"値", "製造番号"}, {"追加されたカスタム.値", "数値"}})
in
#"名前が変更された列 "

もっとあっさりできるようなできないような。

M言語をマスターすればもっとすっきりするんでしょうね!