はじめに
Excelの「データの取得変換（PowerQuery）」を使って定期的に外部のExcelブックなどで集計作業などをする場合、相手のフォーマットがばらばらなケースがあります。
- 余計な列が入っている
- 非表示に設定された列構成が違う
- シート名が年月（202011）のようなフォーマット
こうなるとPowerQueryはすぐにエラーを吐いてしまいます。ほしい列は常にあるのに、余計な列がついたりつかなかったりしているためにエラーになってしまいます。
手順概要
以下の手順でこれらを解決します。
前提として、毎月受領するブックのファイル名は毎回「受注データ.xlsx」にリネームしてから読み込ませます。
- シートを指定しないで読み込む
- 必要列を選択した状態で
他の列の削除を実行（Table.SelectColumnが使用される）
サンプルのExcelブック
以下のような構成です。
ファイル名：受注データ10月.xlsx、シート名：2020OCT
ファイル名：受注データ11月.xlsx、シート名：Sheet1、「原産地」という前月にはない列があります。
シートを指定しないで読み込む
-
「データの取得」から「ブックから」を選択
-
シートを選択せずにフォルダを選択
-
「データ変換」」を選択
-
「Data」以外は不要なので削除
↓
-
「Data」列を展開。「元の列名をプレフィックスとして使用します」はチェックオフ。
-
この時点では列名が「Column1」などの汎用的な名前になっているので「1行目をヘッダーとして使用」をクリック
-
「昇格されたヘッダー数」「変更された型」のクエリ―が追加される。「変更された型」は列名に依存してしまっているため削除する（ここがポイント）。
↓
-
残す列を選択する
-
ホーム ＞ 列の削除 ＞ 他の列の削除
コード詳細
Table.SelectColumn関数で選択した列が引数に渡されている。
let
ソース = Excel.Workbook(File.Contents("C:\Users\User1\Documents\受注データ.xlsx"), null, true),
削除された列 = Table.RemoveColumns(ソース,{"Name", "Item", "Kind", "Hidden"}),
#"展開された Data" = Table.ExpandTableColumn(削除された列, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}),
昇格されたヘッダー数 = Table.PromoteHeaders(#"展開された Data", [PromoteAllScalars=true])
in
昇格されたヘッダー数
let
ソース = Excel.Workbook(File.Contents("C:\Users\User1\Documents\受注データ.xlsx"), null, true),
削除された列 = Table.RemoveColumns(ソース,{"Name", "Item", "Kind", "Hidden"}),
#"展開された Data" = Table.ExpandTableColumn(削除された列, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}),
昇格されたヘッダー数 = Table.PromoteHeaders(#"展開された Data", [PromoteAllScalars=true]),
削除された列=Table.SelectColumns(昇格されたヘッダー数,{"客先発注番号","単価","数量","客先指定納期"})
in
削除された列
これで必要な列だけ抽出されます。
クエリを保存すると以下のようになります。
では、違う月のデータを読み込ませてみます。
先ほどは「受注データ11月.xlsx」⇒「受注データ.xlsx」にリネームして読み込みました。
「受注データ10月.xlsx」⇒「受注データ.xlsx」にリネームして読み込みます。（「すべて更新」のショートカットCtrl+Alt+F5）
エラーなく読み込めました！
（2020/11/21修正）
Table.SelectColumnsをコード操作する部分をGUI操作に修正しました。