使用 @ 与元数据写一个来查找指定月份中指定星期几的对应日期,并且支持月初查找与月底查找,以及查找第几个

• M函数如下:

let
    fx=(x as date,y as text,z as number,optional l as number) as date=>
                let   
                A=if l is null then if z<0 then {Date.EndOfMonth(x),-1} else {Date.StartOfMonth(x),1} else {Date.AddDays(Date.StartOfMonth(x),l*7-1),-1},
                fx2=(x1 as date)=>if Date.DayOfWeekName(x1)=y then x1 else @fx2(Date.AddDays(x1,A{1}))
                in
                    fx2(A{0})
in
    fx

第一参数随便选中指定月份中随便一个日期即可,第二参数输入查找星期几,第三参数控制从月初开始还是月底开始,小于0为月底开始,否则从月初开始,第四可选参数控制从月初查找第几个指定星期,如果超过当月会自动跳转到下一个月

• 经过元数据规范化后的M函数:

let
    fx=(x as date,y as text,z as number,optional l as number) as date=>
                let   
                A=if l is null then if z<0 then {Date.EndOfMonth(x),-1} else {Date.StartOfMonth(x),1} else {Date.AddDays(Date.StartOfMonth(x),l*7-1),-1},
                fx2=(x1 as date)=>if Date.DayOfWeekName(x1)=y then x1 else @fx2(Date.AddDays(x1,A{1}))
                in
                    fx2(A{0}),
    元数据= type function (x as (type date meta [Documentation.SampleValues={"请选择月份,随便一个日期"}]),
                            y as (type text meta [Documentation.AllowedValues={"星期一","星期二","星期三","星期四","星期五","星期六","星期日"}]),
                            z as (type number meta [Documentation.SampleValues={"小于0为从月底开始,反之从月初开始"}]),
                            optional l as (type number meta [Documentation.SampleValues={"选择第几次出现"}])) as date meta 
            [Documentation.Name="这是一个普通是自定义函数名称",
             Documentation.LongDescription="O(∩_∩)O哈哈~"]
in
    Value.ReplaceType(fx,元数据)

有兴趣的可以看看

Ntt.Docomo