Power BI での取り込みが不安定だったり、更新に時間がかかる多くの原因は、Excel 側のデータ整形が曖昧なことにあります。本記事は「表形式」「ヘッダーの扱い」「結合セルの回避」を軸に、Power Query による自動化や 100 万行超の運用、日付・ID の型設計までを網羅。今日から迷わず使える実践的なベストプラクティスをまとめました。
質問概要
Power BI にインポートする前段階として、Excel の売上データをどのような構造・書式で準備しておくべきか。具体的には「表形式にすべきか」「ヘッダーの扱い」「結合セルを避けるか」など、基本的な整形ルールを知りたい——というニーズに対し、本記事は実務で事故を起こしがちなポイントを抑えたルールと、すぐに再現できる変換手順を提示します。
結論：Power BI 取り込み成功率と更新性を最大化する Excel 整形ルール
まずは要点を一覧化します。以下のルールを守るだけで、読み込みエラー・再読み込み時の範囲ズレ・型不一致による NULL 化・パフォーマンス劣化の大半を回避できます。
| 目的 | ベストプラクティス | 補足・理由 |
|---|---|---|
| 表構造を明確にする | Excel テーブル機能（挿入 > テーブル）を必ず使用 | データ追加時に自動拡張され、Power BI で再読み込みしても範囲がずれない。名前付きテーブル（例：SalesTable）で参照の安定性が増す。 |
| 列定義を一意にする | 1 行目を“ヘッダー行”に固定し、重複・空白の見出しをなくす | 列名がユニークでないと Power BI の自動型推論や DAX 参照で衝突する（Sales[金額] 等の参照が不明確になる）。 |
| 読み取りを阻害しない | 結合セル（マージセル）は禁止 | 結合セルを含むと列・行の粒度が壊れ、Power BI が 1 つの値として解釈できず、取り込み失敗や欠損の原因となる。 |
| 集計値は分離 | 小計・合計をデータ行に含めない | 集計は Power BI 側（DAX／ビジュアル）で行う。小計・合計行は“データ”ではなく“レイアウト”。取り込み後の二重集計を招く。 |
| データ型を揃える | 列ごとに単一のデータ型（数値／テキスト／日付）を維持 | 型が混在すると自動型変換が失敗し、NULL・エラー・文字列扱いの数値化など不具合の温床になる。 |
| 空白行・列を排除 | 不要な空行・空列を削除 | 空行・空列があると別テーブル扱いになったり、読み込み範囲が分断され性能低下。可視セルのコピー運用もミスを誘発。 |
| フラット構造 | 階層は列として展開し、1 行＝1 レコードを徹底 | クロス集計やネストを避け、必要ならアンピボット（列を属性化）。事実テーブルは長い縦持ちにする。 |
| 日付列の正規化 | Excel の“日付”型で保持（文字列不可） | Power BI のタイムインテリジェンス（YTD、QoQ 等）が活用可能。ISO 形式表示（yyyy-mm-dd）が混乱を防止。 |
| 命名規則 | 列名・シート名は 売上_金額 のようにアンダースコア区切り | 日本語名は可だが、スペースや記号多用は避けると DAX／Power Query での参照が簡潔に。 |
| 前処理自動化 | Power Query（データ > クエリと接続）で変換を記録 | 抽出・変換の再現性が確保され、ワンクリック更新が可能。人手のコピペを排除。 |
| メタデータ管理 | 単位（円／ドル）や粒度（日次／月次）を別シートで明示 | モデル設計時の混乱を防ぎ、DAX の集計基準や通貨変換の前提を共有できる。 |
| 大容量対応 | 100 万行超は月次ファイルに分割し、Power BI でフォルダー取り込み | パーティション的な読み込みでパフォーマンスを向上。差分更新の設計が容易。 |
「正しい表」とは何か：NG レイアウトからの変換例
悪い例（クロス集計＋結合セル）
見た目は整っていても、機械が読み取れない典型例です。
| 商品 | 2025年 | 2025年 | 2025年 |
|---|---|---|---|
| 1月 | 2月 | 3月 | |
| りんご | 120 | 98 | 150 |
| みかん | 80 | 110 | 95 |
| 合計 653 | |||
良い例（1 行 = 1 レコードの縦持ち）
クロス集計を「年月」属性にアンピボットし、集計行は排除します。
| 商品名 | 年 | 月 | 数量 |
|---|---|---|---|
| りんご | 2025 | 1 | 120 |
| りんご | 2025 | 2 | 98 |
| りんご | 2025 | 3 | 150 |
| みかん | 2025 | 1 | 80 |
| みかん | 2025 | 2 | 110 |
| みかん | 2025 | 3 | 95 |
Power Query でのアンピボット手順（Excel 側）
- 表を選択 > データ > テーブルまたは範囲から取得。
- 商品列（属性列）を残したまま、月の列をすべて選択し 列のピボット解除。
- 属性列を「年月」や「月」へリネーム、値列を「数量」にリネーム。
- 必要なら「年月」を分割し、年・月を数値型に変更。カラム型を必ず設定。
- 合計行が混ざっている場合は フィルター で除去。
- クエリ名を
Sales_Baseのように命名して読み込み。
let
ソース = Excel.CurrentWorkbook(){[Name="SalesTable"]}[Content],
型設定 = Table.TransformColumnTypes(ソース,{{"商品名", type text},{"2025-01", Int64.Type},{"2025-02", Int64.Type},{"2025-03", Int64.Type}}),
アンピボット = Table.UnpivotOtherColumns(型設定, {"商品名"}, "年月", "数量"),
年月分割 = Table.SplitColumn(アンピボット, "年月", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"年","月"}),
型再設定 = Table.TransformColumnTypes(年月分割,{{"年", Int64.Type},{"月", Int64.Type},{"数量", Int64.Type}}),
日付列追加 = Table.AddColumn(型再設定, "日付", each #date([年],[月],1), type date),
不要列削除 = Table.RemoveColumns(日付列追加,{"年","月"})
in
不要列削除
ポイントは「型設定を早い段階で行う」こと。Power Query はステップごとに型が変わり得るため、早めの TransformColumnTypes で不測の文字列化を防ぎます。
ヘッダーとフィールド設計：最初に決める 7 つのルール
- ヘッダーは 1 行のみ。説明文や注釈は別シート（
Readme）に記載。 - 列名は一意。
売上と売上の重複や、空ヘッダー（Column1）を作らない。 - 記号・半角スペースを極力回避。
売上 金額（円）ではなく売上_金額、単位は別列またはメタデータで。 - ID は文字列型。受注番号・顧客コード・郵便番号・JAN など先頭ゼロが落ちるものは 文字列として保持。
- 日付と時刻は分離。時刻が必要な場合は
日付（date）と時刻（time/datetime）に分ける。 - 通貨は通貨列＋通貨コード。
金額と通貨（例：JPY, USD）を別列にし、記号付き文字列は避ける。 - カテゴリ列はコード＋名称。
商品カテゴリコードと商品カテゴリ名のように揃えておくとディメンション化が容易。
| フィールド | Excel の型/表示 | 例 | 備考 |
|---|---|---|---|
| 受注日 | 日付型（表示は yyyy-mm-dd） | 2025-03-01 | 文字列日付は不可。Import 後のタイムインテリジェンスが安定。 |
| 受注時刻 | 時刻/日時型 | 13:45:20 | 分解して格納すると時間帯分析が容易。 |
| 受注番号 | 文字列 | 0001234567 | 先頭ゼロを保つ。数値型にしない。 |
| 数量 | 数値（整数） | 12 | 小数不要なら整数型。桁区切りや単位文字は付けない。 |
| 金額 | 数値（固定小数点） | 123456.78 | 「￥」「,」は表示形式で。データ自体は素の数値。 |
| 通貨 | 文字列 | JPY | 通貨記号の混在を避ける。 |
日付・時刻・数値・文字列：ミスりやすい変換と実務テクニック
日付の文字列化を回避する
Excel で 2025/3/1 をテキスト入力すると、Power BI で 文字列扱いになりやすく、モデルのカレンダーと連携できません。必ず日付型として保持し、見た目だけを yyyy-mm-dd 表示にします。
文字列日付を正規化する式（どうしても文字列しかない場合）
ソースに yyyymm 形式があるとき：=DATE(LEFT(A2,4), MID(A2,5,2), 1) で “1 日”の日付に変換し、セルの表示形式で月表示に整えます。Power Query を使う場合は「列の分割 → 型設定 → #date」で生成。
先頭ゼロが落ちる ID を守る
- 事前に列の書式を「文字列」に設定する。
- 既存数値をゼロ詰め文字列にする：
=TEXT(A2,"0000000000000")（桁数は業務の最大長に合わせる）。
全角・半角の混在を解消
製品名や顧客名は全角・半角が混じると別カテゴリとして扱われます。Power Query で Transform > 形式 > 小文字/大文字/トリム/クリーン を適用し、先頭末尾スペースも除去します。
マイナス・カッコ付き金額に注意
(1,234) など会計表示をデータ化していると文字列化します。セルは数値のまま、表示形式だけで会計表示にし、取り込み先では DAX でプラス/マイナス処理を行いましょう。
空白・NULL・エラー値の扱い指針
- 空文字（””）と NULL は区別。意味のない欠損は NULL、意図的な“空”は空文字に。Power BI のフィルター動作が異なるため。
- エラーは取り込み前に握り潰さない。Power Query の「エラーの置換」は有効だが、エラー件数を別クエリでカウント・ログ化すると品質が担保される。
- NULL をゼロに置換するのは「数量・金額の真正 NULL が業務上あり得ない」場合だけ。安易な 0 埋めは平均・率を歪める。
let
ソース = Excel.CurrentWorkbook(){[Name="SalesTable"]}[Content],
型設定 = Table.TransformColumnTypes(ソース,{{"数量", Int64.Type},{"金額", type number}}),
エラー件数 = Table.RowCount(Table.SelectRowsWithErrors(型設定)),
エラー置換 = Table.ReplaceErrorValues(型設定, {{"数量", 0}, {"金額", 0.0}})
in
エラー置換
上記のように、実運用では エラー件数 を別途メモしておくと、後続で「品質ダッシュボード」を作る際に役立ちます。
100 万行を超えるときの運用：ファイル分割とフォルダー取り込み
Excel の 1 シートは 1,048,576 行が上限です。長期の売上明細は 年月単位でファイル分割し、Power BI でフォルダー取り込みを行うと更新が高速・安定になります。
ファイル命名規則（例）
Sales_2024-01.xlsx
Sales_2024-02.xlsx
...
Sales_2025-03.xlsx
共通レイアウト
- テーブル名は全ファイルで同一（例：
SalesTable）。 - ヘッダー・列順・型は完全一致。列の増減は別ブランチで検証後に反映。
Power Query（Excel/Power BI 共通）の基本フロー
- データ > フォルダーから > 取り込み。
- サンプルファイルで変換（型設定・不要列削除・列名統一）。
- 関数化された変換を全ファイルに適用。
- 必要に応じて 年・月 パラメータで差分読み込みの条件を付与。
let
ソース = Folder.Files("C:\Data\Sales"),
Excelのみ = Table.SelectRows(ソース, each Text.EndsWith([Extension], ".xlsx")),
変換適用 = Table.AddColumn(Excelのみ, "変換", each Excel.Workbook([Content], true)),
展開 = Table.ExpandTableColumn(変換適用, "変換", {"Data", "Item"}, {"Data","Item"}),
Salesのみ = Table.SelectRows(展開, each [Item] = "SalesTable"),
結合 = Table.Combine(Salesのみ[Data]),
型設定 = Table.TransformColumnTypes(結合,{{"受注日", type date},{"受注番号", type text},{"数量", Int64.Type},{"金額", type number}})
in
型設定
パフォーマンス最適化チェックリスト（Excel 側でできること）
- 列は必要最小限：Power BI に不要な列は Excel 側で削除 or Power Query で除外。
- 高カーディナリティの列は分解：長いフリーテキスト（備考など）は別テーブルに退避し、Fact からはキー参照のみに。
- 重複行の事前除去：Excel の データ > 重複の削除 もしくは PQ の 行の削除 > 重複の削除 を使用。
- 型を早く付与：Power Query ではインポート直後に型設定。遅いステップでの型変換はコスト増。
- 不要な数式の除去：数式は計算済み値に置換し、取り込み時の再計算を避ける。
入力品質を底上げする Excel の機能設定
- データの入力規則：カテゴリはリストで選択、数量は 0 以上の整数、日付は範囲指定。
- 構造化参照：テーブルの列参照（例：
=[@数量]*[@単価]）で範囲ズレ防止。 - シート保護＋ヘッダー固定：見出しや列の並び替え事故を防ぐ。
- 標準化された表示形式：日付は
yyyy-mm-dd、時間は 24 時間表記、通貨記号は 表示で付与。
メタデータ（データ辞書）をシートで持つ
モデル設計の混乱は「列の意味が共有されていない」ことが原因です。Dictionary シートで最小限のメタデータを管理しましょう。
| 項目名 | 物理名 | 型 | 単位 | 定義・備考 |
|---|---|---|---|---|
| 受注日 | order_date | date | – | 受注が確定した日。タイムゾーンは JST。 |
| 金額 | amount | number | JPY | 税抜金額。四捨五入はしない。 |
| 数量 | qty | int | pcs | 最小 1。欠品は 0。 |
前処理を自動化する Power Query 設計パターン
推奨クエリ構成
- 0_Source：ソースファイル/フォルダーの参照のみ。
- 1_Transform：型設定・列名統一・アンピボットなどの標準変換。
- 2_BusinessRules：業務ルール（NULL 置換、範囲フィルター、ラベル標準化）。
- 3_Output：Power BI へ読み込む最終テーブル。
クエリ名に数字プレフィックスを付けると、依存関係が視覚的に分かりやすくなります。
よく使う M コード断片
// 前後スペースと連続スペースの正規化
= Table.TransformColumns(#"前のステップ", {{"商品名", each Text.Trim(Text.Clean(_)), type text}})
// 文字列 "N/A" を null に
= Table.ReplaceValue(#"前のステップ","N/A", null, Replacer.ReplaceValue, {"備考"})
// 値のマッピング（コード→名称）
= Table.ReplaceValue(#"前のステップ", each [コード], each if [コード]="A" then "国内" else if [コード]="B" then "海外" else "その他", Replacer.ReplaceValue, {"区分"}) 現場で役立つ「貼って使える」チェックリスト
- テーブル化している（名前付き・範囲自動拡張）。
- ヘッダーは 1 行・一意・スペース/記号最小。
- 結合セル・セル内改行なし。空行・空列なし。
- 1 行 = 1 レコード。クロス集計はアンピボット済み。
- 日付は 日付型、ID は 文字列型、数量/金額は 数値型。
- 小計・合計行は存在しない。
- 通貨コード/単位は列またはメタで明示。
- Power Query で変換ステップを記録、型設定は早期に。
- 重複行チェック済み。不要列は削除。
- 100 万行超はフォルダー取り込みの設計。
トラブルシューティング早見表
| 症状 | 原因 | 解決策 |
|---|---|---|
| 日付で期間スライサーが効かない | 日付が文字列 | Excel 側で日付型に変更、または PQ で Date.FromText／#date で生成。 |
| 受注番号の先頭ゼロが消える | 数値型で取り込み | 文字列型に変更し、TEXT 関数でゼロ詰め。PQ では type text に。 |
| 取り込み時に列がずれる | 見出しの重複/空ヘッダー、結合セル | ヘッダーを一意にし、結合セルを解除。テーブル化で範囲を固定。 |
| 金額が文字列になる | 「￥」「,」「( )」をデータに含めた | 記号は表示形式に。データは素の数値に統一。 |
| 月次ファイルの結合で型が不安定 | 各ファイルで列型/順序が微妙に違う | テンプレートを固定。サンプルファイルで型を設定し、全適用。 |
FAQ：よくある疑問へのショートアンサー
Q. ヘッダーが 2 行ある帳票はどうする？
A. 上段は説明行として除去し、下段のみをヘッダーにします。Power Query の「最初の行をヘッダーとして使用」で適用後、不要行を削除。
Q. Excel の合計行を残したいが？
A. データとしては削除。合計は Power BI のメジャーで表現（例：Total Amount = SUM(Sales[金額])）。
Q. CSV から来る文字コード問題は？
A. Excel へ直接開かず、Power Query の「テキスト/CSV から」で取り込み、区切り・エンコーディングを指定。Excel に貼る場合も、最終的にはテーブルに落とし込む。
Q. タイムゾーンや夏時間は？
A. 取引日時は UTC か業務タイムゾーンに正規化し、別列でタイムゾーンを管理。人が見るレポート側でローカライズ。
Q. 値上げ・通貨換算の履歴は？
A. マスタ（為替・単価）を別テーブルで日付粒度管理（開始日・終了日）。Power BI で期間参照のリレーションを作る前提で、Excel 側では行粒度を揃える。
まとめ：Excel の整形は「見た目」ではなく「機械可読性」
Power BI での成功は、Excel データが テーブル化・一意ヘッダー・非結合・1 行 1 レコード・正しい型 の 5 条件を満たしているかでほぼ決まります。さらに Power Query で変換ステップを記録し、メタデータで前提を共有すれば、更新運用は劇的に楽になります。今日から帳票の「見た目」を整えるのではなく、分析のための「構造」を整える——それが最短の近道です。
コメント