時間を計算で使用する時につまずいたポイントはUnixTime(エポック時間)に変換することと、メジャー式で出来たテーブルから最大値MAXと最小値MINを除外する方法
これがわかると時間ごとの温度の移動平均が簡単に求めることができました。
はじめに
今回紹介する方法は、数多ある手法の中の一つですが、同じような悩みを持った方の参考にしていただけると、とても嬉しいのです。
また、当方はブログ初心者です。色々見苦しい点があると思いますが、ご了承ください。
CSVデータの読み込み
今回は、日付と時刻のカラムと温度データが入ったCSVデータを使用しました。
温度は約2分ごとに更新されています。
column1は日時、column3とcolumn4は温度データ。日時は型と表示に注意します。
UnixTime(エポック時間)の列を作成
いきなり最初のつまずいたポイントがここです。
PowerBiは日付計算は簡単ですか、時間を計算するのはグッと難易度が高くなります。
今回の最大の目的である「移動平均」を求めるには複数の行が必要になるのですが、それをフィルターかける時に時間を使用します。
行番号(ROW)の列を追加してn数の行を指定する方法もありますが、センサーやマイコンのエラーで一定時間温度データが入らない場合は、離れた時間のデータも計算してしまいます。
また、2021/11/1 13:01:01を20211101130101などに変換した列を作成して計算に使用すると、時間は60進法なので、不具合が生じます。そんなことしませんよね！（私は少しやってみるまで気づきませんでした）
色々調べるうちに、こういう計算にはUnixTime(エポック時間)を使用すると良いという情報にたどり着きました。しかし、変換するDAX式がわかりません。調べても調べてもヒットせず、あきらめかけた時・・・ある英語の記事を発見しました。そうです！私は英語が苦手なので完全に無視していました！
ついに見つけたので、以下に紹介します。
Power Queryエディターでカスタム列の追加をします。ここでいうColumn1は日時の列です。
画像のように式を記述します。
Duration.TotalSeconds([Column1]-#datetime(1970, 1, 1, 0, 0, 0)) すると以下の画像のように、10桁の数字が入ったカスタム列ができました。
要は、1970年1月1日午前0時0分0秒からの経過秒数を求めたことになります。
一番右の列にカスタムと書かれた列ができました。
一安心です。
しかし！見落としがちですが、できた列の型が「文字」です。以下の画像のように「整数」の型に変更しましょう。
わかりづらいですが、カスタムという見出しの左側にあるアイコンが変わりました。
列の型を変える方法については、簡単ですね！列の見出しにあるアイコンをクリックしてください。
ついでに、以下のように見出しを「UnixTime」などの名前に変更し、ほかの列の見出しもわかりやすいように変更してしまいましょう。
ここまでUnixTime(エポック時間)が追加できましたので、Power Queryエディターを保存して閉じます。
温度グラフを表示する
とりあえず温度1のデータを折れ線グラフで表示しました。
この辺の手法は基本ですので、割愛させていただきます。
時間ごとの温度変化の推移が確認できます。
しかし、データの揺れというか、ノイズのようなものがたまに入りなんだか見づらいですね。
移動平均
それでは移動平均で表示してみます。グラフを平準化させる目的です。
まずメジャーで計算式を入力します。
以下のようにコードを打ちます。
温度1 移動平均 =
VAR _currentData = MAX('tempData'[UnixTime])
VAR _duration = 600 //範囲(秒)
VAR _period = FILTER(ALL('tempData'), AND([UnixTime] >= _currentData - _duration, [UnixTime] <= _currentData))
VAR _movingAvetage =
CALCULATE(
SUMX('tempData','tempData'[温度1]) / DISTINCTCOUNT(tempData[UnixTime]),
_period
)
RETURN _movingAvetage 
これで移動平均のグラフができました。
グラフが少し平準化されました。
でも、まだノイズの名残りがありますね。
VAR _duration = 600 この値を大きくすると平均する母数が増えるのでもっと平準化されますが、
あまり増やしすぎると古いデータの影響を受けやすくなるので、注意してください。
区間の最大値・最小値を除外する
そこで最大値と最小値を除外することで、ノイズを除去することにしました。
※これが、統計学上正しいかは知りません。あくまでも参考にということで。
以下のように新しいメジャーを作ります。
温度1 移動平均 除くMAX MIN =
VAR _currentData = MAX('tempData'[UnixTime])
VAR _duration = 600 //範囲(秒)
VAR _period = FILTER(ALL('tempData'), AND([UnixTime] >= _currentData - _duration, [UnixTime] <= _currentData))
VAR _periodMax = MAXX(_period,'tempData'[温度1])
VAR _periodMin = MINX(_period,'tempData'[温度1])
VAR _periodMid = FILTER(_period, [温度1] <> _periodMax && [温度1] <> _periodMin)
VAR _movingAvetage =
CALCULATE(
SUMX('tempData','tempData'[温度1]) / DISTINCTCOUNT(tempData[UnixTime]),
_periodMid
)
RETURN _movingAvetage
平均で計算する範囲の中から最大値と最小値を除外するコードです。
実はここでもハマりました！
MAXX・MINXを使わず（よく知らなかった）ずっとMAX・MINを使用してしまい、最大値と最小値を求めることができませんでした。
するとどうでしょう！
3段目のグラフのようにデータの揺れというかノイズというかそう言った物が除外され、
平準化されただいぶ見やすいデータになりました。
4段目のように3つ重ねると変化の度合いがわかり易いですね！
まとめ
どうでしたでしょうか？
だいぶ駆け足で説明しましたが、おわかりいただけましたでしょうか。
つまずきポイントがいくつかあって、その度に手が止まってしまっている方を一人でも救いたくて記事にしました。
Power BIは素晴らしいツールです。ぜひマスターして、スキルアップしていきましょう！
それではまた！
コメント