左の表から右のような集計表を作りたい場合。「そもそも入力表にセル結合なんていれんなっ！」といいたい気持ちをぐっと押さえてクエリでデータクレンジングしてしまおう。
まずこういう表にはテーブル書式なんて設定されていないので、クエリで利用する時は範囲を名前定義する必要がある。しなくても「ファイルから」で指定すればクエリを作ることは一応できるけど、この場合ファイルパスが変わったらリンクが切れてしまうので、基本はＮＧ。
範囲が固定か分からないので、今回は列ごと範囲を定義してしまうことにする。A:D列を選択した状態で名前ボックスに「データ範囲」と入力して Enterで確定。
［データ］→データの取得と変換の［テーブルまたは範囲から］これでクエリが作成できるので、ここからはエディタ画面での設定になる。
最初にこれだけは絶対なのがデータ範囲を絞り込むこと。今は列参照しているので範囲が馬鹿でかくなっている。これをちゃんとやって緩衝地点を作っておかないと、後々の処理が重くなってしまう。その際、「これとこれをフィルタで抽出」とやるより「上から必要なデータだけを残す」ほうが処理負担が少ない。
右ペインの「クエリの設定」で「ソース」を右クリックして「後にステップの挿入」。「カスタム1」が出来上がるので数式バーに下記を入力。
= Table.Buffer(Table.FirstN(ソース, each [Column4]<>null))「Column4（数量）」は必ずデータがあるだろうから、Table.FirstNで「上から Column4にデータが入力されている行」だけを残している。Table.Bufferはバッファ処理を入れるための M関数。
ここまでできたら後は、マウス操作だけで終わる作業なんでさらっと。
［変換］→［１行目をヘッダーとして使用］
「日付」・「商品」列を選択した状態で右クリック［フィル］→［下］
［ファイル］→［閉じて読み込む］→［閉じて次に読み込む］
データンポートで［ピボットテーブル レポート］を選択し
既存のワークシートで読み込み先のセルを指定して［OK］ここから先は、普通のピボットテーブルと全く同じ。
レポートのレイアウト：表形式
行フィールド：「日付」
列フィールド：「商品」「サイズ」
値フィールド：「合計 / 数量」
体裁を整えたら完成。
