DATEDIFFもどきを作る｜Power Query

5794 ワード
PowerBI Excel PowerQuery Excel テキストリンク

2020/9/7更新：バージョン2のコードを修正しました。
2020/2/11更新：エラー出しや引数のドロップダウン等に対応したバージョンを追加しました。
公式関数にないものの、Power Query内で処理するなら、経過年月が必要になることもあります。
個人的には月数が出したいので、作ってました。

コード

引数のintervalにはy,m,dの3種類を入れるか、もしくは省略してください。
インターバルを入れない場合は、年・月・日の3種類それぞれのベースでの結果を返す仕様にしてみました。
要らないかな。
※クリックすると開きます。

コード1
(date1 as date,date2 as date,optional interval as text)=>

//check the argument―"interval"
if List.Contains({"y","m","d",null},interval) then

    let 
        Difference =[Year =Date.Year(date2)-Date.Year(date1),
                     Month =Date.Month(date2)-Date.Month(date1),
                     day =Date.Day(date2)-Date.Day(date1)],

        MonthAdjustment= if Difference[day]<0 then -1 else 0,

        Answers =[
                  d = Duration.Days(date2-date1),
                  m = Difference[Year]*12+Difference[Month]+MonthAdjustment,
                  y = Difference[Year]
                      +(if (Difference[Month]+MonthAdjustment) <0 then -1 else 0)
                 ],

        Answer = if interval =null then Answers
                 else Record.Field(Answers,interval)
    in
        Answer

else "intervalはy,m,dのいずれかを指定するか、省略してください。"

メモ

  • 「日」単位以下は、Duration関数群があるので、そちらを使いましょう。
  • githubなどにもいくつか作例が出ていたはずです。
  • DAXの方にはDATEDIFF関数があります。
  • プルダウンで引数を選択させられれば最高ですが、そもそもできるかどうか分からなかったので、ifでエラー回避しました。

追記分

勉強がてら、手を入れました。コードが長いですが、半分くらいは設例等によるものです。
なお、Excelのワークシート同様、想定外の引数が入った場合にはエラー処理するように修正しました。

コード2（強化版）

fx_DateDiff_PQ
let
    BodyOfFunction=    
    (date1 as date,date2 as date, optional interval as text)=>
    let 
        Difference =[Year =Date.Year(date2)-Date.Year(date1),
                     Month =Date.Month(date2)-Date.Month(date1),
                     day =Date.Day(date2)-Date.Day(date1)],
        MonthAdjustment= if Difference[day]<0 then -1 else 0,
        Answers =[
                  d = Duration.Days(date2-date1),
                  m = Difference[Year]*12+Difference[Month]+MonthAdjustment,
                  y = Difference[Year]
                      +(if (Difference[Month]+MonthAdjustment) <0 then -1 else 0)
                 ],
        Answer = if (date1<=date2) and List.Contains({"y","m","d",null},interval) then
                    if interval =null then Answers
                    //2020/9/7修正
                    else  Record.Field(Answers,interval)
                 else ...
    in
        Answer,

    //3番目の引数に係るドロップダウンリストを定義
    IntervalType =type text
                      meta [Documentation.AllowedValues = {"y","m","d"}],

    //関数自体の説明書き,設例
    NewFunctionType =type function(date1 as date,date2 as date, optional interval as IntervalType) as any
                        meta[Documentation.Name ="fx_DateDiff_PQ",
                             Documentation.LongDescription=
                                    "<code>date1</code>から<code>date2</code>の期間に係るデータを取り出します。"
                                  & "<code>interval</code>を省略した場合は、年月日の3種類すべての結果をレコードで返します。",
                             Documentation.Examples=
                                    {
                                         [
                                          Description="2019/12/29から2020/2/29の月数を求めます。日の部分が同じため丁度2か月扱いとなります。",
                                          Code ="fx_DateDiff_PQ(#date(2019,12,29),#date(2020,2,29),""m"")",
                                          Result ="2"
                                         ],
                                         [
                                          Description="2019/12/29から2020/2/29の期間情報を求めます。<code>interval</code>を省略します。",
                                          Code ="fx_DateDiff_PQ(#date(2019,12,29),#date(2020,2,29))",
                                          Result ="[d=62,m=2,y=0]"
                                         ]

                                    }
                            ],
    ReplaceType =Value.ReplaceType(BodyOfFunction,NewFunctionType)
in
    ReplaceType

出来上がりの様子

強化版の関数は、詳細エディタに貼るとこんな感じになります。

注

ドロップダウンはUI上の話だけで、そこに規定してない値も受け入れができてしまいます。
そのため、ドロップダウン処理とは別に、エラーを出すように仕向けています。
エラーメッセージは、また今度ということで。

参考

Writing documentation for custom M-functions, Part1 | THE SELF-SERVICE-BI BLOG

Chris Webb's BI Blog: Specifying Allowed Values, Sample Values and Descriptions For Function Parameters In Power Query–Part 1 Chris Webb's BI Blog