Excel PowerQuery 列の展開を全てまとめて実施する方法

はじめに

PowerQueryで、非表示になっていた列を展開するにあたってTable.ExpandTableColumnという処理が入ることがある。

ただそれは上図のように、”名前”, “アドレス”,・・・とか1個1個の列名が全て列記されていくのが基本だ。

列の数が多いとその分だけソースコードも長ったらしくなるし、列名の構成が後から変わり得る場合などは、こういう風に列名を決めつけられてしまうのはよろしくないことだ。

そこで今回の記事では、Table.ExpandTableColumnにおいて1個1個の列名を書かず、全ての列を一気に展開する方法を解説する。
・・・といったものの正直、理解には努めたのだが、細かい理屈は理解しきれなかった。

なので、他所のサイト様で得た情報を脳死コピーしているだけの内容になっているのはお断りしておきます。

Excel PowerQuery 列のタイトルが可変の表をあれこれ加工してみる

2021年8月7日

Table.ExpandTableColumnが使われるケース

通常のExcelデータ展開

Table.ExpandTableColumnが使われる典型例の一つとして、
Excelのリボン「データ」タブ
→データの取得
→ファイルから
→ブックから

とし、一番上のフォルダマークを選択した状態で「データの変換」を押す。

次に表示された画面で「Data」列の右端にある□ボタンを押し、「OK」を押す。

そうすると、ソースコードに
Table.ExpandTableColumn
と入り、あと、項目名が色々と列記される(上図でいう”名前”, “アドレス”,・・・の部分)。

他には、クエリのマージ機能で、マージされた側のクエリのデータ(最初は非表示にされている)を展開する場合。

この場合も同様にTable.ExpandTableColumnが入る。

Table.ExpandTableColumnで全ての列を展開する方法

では、Table.ExpandTableColumnで、全ての列を一気に展開する方法について。

ステップ名称の変更

まずTable.ExpandTableColumnが使われるのは、下図のように「展開されたData」とかいうステップになっていると思う。

ただ、この「展開されたData」というステップ名は、詳細エディターでソースコードを見たときには
#“展開された Data”
と「#」マークが付いたりして分かりにくくなる。

そして、他に色々なケースでも汎用的に今回の説明を使えるよう、

• ステップ「展開されたData」→「展開」
• その1個前のステップ→「前のステップ」

とステップ名を変更することにしてみる。

ソースコードの変更

すると、詳細エディターのソースコードは下図のようになる。

そして「展開」のところのソースコードは、次のようになっている。

“Data“というのは、下図でいう「Data」列のように非表示データを格納・展開する部分の列名であり、この列名が別のものだったらソースコードもそれに合わせ書き換える。

そして、「展開」のところのソースコードを、次のように書き換えると問題解決だ。

最初に断ったとおり、細かい理屈は理解しきれなかったので、その辺りの解説はできない。

この定型のソースコード

展開 = Table.ExpandTableColumn( 前のステップ ,  “Data“, List.Union(List.Transform( 前のステップ [Data],each Table.ColumnNames(_))))

は、私はIME変換に「てんかい」として登録している。

改良法―Table.Combineを使用 ―

もっとシンプルなやり方がありました。

クエリを縦に結合するのに用いるTable.Combineを使って

と書けば良い。

この方法はTakeshi Kagataさんより教えていただきました。感謝。