【Power BI 初級編】原材料から製品を一発検索するダッシュボードを作ってみよう

製造現場では、「どの材料がどの製品に使われているか瞬時に把握できない」という課題は珍しくありません。BOM（部品表）は整備されているものの、

• 一覧性が低い
• 製品群ごとにファイルが分かれていて、横断して検索できない

といった理由で調査に時間がかかり、欠品や品質トラブル時の迅速な意思決定が難しくなり後手に回るケースは多いです。そこで今回は、BOMを基に原材料から製品を一発検索できる簡易BIシステムをPower BIで作っていきます。

仮想ケース

ということで、今回はBOMから製品を検索できる仕組みをPower BIで作成していきます。

完成イメージ

作成するダッシュボードは、次のような３区画で構成します。

• 左：原材料カテゴリ／原材料名のフィルター
• 中央：左で選択した原材料を使う製品カテゴリ
• 右：指定した製品と使用原材料の一覧

なお、製品数が少なければ、中央と右を統合したシンプルなビューにすると見やすいです。

元データ（初心者でも扱いやすい構造）

今回は、学習しやすいよう、Excelのシンプルな表を使います。
イメージしやすいよう、「製品＝パスタ」とします。

• 製品ID（ “Recipe” ：連番）
• 製品名（ “Pasta” ）
• 製品カテゴリ（ “PastaCategory” ）
• 使用する原材料（ “Ingredients(,)” ：カンマ区切り）

使用する原材料の列（ “Ingredients(,)” ）はカンマ区切りで入力していますが、後ほどPower Query で行方向に展開し、１材料１セルに配置し直します。

Recipe	Pasta	Pasta Category	Ingredients(,)
Recipe1	ボロネーゼ	ミートソース系	パスタ1.6mm,オリーブオイル,合いびき肉,にんにく,玉ねぎ,赤ワイン,トマト缶(ホール),顆粒コンソメ,塩,ナツメグ,バター,こしょう,粉チーズ,水
Recipe2	カルボナーラ	クリーム系	パスタ1.6mm,オリーブオイル,にんにく,ベーコン,水,塩,味の素,チェダーチーズ(スライス),溶き卵,こしょう
Recipe3	ナポリタン	トマト系	パスタ1.6mm,サラダ油,ソーセージ,にんにく,玉ねぎ,マッシュルーム,ケチャップ,ウスターソース,水,顆粒コンソメ,バター,ピーマン,こしょう,粉チーズ
Recipe4	ペペロンチーノ	オイル系	パスタ1.4mm,オリーブオイル,ソーセージ,にんにく,赤唐辛子,水,しょうゆ,塩,味の素,オリーブオイル,乾燥パセリ
…	…	…	…

データモデリングの流れ

今回作成するのは、Power BIの分析効率を高めるために重要な次の5テーブルです。

1. 前処理テーブル（Pasta）：他テーブルを効率よく作成するための補助テーブル
2. 原材料マスタ（D_Ingredients）：ディメンションテーブル
3. 原材料カテゴリ（D_IngredientCategory）：ディメンションテーブル
4. 製品マスタテーブル（D_PastaName）：ディメンションテーブル
5. 製品値テーブル（F_Pasta）：製品と原材料を紐づける際に中心となるファクトテーブル

ここからは、
ⒶPower Queryでの操作
ⒷPower BIでのモデリング
ⒸPower BIでのビジュアル化
の順に進めていきます。

ⒶPower Queryでの操作

Excelの読み込み

【目的】

• Power QueryにExcelを読み込む

【手順】

• Power BIを起動し「ホーム」→「Excel ブック」を選択
• 「ナビゲーター」が開く。該当する表（”Pasta”）を選択し、「データの変換」を選択
• Power Query上に “Pasta” テーブルが読み込まれる

テーブルの前処理（”Pasta”テーブルの整形）

【目的】

• 他のテーブル作成を効率化するために作成する
  （本テーブルを「複製」して他のテーブルを作成する）

【手順】

• ヘッダー名を設定
  • 1行目の値に “Column1” 等が設定されている場合、「変換」→「テーブル」の「１行目をヘッダーとして使用」を選択

• カンマ区切りで入力された原材料を各行に分割
  （1行に1原材料を配置し、各ディメンションテーブルの下地を作る）
  • 「変換」→「列の分割」の「区切り記号による分割」を選択
  • 「区切り記号による列の分割」が開く
    • 「区切り記号」から「コンマ」を選択
    • 分割は「区切り記号の出現ごと」
    • 「詳細設定オプション」を開き、「分割の方向」で「行」を選択
    • セル内に「引用符文字（”）」を使っていなければ、「”」でも「なし」も可

原材料マスタ（D_Ingredients）の作成

【目的】

• 一意の原材料テーブル（ディメンションテーブル）を作成し、ダッシュボードのスライサーとする。これにより、集計時の過不足を防ぐことができる。

【手順】

• 一意の原材料テーブルを作成
  • 「クエリ」上の ”Pasta” テーブルを「複製」
  • 原材料名（”Ingredients(,)”）以外の列を「他の列の削除」で削除
  • 「ホーム」→「行の削除」を選択し、「重複の削除」

• 原材料を原材料カテゴリごとに分類
  • 「列の追加」の「条件列」を選択
  • 「条件列の追加」が開く。「列名」に “原材料” の列を、「演算子」に「指定の値に等しい」を、「値」に原材料の列に記載された ”原材料名” を、「出力」に ”カテゴリ名” を入力
    例えば、「列名」”Ingredients(,)”列、「値」”パスタ1.6mm”、「出力」”パスタ”　といった形で入力
  • なお、指定のカテゴリ名がない原材料については、一番下「それ以外の場合」に ”その他” 等を入力
  • 【解説】
    実務ではExcel上でVLOOKUPテーブルを用意する方法もありますが、今回は初心者の方でも理解しやすいよう、あえて「条件列」で作成しています。

• インデックスID列を挿入
  • 「列の追加」の「インデックス列」より「1から」を選択し、インデックスID列を追加

【解説】
このインデックスIDは、後におこなうデータモデリングに置いて、製品値テーブル（F_Pasta）との間で「1対多」のリレーションシップを確立するために必須となる「キー」の役割を果たす。

原材料カテゴリマスタ（D_IngredientCategory）の作成

【目的】

• 一意の原材料カテゴリテーブル（ディメンションテーブル）を作成し、ダッシュボードのスライサーとする

【手順】

• 原材料マスタ（D_Ingredients）を「参照」
• 原材料の種類が一意となるテーブルを作成
  • カテゴリ名（ “CategoryName” ）以外の列は「他の列の削除」で削除
  • 「ホーム」→「行の削除」を選択し、「重複の削除」

• インデックスID列を挿入
  原材料マスタ作成時と同様、リレーションシップ確立の際のキーとして使用する。
  • 「列の追加」→「インデックス列」より「1から」を選択し、インデックス列を追加

【解説】
実務ではExcel側でVLOOKUPを用いて整理しておくと、原材料の種類が増えても管理しやすくなります。

製品マスタ（D_PastaName）の作成

【目的】

• 一意の製品テーブル（ディメンションテーブル）を作成し、ダッシュボードのスライサーとする。これにより、製品単位での集計やスライサーによる絞り込みが正確におこなえる。

【手段】

• 「クエリ」上の ”Pasta” テーブルを「複製」
• 一意の製品テーブルを作成
  • “Recipe” , “Pasta” , “PastaCategory” 以外の列は削除
  • “Recipe” を選択し、「ホーム」→「行の削除」を選択し、「重複の削除」

製品値テーブル（F_Pasta）の作成

【目的】

• 製品（ “Recipe” ）と材料（ “IngreID” ）を結びつける、いわゆる「BOMテーブル」を作成する。これが、製品と原材料の関連性を結びつけるファクトテーブルとなる。

【手順】

• 「クエリ」上の ”Pasta” テーブルを「複製」
• 「ホーム」→「クエリのマージ」を選択 →「マージ」が開く
  • “F_Pasta” と “D_Ingredients” の “Ingredients(,)” 列を選択
  • 「結合の種類」で「左外部」を選択
  • 【解説】
    メインテーブルである “F_Pasta” のすべての行を保持しつつ、対応する “D_Ingredients”（マスタ）の情報を横に結合するため。（ExcelのVLOOKUPのイメージ）。
  • 表示された ”Ingredients(,)” 列の右上の両矢印ボタンを押し展開
  • “CategoryName” と ”IngreID” を選択、「元の列名をプレフィックスとして使用します」のチェックを外す

• “F_Pasta” と “D_IngredientsCategory” を結合
  • 上記と同様の手順で結合し、”IngreCateID” を “F_Pasta” に「マージ」
  • “Recipe” , “IngreID” , “IngredCateID” のみ残し、「他の列の削除」

【解説】

今回各マスタにわざわざインデックス列を追加し、それをキーとして結合しました。Power BIでは、テキスト情報ではなく、ID（数値）情報を用いてリレーションシップを組むことが、集計の正確性と処理速度の向上につながるためこのような方法を採っています。

ⒷPower BIでのモデリング（リレーションの考え方）

作成した各テーブルは、次のようなリレーションシップで結び付けます。

• “D_IngredientCategory” → “D_Ingredients”：1 対 多
• “D_Ingredients” → “F_Pasta”：1 対 多
• “D_PastaName” → “F_Pasta“：1 対 多

これは、Power BIの分析効率を高めるスタンダードなスタースキーマ構造になります。

【解説】
この構造により、各マスタ（ディメンションテーブル）がフィルターや属性情報を提供し、中心となる “F_Pasta”（ファクトテーブル）がすべての関連性データを保持します。これにより、どのマスタからでも、効率的かつ高速にデータ検索や集計ができるようになります。

なお、ビジュアル化の際の視認性を高めるため、不要なモデルや項目は非表示にしておきます

ⒸPower BIでのビジュアル化

「原材料」 → 「製品」 → 「製品に使用する原材料一覧」と絞り込めて直感的に使えるダッシュボードを作成します。

左：材料フィルターの作成

• 「視覚化」→「スライサー」を選択
• 原材料カテゴリ―名（”CategoryName”）, 原材料（”Ingredients(,)”）の順に「フィールド」へ配置
• 原材料が多い場合は上部に「検索」窓を設置すると便利

中央：製品一覧（中央）

• 「視覚化」→「マトリックス」を選択
• 製品カテゴリ―名（ “Pasta Category” ）, 製品名（ “Pasta” ）, 製品番号（ “Recipe” ）の順に「行」へ配置
• 今回は「列」、「値」には何も配置しない

右：製品ごとの材料を表示

• 同様に「マトリックス」を選択
• 製品番号（ “Recipe” ）, 原材料カテゴリ―名（ “CategoryName” ）, 原材料（ “Ingredients(,)” ）の順に「行」へ配置
• 今回は「列」、「値」には何も配置しない

完成イメージ

• 左側のスライサーで原材料を選択
• 中央で原材料一覧を見たい製品を選択

なお、複数選択の場合は、「Ctrl + 左クリック」

まとめ

今回の記事では、

• Power Queryを使ったデータ整形
• IDを用いたデータモデリング
• スライサーにより検索できるビジュアルの作成

といったPower BIの基本的かつ汎用性の高いスキルを扱いました。

こうした仕組みは、製造現場のBOM検索に限らず、

• 【ビジネス】薬品や資材の使用先トレーサビリティ
• 【ビジネス】スキルから適切な人材を検索してプロジェクトへアサイン
• 【家事効率化】冷蔵庫の材料から作れる料理の検索

など、ビジネスはもちろん、様々な場面に応用できます。

ということで、次回は「製品に画像を紐づけて、より直感的に探せる仕組みシステム」に挑戦します。是非お楽しみに！