MENU
  1. ホーム
  2. 技術記事
  3. Power Queryとデータフロー――表の意味から変換を設計する

Power Queryとデータフロー――表の意味から変換を設計する

LWP | Power Queryとデータフロー――表の意味から変換を設計する

Power Queryとデータフロー――表の意味から変換を設計する

アユさんとシャケモンが、売上と商品マスターから区分別合計を作る流れを読みます。

ストーリー

アユさんは、処理手順より先に「どのデータが、何に変わるのか」を確認したいと考えています。シャケモンと、売上3行と商品マスター2行を例に、データフローダイアグラム（DFD）による設計と、Power Queryによる実装を分けて読みます。表と会話は説明用の例です。

1 Power Queryは、データの意味と流れから読む

売上明細3行と商品マスター2行を入力し、食品150と雑貨200の区分別合計2行を得る

図1｜入力と出力では、1行が表す意味が違います。

2 DFDの4要素で、データの関係を描く

処理・データフロー・保存場所・外部実体を描き分け、売上集計の境界を示す

図2｜DFDの矢印は渡すデータを表します。処理順を命令する矢印とは区別します。

3 商品キーで結び、区分を付けてから集計する

一意な商品マスターで区分を付け、3行の明細を2行の区分別合計へ集計する

図3｜正常な対応では、区分付与後も明細3行と総額350を保ちます。

4 マスターの重複で、展開後の行と金額が増える

Aのマスターが重複すると展開後5行となり、食品300と雑貨200で合計500になる

図4｜マスターの重複と未登録商品を、別の問題として確認します。

5 テストは入力を置き換え、同じ変換へ渡す

通常入力なら合計350、テスト入力Aの10だけなら食品10となり、入力を混ぜず切り替える

図5｜入力だけを切り替え、結合・展開・集計は共通にします。

6 クエリは、データの責務と再利用で分ける

売上・商品マスター・区分付き明細・集計の責務と、クエリやletや関数へ分ける選択肢を整理する

図6｜四つのクエリは設計上の分割例です。一変換一クエリという必須規則ではありません。

7 依存の矢印は、実行順や1回だけの取得を保証しない

依存関係と遅延評価・フォールディング・更新を区別し、キー・件数・総額・例外を検証する

図7｜設計図と、実際の評価・取得回数・保存の確認を分けます。

まとめ

まず各表の1行の意味、キー、渡すデータ、出力の粒度を決めます。結合ではマスターの一意性と未一致を確認し、展開前後の行数と総額を追います。

テスト入力は通常入力へ混ぜず、同じ変換へ置き換えて渡します。クエリの分割は責務と再利用に合わせて選び、参照関係だけから実行順・1回だけの取得・永続保存を推定しません。

今回の仕掛け

データモデルを考えるアユさんと、Excel・Mの実装を読むシャケモンが、表の意味、変換、結果確認をつなぎます。操作手順を並べる前に入力・属性・関係を描き、図による設計と実機検証を分けました。

今回のソースコードのサンプル

Excel Power Queryの空のクエリの詳細エディターへ入れる、自己完結の例です。外部ファイル・TakiLibは不要です。正常ケースのマスターは商品ごとに一意で、全明細が登録済みという前提です。

サンプルコード 入力を切り替えて区分別の売上を集計する

01

 
let

02

 
    use_test = false,

03

 
    sales_rows =

04

 
        #table(

05

 
            type table [商品 = text, 売上 = nullable number],

06

 
            {{"A", 100}, {"B", 200}, {"A", 50}}

07

 
        ),

08

 
    test_sales =

09

 
        #table(

10

 
            type table [商品 = text, 売上 = nullable number],

11

 
            {{"A", 10}}

12

 
        ),

13

 
    product_master =

14

 
        #table(

15

 
            type table [商品 = text, 区分 = text],

16

 
            {{"A", "食品"}, {"B", "雑貨"}}

17

 
        ),

18

 
    selected_sales = if use_test then test_sales else sales_rows,

19

 
    sales_summary =

20

 
        let

21

 
            joined =

22

 
                Table.NestedJoin(

23

 
                    selected_sales, {"商品"}, product_master, {"商品"},

24

 
                    "商品情報", JoinKind.LeftOuter

25

 
                ),

26

 
            expanded =

27

 
                Table.ExpandTableColumn(joined, "商品情報", {"区分"}, {"区分"}),

28

 
            grouped =

29

 
                Table.Group(

30

 
                    expanded, {"区分"},

31

 
                    {{"売上合計", each List.Sum([売上]), type nullable number}}

32

 
                ),

33

 
            sorted = Table.Sort(grouped, {{"区分", Order.Ascending}})

34

 
        in

35

 
            sorted

36

 
in

37

 
    sales_summary

関数の説明

Table.NestedJoinで商品キーを左外部結合し、Table.ExpandTableColumnで区分を展開します。Table.Groupで区分別にまとめ、List.Sumで売上を合計します。Table.Sortは集計後の表示順を明示します。

コードの説明

use_testがfalseなら通常の売上3行、trueなら商品Aの10だけを選びます。選んだ入力に対して、結合・展開・集計・並べ替えを共通に実行します。外側は入力と業務結果、内側のletは集計結果を作る操作の詳細です。掲載例は1本のクエリであり、図6の四つのクエリを作成するコードではありません。

入力・概要

通常入力は商品Aの100、商品Bの200、商品Aの50。テスト入力は商品Aの10。商品マスターはAが食品、Bが雑貨です。売上の型はnullable numberとして、追加のnullケースも検証できる形にしています。

結果

WindowsデスクトップExcel 16.0、ビルド20430の独立した新規ブックで、掲載コードと入力を変えた7ケースを実行確認しました。

  • 通常入力：雑貨200、食品150の2行。合計350。

  • テスト入力：食品10の1行。

  • Aを同じ食品区分で2行にしたマスター：食品300、雑貨200。合計500。

  • 重複マスターの展開明細：Aの100が2行、Aの50が2行、Bの200が1行。5行・合計500。

  • 最後のAの50を未登録Cの50へ変更：区分nullの50、雑貨200、食品100。未一致を自動除外しません。

  • Aの100をnullへ変更：食品50、雑貨200。

  • Aの売上2件を両方nullへ変更：食品の売上合計はnull、雑貨200。

図版の表の行順は説明用です。掲載コードの出力順は区分の昇順で、今回の通常入力では雑貨、食品の順でした。

読むポイント

重複・未登録を止める業務用の検査は、この短い変換例には実装していません。正常入力の前提を確認し、運用する場合は例外の扱いを決めます。List.Sumはnull以外を合計し、全てnullのグループはnullになります。nullを0へ置き換えるかは業務仕様です。

今回の実行確認は、コード内の説明用データに限定します。外部接続の取得回数、プライバシー設定、フォールディング、通常の業務ブックの更新は測定していません。

出典メモ

元ネタ「Power Queryを理解したければデータフローダイアグラムを学ぼう」を7枚へ再構成しました。元ネタの入力と通常・テストの切替を保ち、局所変数名・letの粒度・展開後の列名を現行の記述基準に合わせています。掲載Mは構文・命名の静的検査にも合格しました。

DFDの4要素と設計モデルはIBM：What is a data flow diagram?、結合・展開・集計の仕様はMicrosoft Learn：Table.NestedJoin、Table.ExpandTableColumn、Table.Groupを参照しました。

遅延評価とフォールディングはMicrosoft Learn：Query evaluation and query folding、nullを含む合計はList.Sumを参照しました。

Copyright © 2026 LWP 山中 一弘

本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。

Copyright © LWP 山中 一弘 |