Power Queryで横長の表を縦長にしたい

Question

EXCELのPower Queryを使って、画像のような加工がしたいのです。
例は商品の数が２つですが、いくつであっても対応可能なダイナミックなクエりにしたいのです。
どんなＭ関数とか使えばいいのでしょうか？

Answer 1

//元データはワークシート上の"テーブル1"という名前のテーブルです。

let
Source = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
項目名={"顧客ID","分類","品名","色"},
可変列名リスト = Table.RenameColumns(Table.FromList(List.RemoveMatchingItems(Table.ColumnNames(Source),{"顧客ID"}), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "可変列名"}}),
分類消去 = Table.AddColumn(可変列名リスト, "カスタム1", each if Text.EndsWith([可変列名], "分類") then Text.Range([可変列名],0,Text.Length([可変列名])-2) else [可変列名]),
品名消去 = Table.AddColumn(分類消去, "カスタム2", each if Text.EndsWith([可変列名], "品名") then Text.Range([可変列名],0,Text.Length([可変列名])-2) else [カスタム1]),
色消去 = Table.AddColumn(品名消去, "カスタム3", each if Text.EndsWith([可変列名], "色") then Text.Range([可変列名],0,Text.Length([可変列名])-1) else [カスタム2]),
商品リスト = Table.RemoveColumns(Table.Group(色消去, {"カスタム3"}, {{"カウント", each Table.RowCount(_), type number}}),{"カウント"}),
追加されたカスタム = Table.AddColumn(商品リスト, "カスタム", each {"顧客ID",[カスタム3]&"分類",[カスタム3]&"品名",[カスタム3]&"色"}),
追加されたカスタム1 = Table.AddColumn(追加されたカスタム, "カスタム.1", each Table.SelectColumns(Source,[カスタム])),
追加されたカスタム2 = Table.AddColumn(追加されたカスタム1, "カスタム.2", each Table.ColumnNames([カスタム.1])),
追加されたカスタム3 = Table.AddColumn(追加されたカスタム2, "カスタム.3", each List.Zip({[カスタム.2],項目名})),
追加されたカスタム4 = Table.AddColumn(追加されたカスタム3, "カスタム.4", each Table.RenameColumns([カスタム.1],[カスタム.3])),
カスタム1 = Table.Combine(追加されたカスタム4[カスタム.4]),
フィルターされた行 = Table.SelectRows(カスタム1, each ([分類] <> null)),
並べ替えられた行 = Table.Sort(フィルターされた行,{{"顧客ID", Order.Ascending}})
in
並べ替えられた行

Answer 2

手元に環境が無いので試せませんが、

1. 適当な文字列で3列ずつ列のマージ
2. 列のアンピボット
3. 列の分割

がわかりやすそうですかね。
2,3は固定なので
1.をList.Generateあたりで列番号を生成しつつやる感じでしょうか。

縦横反転でもうまくやれば出来そうな気はしますが。

Comments

[garucia]

imihitoさん、回答ありがとうございます。
動作検証が十分とは言えませんが、一応動くものが出来ましたので、自己解決とさせていただきます。
商品別のテーブルを商品の数だけ作り、列名から商品名を削除して全てTable.Combineするという方法です。

Answer 3

そういった変換は正規化と呼ばれるもので、概ねUNION クエリーを使用します。
[Power Query / 取得と変換] ブックにある複数のワークシートをまとめる

上記では別シートを纏める例ですが、同じシートで項目を変更させながら纏める事になるかと思います。

いくつであっても対応可能な

そもそも列が可変になる定義は行えないと思いますので、その場合はVBAで変換を掛けるしかないかと。

追記

タグにPower BIがあったので。
Power BI で　UNION　ってどうやるのか

Comments

[garucia]

saziさん、回答ありがとうございます。
列名が同一の複数のテーブルをUNIONする場合はTable.Combine(tables as list)を使えばいいと思います。
例のように複数商品のがテーブルが横に並んだような構造のデータを、商品名を消去して列名を同じくし、商品の数だけテーブルを作ることができれば、Table.Combine(tables as list)でうまくいくと考えています。しかし、そのやり方がわかりません。

[sazi]

元となるシートを参照したシートを追加する数文準備すれば使えないですか？

[garucia]

ありがとうございます。
商品数・顧客数は変化するので追加するシート数も変化します。手作業でのシート追加などが必要ないようなやり方を模索しています。十分余裕をもった数のシートを準備しておくという方法もありますが、避けたいです。

>その場合はVBAで変換を掛けるしかないかと。

VBAで加工するのは既にやっていますが、M言語の勉強中なので Power Query で実現したいと考えています。

M言語はVBAに比べてハードルが低いと言われてますが、Power Queryエディタの扱いはワークシートやセルの扱いとは別世界です。EXCELの標準機能ですが、ワークシートよりもデータベース寄りではないでしょうか。

もうちょっと頑張ってみます。また何かヒントがありましたらお願いします。

[sazi]

データベースで可変の列を定義する事はできません。
その為の正規化です。
正規化した状態から、列に展開するのはクロス集計として機能が提供されていますが、その逆はありません。

[garucia]

データベースのことは詳しくないのですが、Power Queryを使うと列数・列名が不特定な場合も扱うことが可能です。「M Is for (Data) Monkey」や「Collect, Combine, and Transform Data Using Power Query in Excel and Power BI」に詳しく書いています。例に挙げた元データのように、データとは言えないような素材を加工して扱いやすいデータに変換できるので、強力なツールだと思います。
一応動くものが出来ましたので、自己解決とさせていただきます。
ありがとうございました。

[sazi]

英語圏まで手を伸ばしていないので、勉強不足でした。
因みにそれは、Power BIの標準機能で可能なんですよね？

[garucia]

Power BIでやってみました。
let の次行の Sourceを Current.Workbook ではなく、以下のようにするとできました。

Source = Table.SelectRows(Excel.Workbook(File.Contents("C:\Users\garucia\Desktop\商品.xlsx")), each ([Name] = "テーブル1")){[Item="テーブル1",Kind="Table"]}[Data],

エクセルファイルのパスは貴殿の環境に合わせてください。"テーブル1"という名前のテーブルが必要です。