🚀 STEP 30: Power Queryでの高度なデータ整形

プロレベルのテクニック！データを自在に変形させよう

📋 このステップで学ぶこと

列の分割（区切り記号、文字数）
列の結合（マージ）
ピボット（縦持ち→横持ち）
ピボット解除（横持ち→縦持ち）★最重要
条件列の追加
インデックス列とその他の便利機能

ゴール：どんな形式のデータも、Power BIに最適な形に変換できるようになる

✂️ 1. 列の分割

1つの列に複数の情報が入っている時に分割する

元データでは「山田太郎」のように姓と名が1つの列に入っていたり、「東京都,渋谷区,道玄坂」のように住所がカンマ区切りになっていることがあります。これらを分割して、個別の列にしましょう。

📊 列の分割方法

分割方法	使用場面	例
区切り記号による分割	カンマ、スペース、タブなどで区切られている	「山田太郎」→「山田」「太郎」
文字数による分割	固定長のデータ（郵便番号など）	「1500043」→「150」「0043」
位置による分割	特定の位置で分割したい	商品コードの先頭2文字を抽出

✅ 区切り記号による分割の手順

手順	操作内容
1	分割したい列を選択（例：[氏名]列）
2	変換タブ→「列の分割」→「区切り記号による分割」
3	区切り記号を選択（スペース、カンマ、タブ、セミコロン、カスタム）
4	分割場所を選択（左端、右端、各区切り記号）
5	OK をクリック
6	作成された列名を変更（例：[氏名.1]→[姓]、[氏名.2]→[名]）

💡 区切り記号の種類と選び方

区切り記号	よくある使用場面	元データの例
スペース	氏名（姓と名）	山田太郎
カンマ（,）	住所、CSVデータ	東京都,渋谷区,道玄坂
ハイフン（-）	電話番号、日付	03-1234-5678
スラッシュ（/）	日付、パス	2024/01/15
アンダースコア（_）	ファイル名、コード	sales_2024_01

🔧 分割場所の選び方

分割場所	動作	使用場面
左端の区切り記号	最初の区切りでのみ分割	「山田太郎様」→「山田」「太郎様」
右端の区切り記号	最後の区切りでのみ分割	「山田太郎様」→「山田太郎」「様」
各区切り記号	すべての区切りで分割	「東京都,渋谷区,道玄坂」→3列に

📊 実践例：住所の分割

【元データ】
住所
──────────────────────
東京都,渋谷区,道玄坂1-1-1
大阪府,大阪市,梅田2-2-2
愛知県,名古屋市,栄3-3-3

【分割後】
都道府県    市区町村      番地
──────────  ──────────    ─────────────
東京都      渋谷区        道玄坂1-1-1
大阪府      大阪市        梅田2-2-2
愛知県      名古屋市      栄3-3-3

※ 区切り記号：カンマ、分割場所：各区切り記号

🔗 2. 列の結合（マージ）

複数の列を1つにまとめる

分割の逆で、複数の列を1つの列に結合できます。姓と名を結合して氏名にしたり、郵便番号にハイフンを入れたりする時に使います。

📊 列を結合する2つの方法

方法	操作	元の列の扱い
列のマージ	変換タブ→列のマージ	削除される
カスタム列	列の追加タブ→カスタム列	残る（保持したい時に便利）

✅ 列のマージの手順

手順	操作内容
1	結合したい列を選択（Ctrlキーを押しながら複数選択）
2	変換タブ→「列のマージ」
3	区切り記号を選択（なし、スペース、カンマ、カスタムなど）
4	新しい列名を入力
5	OK をクリック

💡 カスタム列での結合（元の列を残す場合）

目的	カスタム列の式
姓と名を結合	[姓] & [名]
スペースを入れて結合	[姓] & ” ” & [名]
敬称を追加	[姓] & ” ” & [名] & ” 様”
郵便番号にハイフン	[郵便番号前半] & “-” & [郵便番号後半]

※ &記号はテキストを結合する演算子です

📊 実践例：郵便番号のフォーマット

【元データ（分割後）】
郵便番号.1    郵便番号.2
──────────    ──────────
150           0043
160           0022
170           0001

【結合後】
郵便番号
──────────
150-0043
160-0022
170-0001

手順：2列を選択→列のマージ→区切り記号：カスタム「-」

📊 3. ピボット（縦持ち→横持ち）

データの形を変える「ピボット」とは

ピボットは、縦に長いデータ（縦持ち）を横に広いデータ（横持ち）に変換する機能です。レポートや表の見せ方を変えたい時に使います。

📊 縦持ち vs 横持ちの比較

【縦持ちデータ（元）】          【横持ちデータ（ピボット後）】
商品      月    売上              商品      1月   2月   3月
────────  ────  ────              ────────  ────  ────  ────
りんご    1月   100               りんご    100   150   120
りんご    2月   150               みかん    80    90    110
りんご    3月   120
みかん    1月   80                ↑ 横持ちは見やすいが、
みかん    2月   90                  Power BIでの分析には向かない
みかん    3月   110

↑ 縦持ちはPower BIに最適！

📊 縦持ちと横持ちの特徴

形式	特徴	適した用途
縦持ち（長い形式）	1行1データ、列数が少ない	Power BIでの分析、データベース
横持ち（広い形式）	1行に複数データ、列数が多い	Excelレポート、人間が見る表

✅ ピボットの手順

手順	操作内容
1	ピボットしたい列を選択（例：[月]列）
2	変換タブ→「列のピボット」
3	値列を選択（例：[売上]）
4	集計関数を選択（合計、カウント、平均など）
5	OK をクリック

⚠️ ピボット実行前の注意点

注意点	説明
不要な列は事前に削除	ピボット列以外の列は「グループ化キー」になる
重複データに注意	重複があると意図しない集計（合計など）が行われる
列数の確認	ピボット後に列が多くなりすぎないか確認

📈 4. ピボット解除（横持ち→縦持ち）★最重要

Power BIに最適な形にデータを変換する

ピボット解除は最も重要なテクニックです。Excelで作られた横持ちレポートを、Power BIに最適な縦持ちデータに変換します。実務では圧倒的にこちらを使う機会が多いです。

📊 ピボット解除のイメージ

【横持ちデータ（元）】- Excelでよくある形式
店舗      1月   2月   3月   4月   5月   ...
────────  ────  ────  ────  ────  ────
新宿店    100   110   105   120   115
渋谷店    80    85    90    95    100
池袋店    90    95    100   105   110

【縦持ちデータ（ピボット解除後）】- Power BIに最適！
店舗      月    売上
────────  ────  ────
新宿店    1月   100
新宿店    2月   110
新宿店    3月   105
渋谷店    1月   80
渋谷店    2月   85
...

1列に1種類の情報 = Power BIで自在に分析可能！

📊 ピボット解除の2つの方法

方法	操作	使用場面
選択した列をピボット解除	解除したい列を選択→列のピボット解除	解除する列が少ない時
他の列をピボット解除	残したい列を選択→他の列をピボット解除	残す列が少ない時（★よく使う）

✅ ピボット解除の手順（他の列をピボット解除）

手順	操作内容
1	残したい列を選択（例：[店舗]列）※Ctrlで複数選択可
2	変換タブ→「他の列をピボット解除」
3	[属性]列と[値]列が自動で作成される
4	[属性]→[月]に列名を変更
5	[値]→[売上]に列名を変更
6	データ型を確認・修正

💡 ピボット解除後の列名について

自動生成される列名	内容	変更後の例
属性	元の列名が入る	月、年度、カテゴリなど
値	各セルの値が入る	売上、数量、金額など

※ 必ず列名を変更してください！「属性」「値」のままだと、後で何のデータかわからなくなります

🔧 よくあるExcelレポート形式とピボット解除

元のExcel形式	残す列	ピボット解除後
月次売上（1月〜12月が列）	店舗、商品	店舗、商品、月、売上
年度別実績（2020〜2024が列）	部門、担当者	部門、担当者、年度、実績
カテゴリ別売上（A〜Eが列）	地域、店舗	地域、店舗、カテゴリ、売上

🎛️ 5. 条件列の追加

IF文のように条件で値を分ける

他の列の値に基づいて、新しい列を作成できます。ExcelのIF関数と同じような機能で、売上ランクや年代などを自動分類できます。

📊 条件列の活用例

分類目的	条件	結果
売上ランク	100万以上→A、50万以上→B、その他→C	A、B、C
年代	20未満→10代、30未満→20代、…	10代、20代、30代…
顧客分類	購入回数10以上→VIP、5以上→優良、…	VIP、優良、一般
達成状況	達成率100以上→達成、その他→未達成	達成、未達成

✅ 条件列の追加手順

手順	操作内容
1	列の追加タブ→「条件列」
2	新しい列名を入力（例：「売上ランク」）
3	条件を設定（列名、演算子、値、結果）
4	「句の追加」で条件を追加（複数条件の場合）
5	「それ以外」の値を設定
6	OK をクリック

💡 条件列で使える演算子

演算子	意味	例
等しい	= 値	[地域] = 「東京」
等しくない	<> 値	[ステータス] <> 「削除」
次の値より大きい	> 値	[売上] > 1000000
次の値以上	>= 値	[年齢] >= 20
次の値を含む	部分一致	[商品名] contains 「iPhone」
次の値で始まる	先頭一致	[商品コード] begins with 「A」

📊 実践例：売上ランクの作成

【条件列の設定】
新しい列名：売上ランク

IF [売上金額] >= 1000000 THEN 「A」
ELSE IF [売上金額] >= 500000 THEN 「B」
ELSE IF [売上金額] >= 100000 THEN 「C」
ELSE 「D」

【結果】
売上金額      売上ランク
──────────    ──────────
1,500,000     A
800,000       B
300,000       C
50,000        D

🔢 6. インデックス列とその他の便利機能

連番やテキスト抽出など、便利な機能

インデックス列（連番）の追加や、テキストの一部を抽出する機能など、データ整形に役立つ便利な機能を紹介します。

📊 インデックス列の用途

用途	説明
行番号として	各行に1から始まる連番を付ける
元の行の特定	重複削除やソート後に元の行を参照
ユニークIDの作成	IDがないデータに識別子を追加
ソート順の保持	元の並び順を後で復元するため

✅ インデックス列の追加手順

手順	操作内容
1	列の追加タブ→「インデックス列」
2	開始値を選択（「0から」または「1から」）
3	[インデックス]列が自動で追加される

💡 テキストの抽出機能

抽出方法	説明	例
最初の文字	左から指定文字数を抽出	「ABC123」→「ABC」（3文字）
最後の文字	右から指定文字数を抽出	「file.xlsx」→「xlsx」（4文字）
範囲	開始位置と長さを指定	「ABC123DEF」→「123」（4文字目から3文字）
区切り記号の前	区切り文字より前を抽出	「user@example.com」→「user」
区切り記号の後	区切り文字より後を抽出	「user@example.com」→「example.com」

操作：変換タブ→「抽出」→抽出方法を選択

🔧 列の複製（元データを保持して加工）

用途	説明
元データの保持	元の列を残したまま、コピーした列を加工
複数パターンの試行	複製して異なる加工を試す
バックアップ	変換前のデータを念のため保持

操作：列を右クリック→「列の複製」

📝 STEP 30 のまとめ

✅ このステップで学んだこと

列の分割：区切り記号や文字数で1つの列を複数に
列の結合：複数の列を1つにマージ
ピボット：縦持ち→横持ち（レポート用）
ピボット解除：横持ち→縦持ち（★最重要！Power BIに最適化）
条件列：IF文で新しい分類列を作成
インデックス列：連番の追加
テキスト抽出：文字列の一部を取り出す

💡 最重要ポイント

ピボット解除は最も重要なテクニックです！

Excelで作られた横持ちレポートを、Power BIに最適な縦持ちデータに変換できます。

Power BIは縦持ちデータ（正規化されたデータ）で真価を発揮します。

「1つの列には1種類の情報」を心がけましょう！

📝 実践演習

演習 1 基礎

「山田太郎」のような氏名データを、姓と名に分割してください。その後、再度結合して「山田太郎様」という形式にしてください。

【手順】

作業	操作内容
分割	[氏名]列を選択→変換タブ→列の分割→区切り記号：スペース→OK
列名変更	[氏名.1]→[姓]、[氏名.2]→[名]に変更
結合	列の追加タブ→カスタム列→式：[姓] & [名] & “様”→新しい列名：宛名

演習 2 応用

横持ちの月次売上データ（1月、2月、3月の列）を縦持ちに変換してください。その後、売上が100万円以上の月を「好調」、それ以外を「通常」とする条件列を追加してください。

【手順】

作業	操作内容
ピボット解除	[店舗]列を選択→変換タブ→「他の列をピボット解除」
列名変更	[属性]→[月]、[値]→[売上]に変更
データ型変更	[売上]列のデータ型を「整数」または「10進数」に
条件列追加	列の追加タブ→条件列→IF [売上] >= 1000000 THEN 「好調」 ELSE 「通常」

チャレンジ発展

以下の複雑なデータ整形を実施してください：①横持ちデータをピボット解除、②日付列から年・月を抽出、③年齢から年代の条件列を作成、④売上ランクの条件列を作成（3段階）

【総合的なデータ整形手順】

作業	操作内容
①ピボット解除	ID列など固定列を選択→変換タブ→他の列をピボット解除
②日付から抽出	[日付]列を選択→列の追加タブ→日付→年/月（または変換タブ→日付）
③年代の作成	列の追加タブ→条件列→IF [年齢] < 20 THEN 「10代」 ELSE IF [年齢] < 30 THEN 「20代」...
④売上ランク	列の追加タブ→条件列→IF [売上] >= 1000000 THEN 「A」 ELSE IF [売上] >= 500000 THEN 「B」 ELSE 「C」
⑤最終確認	列名・データ型を確認→ステップ名を整理→閉じて適用

❓ よくある質問

Q1: ピボットとピボット解除、どちらを使うことが多いですか？

圧倒的にピボット解除（横→縦）が多いです。

Excelで作られたレポートは横持ちが多いですが、Power BIは縦持ちが得意です。ほとんどの場合、横持ちデータをピボット解除して縦持ちに変換します。

ピボット（縦→横）は、最終的なレポート出力時に使う程度です。

Q2: 列を分割したら元の列が消えました。残す方法は？

分割前に「列の複製」をしてください。

列の分割は元の列を置き換えます。元データを保持したい場合は、事前に列を複製し、複製した方を分割しましょう。

列を右クリック→「列の複製」で簡単にコピーできます。

Q3: 条件列とカスタム列の違いは何ですか？

条件列はGUIで簡単、カスタム列はより柔軟です。

条件列は画面で条件を設定できるので初心者向けです。IF文のような単純な条件分岐に最適です。

カスタム列はM言語で記述するため自由度が高いですが、やや難しいです。まずは条件列から使いましょう。

Q4: ピボット解除で「属性」「値」という名前になります。

これらは必ず列名を変更してください。

ピボット解除後は必ず列名を適切に変更しましょう。「属性」→「月」、「値」→「売上」など、データの内容がわかる名前にすることが重要です。

わかりやすい列名は、後の分析やレポート作成で役立ちます。

Q5: 条件列で複数の条件を組み合わせたい（ANDやOR）

条件列のGUIではAND/ORの指定が限られています。

複雑な条件（例：「東京都かつ売上100万以上」）が必要な場合は、カスタム列でM言語を使うか、2段階に分けて条件列を作成します。

STEP 31のMコードを学ぶと、より複雑な条件も簡単に書けるようになります。

Q6: ピボット解除後にデータがおかしくなりました。

「他の列をピボット解除」で残す列を確認してください。

残したい列を正しく選択していないと、意図しない結果になります。

複数の列を残す場合は、Ctrlキーを押しながら複数列を選択してから「他の列をピボット解除」を実行してください。

Q7: 分割後の列数が予想と違います。

元データに区切り記号の数が異なる行がある可能性があります。

例えば、住所データで「東京都,渋谷区,道玄坂」と「大阪府,大阪市」のように、区切りの数が違うと、列数が合わなくなります。

分割前にデータを確認し、必要に応じて「分割場所」の設定を調整してください。

📝

学習メモ

BIツール入門 - Step 30

📋 過去のメモ一覧 ▼