131_Power Query之获取钉钉日志自动刷新Power BI报告

let 
fx=(AppKey as text ,AppSecret as text,Month as date,RecodeName as text,NameList as list ) as table=>
let
token=Json.Document( Text.FromBinary(Web.Contents("https://oapi.dingtalk.com/gettoken?appkey="&AppKey&"&appsecret="&AppSecret)))[access_token],

//======================时间戳转北京时间=========================================================================
BeijingTime=(x as number) => 
    let 
    t=DateTime.FromFileTime((11644473600000+x)*10000)
    in
    t,

//======================日期转换为时间戳=========================================================================
UnixTimestampStart=(m as date)=>
    let
        源 = ((Number.From(m)-70*365-19)*86400-8*3600)*1000,
        tf=Text.From(源)
    in
    tf,

//======================提供日期的月末最后一天转换为时间戳=========================================================
UnixTimestampEnd=(m as date)=>
    let
        t=Date.EndOfMonth(m),
        st = ((Number.From(t)-70*365-19)*86400-8*3600)*1000+86399999,
        tf=Text.From(st)
    in
    tf,

//======================根据cursor获取每页recode=================================================================
fxrl=(n as number,name as text)=>
    let
    fx=Json.Document(
                        Web.Contents("https://oapi.dingtalk.com/topapi/report/list?"
                                        ,   [Query=[access_token=token]
                                                ,Content=Text.ToBinary("{'start_time':"&UnixTimestampStart(Month)&",'end_time':"&UnixTimestampEnd(Month)&",'template_name':'"&name&"','cursor':"&Text.From(n)&",'size':20}")
                                            ]
                                    )
                    )[result]
    in
    fx,

//======================每页的recode转换成表=====================================================================
fxrt = (listr as list)=>
    let 
    fx=Table.Combine(List.Transform(listr,(R)=> Table.FromRecords({Record.TransformFields( R,{"contents", each  Record.FromList(Table.FromRecords(_)[value],Table.FromRecords(_)[key])})})))
    in
    fx,

//======================获取corsorlist==========================================================================
//其中100是个参数，表2000条数据，多数需求能满足，大约这个数用条数除以20得到向上取整数字替换100
corsorlist={0} & List.Distinct( List.Transform({1..100},(L)=>List.Accumulate({1..L},0,(x,y)=> 
            if y=1 then 
            Json.Document(Web.Contents("https://oapi.dingtalk.com/topapi/report/list?"
                            ,[Query=[access_token=token]
                            ,Content=Text.ToBinary("{'start_time':"&UnixTimestampStart(Month)&",'end_time':"&UnixTimestampEnd(Month)&",'template_name':'"&RecodeName&"','cursor':"&Text.From(0)&",'size':20}")])
                        )[result][next_cursor]
            else
            Json.Document(Web.Contents("https://oapi.dingtalk.com/topapi/report/list?"
                            , [Query=[access_token=token]
                            ,Content=Text.ToBinary("{'start_time':"&UnixTimestampStart(Month)&",'end_time':"&UnixTimestampEnd(Month)&",'template_name':'"&RecodeName&"','cursor':"&Text.From(x)&",'size':20}")])
                        )[result][next_cursor]))
                        ),

//======================获取每页数据的record组成datalist==========================================================
datalist = List.Transform(corsorlist,each fxrl(_,RecodeName)[data_list]),

//======================datalist转换成表=========================================================================
tables = Table.Buffer(Table.Combine(List.Transform(datalist,each fxrt(_)))),

//======================判断是否为空表，容错机制===================================================================
TF=Table.IsEmpty(tables),//tables=#table({},{})

//======================用namelist展开recode得到表================================================================
展开 = if TF then #table(NameList,{}) else Table.ExpandRecordColumn(tables, "contents", NameList),

//======================删除其他列得到目标列=======================================================================
删除 = Table.SelectColumns(展开,NameList)

in
    删除,
metadata=[
Documentation.Name="钉钉日志数据获取函数：Fxdingding",
Documentation.LongDescription="AppKey：登录钉钉开放平台获得（需要组织管理员权限）；AppSecret：登录钉钉开放平台获得（需要组织管理员权限）；Month：表示要获取数据的月份的1号填写为：#date(2019,1,1)；RecordName：要获取的日志标题名称；NameList：日志的标题名称，使用list形式。"
]

in
Value.ReplaceType(fx,Value.Type(fx) meta metadata)