POS・EC・卸で異なる列名、データ型、売上の粒度をチャネル別の前処理クエリでそろえ、共通スキーマへ追加します。 本記事では、完成形を先に確認したうえで、入力データの整え方、処理の組み立て、検算、更新運用までを順番に説明します。単に機能のボタンを追うのではなく、翌月のデータへ更新しても同じ結果を再現できることをゴールにします。
この記事でできるようになること
POS、EC、卸の列名・型・粒度をそろえ、更新可能な統合テーブルを作る。あわせて、結果が合わないときにどこから確認すべきか、元データの様式変更へどう備えるかも分かります。
対象は、販売チャネルごとに形式が異なる売上ファイルを月次で集計する担当者です。サンプルの列名は読み替えられますが、データの粒度、キー、例外処理という考え方は、業種やファイル名が変わっても共通して使えます。
Power Queryで解決する業務課題と完成形
この業務で難しいのは、計算式そのものよりも、入力の意味がそろっていない状態で集計を始めてしまうことです。チャネル名、売上日、商品コード、伝票番号、明細番号を共通キー候補とし、集計粒度を『1伝票の商品明細1行』へ統一する、という設計を最初に固定すると、処理途中の判断がぶれにくくなります。
完成形は、担当者が毎回セルをコピーして作る表ではありません。元ファイルを所定の場所へ置き、更新を実行すると、同じ変換と判定が再実行される仕組みです。結果表には数値だけでなく、元ファイル、判定状態、エラー理由などの追跡列も残します。
図1では、入力から出力までの全体像を示しています。左側の元データを直接集計せず、中央で型・粒度・キーを整えた後、右側の確認用テーブルへ渡す流れを見てください。
入力データとキーを先に設計する
作業を始める前に、入力表を「何の1行か」という粒度で説明できる状態にします。同じ列名でも、明細1行、日次合計1行、月末残高1行では、結合や合計の意味が異なります。粒度が違う表をそのまま結合すると、行が増えたり金額が重複したりします。
使用するデータ
- POS明細: 取引日時・JAN・数量・税込売上
- EC受注明細: 注文日・SKU・単価・数量・値引
- 卸売実績: 計上月・商品コード・出荷数量・税抜金額
キー設計の考え方
チャネル名、売上日、商品コード、伝票番号、明細番号を共通キー候補とし、集計粒度を『1伝票の商品明細1行』へ統一する。キー候補は、見た目が同じだけでは十分ではありません。空白、全角半角、先頭ゼロ、日付と日時、重複の有無を確認し、結合前に一意性を検査します。
図2では、入力表ごとに役割と粒度を分けています。どの列を結合に使い、どの列を結果確認に残すかを、変換操作より前に整理するのがポイントです。
Power Queryで処理を組み立てる手順
ここから実装します。操作名を暗記するより、各ステップの入力と出力を確認してください。適用したステップには「型の変更1」のような自動名を残さず、「商品コードを文字列化」「請求単位へ集約」のように目的が分かる名前を付けます。
手順1: 各チャネルをステージングクエリとして読み込む
各チャネルをステージングクエリとして読み込む段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
元データへ直接多くの処理を積まず、参照用またはステージング用のクエリを分けると、取得と業務ロジックの境界が明確になります。データソース変更時にも、影響範囲を取得部分へ限定できます。
手順2: 列名を共通名へ変更する
列名を共通名へ変更する段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
手順3: 商品コードと日付のデータ型を統一する
商品コードと日付のデータ型を統一する段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
手順4: 税込・税抜や値引後金額の定義をそろえる
税込・税抜や値引後金額の定義をそろえる段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
手順5: チャネル列と元ファイル列を追加する
チャネル列と元ファイル列を追加する段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
手順6: 共通列だけを同じ順序へ並べてクエリを追加する
共通列だけを同じ順序へ並べてクエリを追加する段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
図3は、6つの操作を左から右へ並べたものです。途中でエラー行を捨てず、正常データと要確認データを分岐させている点に注目してください。
式の意味を分解して理解する
画面操作だけでも一部は作れますが、再利用性や例外処理を高めるには式の意味を確認する必要があります。次の式は、このユースケースの中心となる処理例です。実際のテーブル名と列名へ置き換えてください。
Table.SelectColumns(
Source,
{"チャネル", "売上日", "商品コード", "数量", "売上金額"},
MissingField.UseNull
)共通スキーマに存在する列だけを明示し、欠けた列はnullで補います。チャネル固有列はステージング側に残し、統合テーブルを不用意に太くしません。
式を一度に完成させず、変数または適用したステップごとに中間結果を表示すると理解しやすくなります。特にnull、0、空文字、存在しないキーは別物です。画面上で同じ空欄に見えても、比較式や集計結果は変わります。
図4では、式へ渡る入力、評価条件、出力の3段階を示しています。式の文字列だけでなく、どのフィルターや行が評価対象になるかを確認してください。
検算で更新結果の正しさを確認する
更新がエラーなく完了しても、業務上正しいとは限りません。検算は元データと結果を同じ軸で比較し、件数・金額・例外の3方向から行います。まず小さなサンプルで正解を作り、その後に全件へ広げます。
検算1: チャネル別の元件数と統合後件数が一致する
チャネル別の元件数と統合後件数が一致することを確認します。差がある場合は、差額だけを見るのではなく、どのキーの行が増減したかを反結合や重複一覧で抽出します。件数が一致していても、別の行が入れ替わっている可能性があるため、代表キーと合計値の両方を確認してください。
検算2: 売上金額合計をチャネル別に突合する
売上金額合計をチャネル別に突合することを確認します。差がある場合は、差額だけを見るのではなく、どのキーの行が増減したかを反結合や重複一覧で抽出します。件数が一致していても、別の行が入れ替わっている可能性があるため、代表キーと合計値の両方を確認してください。
検算3: 商品コードがnullの行を抽出する
商品コードがnullの行を抽出することを確認します。差がある場合は、差額だけを見るのではなく、どのキーの行が増減したかを反結合や重複一覧で抽出します。件数が一致していても、別の行が入れ替わっている可能性があるため、代表キーと合計値の両方を確認してください。
検算4: 伝票番号と明細番号の重複を調べる
伝票番号と明細番号の重複を調べることを確認します。差がある場合は、差額だけを見るのではなく、どのキーの行が増減したかを反結合や重複一覧で抽出します。件数が一致していても、別の行が入れ替わっている可能性があるため、代表キーと合計値の両方を確認してください。
検算結果を保存する
更新日、元ファイル名、元件数、結果件数、主要合計、エラー件数を1行で残すと、翌月の変化を比較できます。検算表も成果物の一部です。
図5は、元データ、変換結果、例外一覧を照合する検算ループです。差が0になることだけでなく、除外した行に説明可能な理由があることを確認します。
よくある失敗と修正方法
このテーマでは、処理が止まるエラーよりも、止まらずに誤った値が出る状態へ注意が必要です。次の失敗は、プレビューだけでは気づきにくいため、設計段階で確認列と例外一覧を用意します。
失敗1: ECのキャンセルを売上として残す
ECのキャンセルを売上として残すと、結果表は作れても意味が変わります。まず該当条件だけを抽出した確認クエリまたは確認メジャーを作り、発生件数と影響額を見ます。そのうえで、自動補正できるもの、人の判断が必要なもの、元システムで直すものへ分けます。
対処時は、異常行を単純に削除しないでください。除外理由、元キー、元ファイルを残せば、後から担当者へ確認できます。修正後は正常行だけでなく、この失敗条件が0件または想定件数になったことを再検算します。
失敗2: 税込と税抜を同じ列へ混在させる
税込と税抜を同じ列へ混在させると、結果表は作れても意味が変わります。まず該当条件だけを抽出した確認クエリまたは確認メジャーを作り、発生件数と影響額を見ます。そのうえで、自動補正できるもの、人の判断が必要なもの、元システムで直すものへ分けます。
対処時は、異常行を単純に削除しないでください。除外理由、元キー、元ファイルを残せば、後から担当者へ確認できます。修正後は正常行だけでなく、この失敗条件が0件または想定件数になったことを再検算します。
失敗3: 月次集計済みの卸データを明細と同じ粒度で追加する
月次集計済みの卸データを明細と同じ粒度で追加すると、結果表は作れても意味が変わります。まず該当条件だけを抽出した確認クエリまたは確認メジャーを作り、発生件数と影響額を見ます。そのうえで、自動補正できるもの、人の判断が必要なもの、元システムで直すものへ分けます。
対処時は、異常行を単純に削除しないでください。除外理由、元キー、元ファイルを残せば、後から担当者へ確認できます。修正後は正常行だけでなく、この失敗条件が0件または想定件数になったことを再検算します。
失敗4: 先頭ゼロ付き商品コードを数値へ変換する
先頭ゼロ付き商品コードを数値へ変換すると、結果表は作れても意味が変わります。まず該当条件だけを抽出した確認クエリまたは確認メジャーを作り、発生件数と影響額を見ます。そのうえで、自動補正できるもの、人の判断が必要なもの、元システムで直すものへ分けます。
対処時は、異常行を単純に削除しないでください。除外理由、元キー、元ファイルを残せば、後から担当者へ確認できます。修正後は正常行だけでなく、この失敗条件が0件または想定件数になったことを再検算します。
毎月更新できる運用へ仕上げる
一度だけ正しい集計を作ることと、毎月安全に更新できることは別です。担当者が変わっても、入力場所、更新順、検算、エラー時の対応が分かる状態へ整えます。
運用ポイント1: 列対応表を別シートで管理する
列対応表を別シートで管理するようにします。ルールはクエリやメジャーの中だけへ閉じ込めず、更新手順と確認表にも同じ言葉で記載します。誰がいつ確認したかを残すと、データの訂正や再更新が必要になったときに判断しやすくなります。
運用ポイント2: 新チャネル追加時はステージングだけ作る
新チャネル追加時はステージングだけ作るようにします。ルールはクエリやメジャーの中だけへ閉じ込めず、更新手順と確認表にも同じ言葉で記載します。誰がいつ確認したかを残すと、データの訂正や再更新が必要になったときに判断しやすくなります。
運用ポイント3: 更新日時と取込ファイル名を残す
更新日時と取込ファイル名を残すようにします。ルールはクエリやメジャーの中だけへ閉じ込めず、更新手順と確認表にも同じ言葉で記載します。誰がいつ確認したかを残すと、データの訂正や再更新が必要になったときに判断しやすくなります。
運用ポイント4: 月次締め後に件数・金額の検算表を保存する
月次締め後に件数・金額の検算表を保存するようにします。ルールはクエリやメジャーの中だけへ閉じ込めず、更新手順と確認表にも同じ言葉で記載します。誰がいつ確認したかを残すと、データの訂正や再更新が必要になったときに判断しやすくなります。
図6では、データ配置、更新、検算、例外確認、確定という運用サイクルを示しています。自動化の目的は確認をなくすことではなく、確認すべき箇所を限定することです。
導入前に確認したい適用範囲
Power Query 売上データ 統合は、繰り返し発生し、入力と判断ルールを説明できる業務で特に効果を発揮します。一方、毎回まったく異なる様式を人が読み解く作業や、正解が担当者の経験だけに依存する判断は、先にルールの整理が必要です。ツールへ移す前に、誰が見ても同じ判定になる条件を言葉とサンプルで残してください。
導入判断では、POS明細: 取引日時・JAN・数量・税込売上を安定して取得できるか、各チャネルをステージングクエリとして読み込むための権限や保存場所があるかを確認します。更新頻度が低く対象が数件だけなら、仕組みの保守コストが手作業を上回る場合もあります。件数だけでなく、誤集計時の影響、監査証跡、担当者交代の頻度も判断材料です。
本番化する前に、正常例だけでなく、ECのキャンセルを売上として残すケースを含むテストデータを用意します。期待結果を先に表へ書き、処理結果と一致するかを確認してください。正解表があれば、後日の式変更や元データ変更でも回帰テストを行えます。
小さく始める基準
最初の対象は1期間・1部門・代表的なPOS明細に絞ります。検算方法まで合意できたら対象範囲を広げ、例外の発生率と確認工数を記録します。
本記事にはアフィリエイトリンクが含まれます。リンク経由で申込みがあった場合、運営者に報酬が支払われることがあります。
動画で体系的に学びたい方へ
ここまでの内容を自分のデータへ応用するには、画面操作と式の評価結果を往復しながら練習することが近道です。次の3講座は、この記事と学習範囲が重なるものを選んでいます。講座内容、価格、利用環境は変更されることがあるため、リンク先の最新情報を確認してから判断してください。
1. Excel＆Power BI両方で使えるPower Query 完全ガイド 初級者から上級者まで対応
Power Queryを実務レベルまで深めたい人。ExcelとPower BIの両方で使いたい人。
2. 【続】Microsoft – Excel Power Pivot 入門講座 ～Power Query Editor編～
Power Query Editorの操作を体系的に学びたい人。変換手順を動画で確認したい人。
3. Microsoft Excel – Power Pivot, Power Query, DAX 入門講座
Power QueryとDAXをExcelでまとめて学びたい人。データモデルの基礎から確認したい人。
Power Query 売上データ 統合を実務で使うためのまとめ
POS・EC・卸で異なる列名、データ型、売上の粒度をチャネル別の前処理クエリでそろえ、共通スキーマへ追加します。 成否を分けるのは、チャネル名、売上日、商品コード、伝票番号、明細番号を共通キー候補とし、集計粒度を『1伝票の商品明細1行』へ統一するという設計を、操作前に決められるかどうかです。
実装後は、チャネル別の元件数と統合後件数が一致することから検算を始めてください。さらに、列対応表を別シートで管理することで、翌月以降の更新にも耐えやすくなります。
最初から全データへ適用せず、代表的な正常例、境界値、例外例を含む小さなサンプルで結果を確定してから全件へ広げると、修正範囲を小さくできます。
コメント