PowerBIでお小遣い管理をしよう

月一更新…なんのことかな…？

Office 365を契約しているとPowerBIもなんだかそこそこ使える雰囲気があるのですが、まったく使わないのももったいないと思うわけです。
というわけでBIっぽく自分のお金の状態をPowerBIに載せて分析してみましょう。

なおPowerBIの使い方はよくわかってないので、すごいざっくりとした使い方のみです。

とりあえずデータを作ろう

まずはPowerBIに載せるデータを用意しましょう。
今回はExcelを使います。というかExcelじゃないとつらい。

生のデータを直接出すとなんかよろしくない感じがあるので、ダミーデータをサクサクと生成します。
データの要素としては、
・日付
・消費金額
・累積値
・（カテゴリ）
・（項目名）
とします。（）のものは必須ではないです。

おおむねこんな感じのテーブルになります。
PowerBIに載せるためにテーブルにしとく必要があるのでそこだけ注意です。

f:id:fantasticswallow:20170709110330p:plain

支出は+、収入は-で管理します。
Currently Costのところは '=E6 - D7'みたいな式で計算。
Categoryは好きにお使いください（入力が面倒なのでA,B,C...みたいな振り方している）

データについては次のような条件を基に生成しました
・30日に200000くらいお金を手に入れる
・大体毎日ごはんに =INT(RAND() * 1000 + 500)くらいの出費をする
・毎月1日にOffice 365に900円、携帯代として8000円はらう
・4/1に交通費として70000払い、以後は5000円ずつ交通費として使用
・大体3週間おきに =INT(RAND() * 2000 + 2500)くらいの本を買う
・10日おきに2900円払うと無料で10連を回すやつをやる
・月末金曜日に1,2本何か買う
・あとたまに適当な出費

みたいな感じです。私ではありません

生成し終わったらテーブルを日付順にソートします。これでひとまずテーブルは完成です。

日付ごとの残金を作成する

上のやつだけでも行ける気がしますが、日ごとの推移を追うにはちょっと不便なのでもう一つテーブルを作成します。
というのもすべての日にデータを入力してるわけではないのと、複数行に同じ日付がはいるケースがあるため、集計が面倒だからです
なので日付と残金だけ入れたテーブルを作成しましょう。
こっちはPower Queryで簡単に。

Advanced Editorで見るとこんな感じです。

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Date", type datetime}, {"Name", type text}, {"Price", Int64.Type}, {"Currently Costs", Int64.Type}}),
    Custom1 = Table.FromColumns({List.DateTimes(#"Changed Type"{0}[Date],Duration.Days(DateTime.LocalNow() - #"Changed Type"{0}[Date]) + 1, #duration(1,0,0,0))}),
    Custom2 = Table.AddColumn (Custom1, "test", (x) => Table.Last(Table.SelectRows(#"Changed Type", each x[Column1] = [Date]))[Currently Costs]),
    Custom3 = Table.FillDown(Table.ReplaceErrorValues(Custom2, {"test", null}), {"test"}),
    #"Changed Type1" = Table.TransformColumnTypes(Custom3,{{"Column1", type date}})
in
    #"Changed Type1"

Changed Typeまでは自動生成なので（Excelのテーブルから作成するとそこまで入る）、下3行について説明します。
Custom1はテーブルの最初の日付から、現在の日付まで1日おきのリストを作成します。
DateTimesの第2引数にはリストの項目数を指定するのでDuration.Daysで差分の日数を入れている感じです。

Custom2は残金の列を作成しています。特定の日付の中でも最後のデータをその日の最終残金として取得して列を生成します。

f:id:fantasticswallow:20170709113501p:plain

上のような感じで、日付と対応する金額が入るのですが、支出が記録されていない日についてはErrorとなります。
ここは前の日のデータを引き継いでほしいので、次のCustom3で対応します。

Custom3では、Errorをいったんnullに置き換え、その後FillDownでnullを一つ上の行のデータで埋めるようにしています。
これで連続したデータの完成です。

最後にDateTimeになっているのをDateにしてExcelに吐き出して完了です。

PowerBIで読み込もう

というわけでExcelのデータが完成しました。
こだわる場合はもっといれるといいと思いますが、まあ今回はこれで。

作成したExcelファイルはOneDrive上に保存します。
（どこでもいいですが、OneDriveとかに載せると自動更新がかけやすいです）

ここまでやったらようやくPowerBIを開きましょう。
Power BI | 対話型のデータ視覚化 BI ツールあたりからサインインします。

PowerBIの左下の「データの取得」から「ファイル」っぽいところの「取得」を押します

f:id:fantasticswallow:20170709115038p:plain

そしたらどこのファイルを参照するか聞かれるので、先ほど保存したファイルの保存先を選択します。
私の場合はOneDrive - 個人用となります。(適宜選んでください)

接続し終わるとファイル一覧が表示されるので、先ほど作成したExcelファイルを選択します。

f:id:fantasticswallow:20170709115344p:plain

選択するとまっさらな……なんて呼ぶのかわかりませんがシートっぽいものが表示されると思います。
表示されたらデータを選択していきます。
ひとまず残金グラフを作りましょう。

f:id:fantasticswallow:20170709115932p:plain

フィールドからテーブルQuery1のColumn1とtestを選択し(名前は適宜合わせてください)、グラフの種類を折れ線にすると上のような感じでグラフが出ます。
これだけだと手元のExcelで出すのと一緒じゃん！ってなるので予測値を出してみましょう。

f:id:fantasticswallow:20170709144330p:plain

右の視覚化ペインの中のタブを「分析」に切り替え、「予測」のグラフを追加します。
「予測の長さ」には予測したいデータ数を入力します。今回は60日分予測するために60ポイントとしています。
「信頼区間」は指定した確率でこの辺通るだろみたいな補助線を算出するのに使用されます。確率が高いほど上限値、下限値は広く取られます（全部取れば再現率は100%みたいなあれ）。95%とかでいい気はします。
「季節性」は一定の周期でグラフが変動している場合に使用します。今回のケースであればおおよそ30日置きに給料が増えてるので30とか指定します。(自動で勝手に計算してくれるので任せてもいいです。)
(内部的な予測アルゴリズムはSeasonal ETSだと思います。Excelの関数と一緒っぽいのでExcelで予測しても同じようになるはず)

なるほどこれで予測値が見れる！のはいいんですけど、今のところExcelでよくね感を避けれません。
しかしPowerBIだと、Excelでグラフを管理するのと違い、自動で更新してくれるというメリットがあります。
特に予測については自動で範囲を変えるのは結構だるいのでありがたいです。
なので自動更新の設定をしましょう。

いったんレポートを保存してレポート編集画面を抜けます。
「マイワークスペース」の「データセット」を選んで「更新のスケジュール設定」を選びます。
f:id:fantasticswallow:20170709151138p:plain