141_Power Query之获取钉钉审批流自动刷新Power BI报告

2020年5月29日22:37:39 评论 1,335 3219字阅读10分43秒

焦棚子的文章目录

请在文末下载附件

 

一、背景

钉钉办公给很多企业带来了很多方便,比如审批流线上化,通用化。线上化填写后,数据自动获取又是一个硬伤了,虽然数据可以下载,但我们要自动刷新数据怎么办呢?

结合power bi做一个自动刷新,和Power BI打通,美滋滋

推荐阅读:Power Query之获取钉钉日志自动刷新Power BI报告

二、数据结构

为了方便演示,新建一个审批流模板。

标题:收款审批

字段:客户姓名、收款方式、收款金额(元)、备注

141_Power Query之获取钉钉审批流自动刷新Power BI报告

测试审批流数据

141_Power Query之获取钉钉审批流自动刷新Power BI报告

审批后结果
141_Power Query之获取钉钉审批流自动刷新Power BI报告

三、上M

1、自定义函数Fxdingding_process

看到这一串不要慌,直接复制到自己的pq中保存为Fxdingding_process即可。

let 
fx=(AppKey as text ,AppSecret as text,Month as date,ProcessCode as text ) as table  =>
let
token= Json.Document( Text.FromBinary(Web.Contents("https://oapi.dingtalk.com/gettoken?appkey="&AppKey&"&appsecret="&AppSecret)))[access_token],
UnixTimestampStart=Text.From(((Number.From(Month)-70*365-19)*86400-8*3600)*1000),
UnixTimestampEnd=Text.From(((Number.From(Date.EndOfMonth(Month))-70*365-19)*86400-8*3600)*1000+86399999),

fxrl=(n as number,process_code as text)=>
    let
    fx=Json.Document(
                        Web.Contents("https://oapi.dingtalk.com/topapi/processinstance/listids?"
                                        ,   [Query=[access_token=token]
                                                ,Content=Text.ToBinary("{'start_time':"&UnixTimestampStart&",'end_time':"&UnixTimestampEnd&",'process_code':'"&process_code&"','cursor':"&Text.From(n)&",'size':20}")
                                            ]
                                    )
                    )[result][list]
    in
    fx,

fxrt = (process_instance_id as text)=>
    let
    Rl=Json.Document(
                        Web.Contents("https://oapi.dingtalk.com/topapi/processinstance/get?"
                                        ,   [Query=[access_token=token]
                                                ,Content=Text.ToBinary("{'process_instance_id':'"&process_instance_id&"'}")
                                            ]
                                    )
                    ),
    records_spl=
                if  
                    List.Count(Rl[process_instance][operation_records]) >1
                then
                    Rl[process_instance]
                    [
                        [business_id],
                        [create_time],
                        [originator_dept_name],
                        [result],
                        [originator_userid]
                    ]&
                    Record.FromList( 
                                    List.Transform(Rl[process_instance][form_component_values],
                                                    (R)=> R[value]),
                                    List.Transform(Rl[process_instance][form_component_values],
                                                    (R)=> R[name])
                                    
                                    )
                    &Rl[process_instance][operation_records]{1}[[remark],[date]]
                    
                else 
                    Rl[process_instance]
                    [
                        [business_id],
                        [create_time],
                        [originator_dept_name],
                        [result],
                        [originator_userid]
                    ]&
                    Record.FromList( 
                                    List.Transform(Rl[process_instance][form_component_values],
                                                    (R)=> R[value]),
                                    List.Transform(Rl[process_instance][form_component_values],
                                                    (R)=> R[name])
                                    
                                    )             
                    &[remark=null,date=null]                                             
    in
    records_spl,

N=  List.Count(List.Generate(()=>1,each List.Count( fxrl(_, ProcessCode))>0 ,each _+1)),
process_instance_id_list = List.Combine(List.Transform({1..N},each fxrl(_,ProcessCode))),
result= Table.FromRecords( List.Transform(process_instance_id_list,each fxrt(_)) )

in
    result,
    
metadata=[
Documentation.Name="钉钉审批数据获取函数:Fxdingding_process",
Documentation.LongDescription="AppKey:登录钉钉开放平台获得(需要组织管理员权限);AppSecret:登录钉钉开放平台获得(需要组织管理员权限);Month:表示要获取数据的月份的1号填写为:#date(2019,1,1);ProcessCode:表示审批模板的唯一编码,在审批模板编辑页的url中查看。"
]

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

 

2、自定义函数Fxdingding_process的说明

function(AppKey as text ,AppSecret as text,Month as date,ProcessCode as text) as table

AppKey:登录钉钉开放平台获得(需要组织管理员权限);

AppSecret:登录钉钉开放平台获得(需要组织管理员权限);

Month:表示要获取数据的月份的1号填写为:#date(2019,1,1),为什么要设置这个参数,因为钉钉api获取数据是由日期限制的,好像是180天,所以数据就按照每月获取的;

ProcessCode:表示审批模板的唯一编码,在审批模板编辑页的url中查看。

141_Power Query之获取钉钉审批流自动刷新Power BI报告

3、调用函数得到结果

141_Power Query之获取钉钉审批流自动刷新Power BI报告
4、以上,对于pq了解多一些人操作起来没有问题,接下来再把函数和字段名称管理一起封装,让更多对pq不是很了解的人直接拿来就用(详细见附件)

步骤1:配置《01名称管理》Excel文件表名、ID、表ID、原始名称、统一名称;

141_Power Query之获取钉钉审批流自动刷新Power BI报告

步骤2:配置好Power BI文件4个参数;

141_Power Query之获取钉钉审批流自动刷新Power BI报告

步骤3:直接一个简单的函数Fxtable得到结果,是不是非常简单。

141_Power Query之获取钉钉审批流自动刷新Power BI报告

 

四、总结

1、直接调用自定函数fxdingding输入四个参数,即可得到结果;

2、注意参数的格式,详见上图《结果》

3、AppKey、AppSecret 获取需要管理员权限并登录钉钉开放平台获得;

钉钉开放平台

4、具体步骤见开发文档;

钉钉开发文档

5、服务器公网出口IP名单要提前设置好,不然数据会跑不出来。

141_Power Query之获取钉钉审批流自动刷新Power BI报告

6、ProcessCode参数获取

141_Power Query之获取钉钉审批流自动刷新Power BI报告

by焦棚子

焦棚子的文章目录

请点击【立即购买】或者【升级VIP】获得案例附件。

隐藏内容需要支付:¥20
立即购买 升级VIP
焦棚子
  • 本文由 发表于 2020年5月29日22:37:39
  • 除非特殊声明,本站文章均为焦棚子原创,转载请务必保留本文链接
142_Power BI之同比预测 Power BI

142_Power BI之同比预测

焦棚子的文章目录 请在文末下载附件 一、背景 最近刚好在做一个简单同比预测的模型,预测方法很简单,就是累计同比预测,把MTD展示出来。 二、数据源 1、日期表:Calendar 2、事实表1:Data...
140_Power BI&Power Pivot之降维展示同类型比较 Power BI

140_Power BI&Power Pivot之降维展示同类型比较

焦棚子的文章目录 请在文末下载附件 一、背景 最近一段时间比较忙,几乎没有时间更新网站内容,今天刚好周末,更新一个简单的需求。 上效果图: 在我们日常做对比分析的时候,经常几个相同维度(比如省份、区域...