【Power BI入門】家計簿分析レポート#4 ～Power Queryで「分析できるデータ」に整える～
この記事は「Power BI入門 家計簿レポート」シリーズの第4回目で、前回設計したデータモデルをもとに、実際にレポートで使うテーブルの形にしていきます。
元のCSVデータから、設計したテーブルの形にするには、どうすればよいのですかな…？
Power BIの「Power Query」機能を使えば、データの取得・整形操作ができます。
前回は、家計簿レポートの設計図となるデータモデルを、スタースキーマの考え方で設計しました。
今回からはいよいよPower BI Desktopを操作していきます。
- Power Queryで何ができるのか？
- Power Queryでデータ整形する基本的な操作方法
- 整形したデータをPower BIに読み込む際の注意点
Power BIについて詳しく学びたい方は、ぜひパワ実のPower BI入門書もご覧ください！
YouTube動画で見たい方は、こちらからどうぞ！
今回実施するところ
このシリーズでは、Power BIを使ったデータドリブンな意思決定の流れを学ぶことができます。
今回はステップ③、データの準備で、前回②で設計したデータモデルを、Power Queryを使って実際に形にしていきます。
実務のデータ分析でも、この『データを整える』工程は、非常に重要なパートになります。
ここが適切にできていると、あとのレポート作成作業が楽になりますぞ！
Power Queryとは？
役割
最初にPower Queryとは、データの取得・変換・読み込み、いわゆるETL処理を自動化できるツールです。
Power Queryは、Power BI やExcelに標準で組み込まれている機能です。
例えば、以下の例を見てみましょう。
左側の元データは、人間が見るぶんには分かりやすくても、セルが結合されていたり、年が横に並んでいたりして、このままでは分析に使えません。
これをPower Queryで変換すると、右の変換後のデータような『1行1レコード』のきれいなテーブルフォーマットになります。
Power BIなどでデータ分析する場合は、右のようなテーブルフォーマットに変換しておく必要があります。
そしてPower Queryは、この変換の手順がすべて記録されているため、一度変換の操作を作ってしまえば、新しいCSVを取得しても、更新1つで同じ変換が再実行されます。
画面構成
Power Queryエディターの画面構成を説明します。
基本的な4つのエリアだけ覚えておきましょう。
- 操作メニュー：リボンからデータの取得や変換操作を行う
- クエリの一覧：クエリ（データの取得や操作を要求する命令文）の一覧が表示される
- プレビュー：クエリの一覧で選択したクエリを実行した結果のテーブルが表示される
- 適用したステップ：操作メニューで実行したデータ処理の履歴が1ステップずつ記録される
Power Queryでデータの変換を行っても、元データは変更されず、新しくテーブルが作成される形となるため、壊れることはありません。
失敗してもステップを消せばやり直せるので、安心して触ってみてくだされ！
データ整形の流れ
Power Queryでの作業は、大きく3ステップで進めます。
- データを取得する
- 操作メニューで必要な変換をする
- 必要なクエリをテーブルとして出力する
今回はこの3ステップのながれを、家計簿データで一通り体験していきます。
データの取り込み
概要
取り込みの前に、今回Power BIに取り込む3種類のCSVファイルを確認しておきます。
1つめが収支履歴のCSVで、これは、日々の収入・支出が1行ずつ記録された、トランザクションデータで、今回のサンプルは2024年と2025年の2ファイルがあります。
2つめが投資信託、3つめが預金残高のCSVファイルです。
この2つはある時点での資産の状態を写した、スナップショットデータです。
前回の設計を思い出してください。
収支履歴からはfact_Cashflowを、投資信託と預金残高の2つを合体させてfact_Assetsを作っていきます。
この対応関係を頭に置いて、実際に取り込んでいきましょう！
操作
サンプルファイル（以下からダウンロード可能）を以下のように、「Dドライブ＞Power BI」フォルダ下に格納します。
※格納場所は任意ですが、今後の手順は以下のフォルダパスに格納した前提のものとなります
１．Power BI Desktopを起動します。まずは収支履歴のCSVを取り込んでいきます。
２．ホームタブの「データを取得」から「詳細」をクリックします。
３．「フォルダー」を選び、「接続」をクリックします。
４．「結合」＞「データの結合と変換」をクリックします。
５．ファイルの結合画面で、「OK」をクリックします。
６．Power Queryエディター画面が開き、左のクエリ一覧に「収支履歴」が追加されたことを確認します。
７．残り2つのCSVは、Power Queryエディターの「新しいソース」＞「テキスト/CSV」で追加します。
８．まず、「投資信託.csv」を選び、「開く」をクリックします。
９．「OK」をクリックします。
１０．クエリ一覧に「投資信託」が追加されたことを確認します。
１１．ステップ7～9と同様の手順で、「預金残高」クエリも取り込みます。
これで3つのクエリが揃いましたぞ！
データ変換
ここから、Power BIのPower Query機能を使って、データ変換をしていきます。
fact_Cashflow
まずは「fact_Cashflow」を、収支履歴のCSVから作ります。
変換の基本操作は4つだけです。
- 振替列がFALSEのレコードのみをフィルターで残す。
- メモ、振替、IDの列を削除する
- 金額のプラス・マイナスから「収支」という列を追加する
- 各列のデータ型を正しく設定する
１．最初にクエリ一覧から「収支履歴」を選択し、クエリの名前を、右側のクエリの設定ペインから「fact_Cashflow」に変えます。
２．「振替」列の「▼（フィルター）」をクリックし、「」にだけチェックを残して、「OK」をクリックします。
データを整えるこの段階で除外しておけば、このあとレポートを作るときに振替のことを一切気にしなくてよくなりますぞ。
３．次に分析に必要な列だけ残すため、「列の選択」から、残したい列を選びます。
以下の列だけチェックを残し、「OK」をクリックします。
４．金額列のプラス・マイナスから収支の列を作るため、「列の追加」から「条件列」をクリックします。
以下のように、金額が0より小さければ「支出」、それ以外は「収入」という設定をし、「OK」をクリックします。
５．追加した「収支」列をテキスト型に変換します。
他の列のデータ型も適切なことを確認すれば、fact_Cashflowの完成です。
2024年と2025年、2年分の取引が1つのテーブルにまとまりました。
2026年のファイルが追加されても、エクスポートしたCSVをフォルダーに置いて更新ボタンを押すだけで、同じ変換処理が反映できます。
fact_Assets
次に、資産のファクトテーブル「fact_Assets」を作っていきます。
やることは上図のように、投資信託と預金残高、2つのCSVから不要な列を削除して、資産種別の列を追加し、最後に縦に統合して1つのテーブルにします。
１．投資信託のクエリを準備
１．データ型は、CSVデータの取得時に自動で設定されているので、正しく設定されていることを確認すればOKです。
２．「列の選択」をクリックし、必要な列だけを残します。
以下の列にチェックを入れて、「OK」をクリックします。
３．預金データと統合した後に、どの行が投資信託でどの行が預金か区別できるように、「列の追加」から「カスタム列」をクリックしましょう。
４．「資産種別」という列を追加し、値は固定で「”投資信託”」と入力し、「OK」をクリックします。
５．新しく作成された「資産種別」列のデータ型を「テキスト」型に変更します。
これで、「投資信託」クエリの準備は完了です。
２．預金残高のクエリを準備
１．同様の操作で、「預金残高」クエリを選択し、「ホーム」タブの「列の選択」で、必要な列を残します。
以下の列にチェックを残し、「OK」をクリックします。
２．「投資信託」のテーブルと、列名をそろえたいため、「残高（円）」の列名をダブルクリックして、「評価額（円）」に列名を変更します。
３．預金には評価損益がないので、列を作って形を揃えます。「列の追加」から「カスタム列」をクリックします。
以下のように入力し、「OK」をクリックします。
４．もう一つカスタム列を作成し、「資産種別」列に「”預金”」と入力し、「OK」をクリックします。
５．作成した「評価損益」列のデータ型を「整数」にし、「資産種別」列のデータ型を「テキスト」に設定します。
これで、2つのクエリの列構成が同じになりました。
３．2つのクエリを1つに統合
「投資信託」と「預金残高」の2つのクエリを統合します。
１．ホームタブの「クエリの追加」から、「クエリを新規クエリとして追加」の方を選びます。
「預金残高」と「投資信託」の2つを選び、「OK」をクリックします。
２．クエリの名前を「fact_Assets」に変えます。
投資信託と預金のデータが、1つのテーブルに縦に繋がりましたぞ！
dim_Category
続いて、ディメンションテーブル（分析の切り口になるマスタ）を作ります。
まずは、カテゴリマスタ「dim_Category」を作っていきます。
「fact_Cashflow」の中にある大項目と中項目の組み合わせを取り出して、重複を削除して作ります。
さらに『費用種別』という列を追加して、住居費や光熱費のような固定費と、食費のような変動費を区別できるようにします。
この分類があると、レポートで固定費と変動費の割合が一目で見えるようになります。
１．「fact_Cashflow」を右クリックして、「参照」を選びます。
「参照」と「複製」の違い
「複製」は元のクエリのコピーのため、元のクエリを後から直しても、コピー先のクエリには反映されません。
一方「参照」は元のクエリの結果を入力として使うというものです。
今後、元のクエリ（fact_Cashflow）の変換を修正したら、そこから参照しているこのクエリにも自動で反映されます。
２．クエリ名は「dim_Category」に変更します。
３．必要なのは「大項目」と「中項目」だけなので、「Ctrl」キーを押しながら、この2列を選択し、「右クリック」＞「他の列の削除」をします。
４．今は取引の行数分、同じカテゴリが繰り返されているので、2つの列を選択した状態で、「右クリック」＞「重複の削除」で、重複行を削除します。
これでカテゴリの一覧、つまりマスタになりました。
５．「費用種別」列を「列の追加」の「条件列」で追加します。
以下のように入力し、「OK」をクリックします。
ここの分類は、個人の考え方で調整してOKですぞ！
６．追加された「費用種別」列のデータ型を「テキスト」型に変更します。
これで、「dim_Category」ができました。
dim_Account
最後に、口座マスタの「dim_Account」を作ります。
元データは、「fact_Cashflow」と「fact_Assets」の2つになります。
両方のファクトテーブルから「保有金融機関」の列を取得して、重複を削除して作っていきます。
１．「クエリの追加」から「新規クエリとして追加」を選びます。
２．「fact_Cashflow」と「fact_Assets」を選び、「OK」をクリックします。
３．2つのクエリが結合されたので、クエリ名を「dim_Account」に変更します。
クエリの追加は「列名が同じ列」だけを1つに結合しますが、今回は、両方のテーブルに共通で存在する「保有金融機関」の列だけが必要になります。
４．「保有金融機関」列を右クリックし、「他の列の削除」をクリックします。
５．右クリックで「重複の削除」もします。
６．口座の種類で分析できるように、「列の追加」から「条件列」で、「口座種別」の列を追加します。
以下のように、名前の付け方のルールを利用した分類をし、「OK」をクリックします。
７．「口座種別」列のデータ型を「テキスト」に変更します。
これで、『現金や銀行からの支出がいくら、キャッシュレスがいくら』みたいな切り口で家計を見られるようになります。
設計した4つのテーブルが、すべて完成しましたぞ！
変換後のデータを適用
最後に、作成したクエリをPower BI に読み込みます。
１．「投資信託」のクエリを右クリックし、「読み込みを有効にする」のチェックを外します。
読み込みの有効化・無効化
読み込みを無効化したクエリの名前は、日本語では分かりづらいですが、以下のように斜体で表示されます。
これは、「データ更新時に中間クエリとして更新されるが、レポートには読み込まない」状態です。
２．同様の操作で、「預金残高」のクエリも読み込みを無効化します。
３．ホームタブの「閉じて適用」をクリックします。
これでPower Queryエディターを閉じて、変換結果のクエリがPower BI に読み込まれます。
４．読み込みが終わると、右側のデータペインに4つのテーブルが表示されます。
５．「テーブルビュー」で、データの中身を確認してみます。
以下のように、変換後のデータが入っています。
新しくフォルダー内のデータが更新されたら、「更新」ボタンを押すだけで、CSVの再取り込みからPower Queryでの変換まで、全部が自動で再実行されます。
最後に、「ファイル」タブから、名前を付けてPower BIファイル（.pbix）を保存しておきましょう。
お疲れ様でした！
これが、基本的なPower Queryでのデータ変換の流れです。
さいごに
この記事では、Power Queryで3種類のCSVを取り込み、ファクトテーブルと、ディメンションテーブルを作成しました。
今回はPower Queryでよく使う基本的な操作を行ったので、今回の家計簿データ以外のデータ整形にも活用できます。
また、変換処理はすべてステップとして記録されているので、データが新規に追加された場合も、自動でデータ更新ができます。
次回は、今回作成しなかった日付テーブル、dim_DateをDAXで自動生成して、テーブル同士のリレーションシップを設定します。
