131_Power Query之獲取釘釘日誌自動重新整理Power BI報告

部落格：www。jiaopengzi。com

一、背景

最近在玩釘釘日誌，企業填寫

簡單資料

後方便彙總到一起比較實用的工具，但資料填寫以後還是需要下載日誌報表，比較麻煩。

在做BI的時候，可不可以用pq（power query）獲取這些資料同步更新呢？查閱釘釘開發文件，日誌是可以透過api介面獲取。

那麼寫個pq（power query）自定義函式獲取即可。

二、資料結構

為了方便演示，新建一個日誌填寫模板。

標題：

測試日誌

欄位：

日期，測試專案，測試值

設計日誌模板填寫測試資料

填寫好的日誌

三、上M

1、自定義函式Fxdingding

看到這一串不要慌，

直接複製

到自己的pq中儲存為

Fxdingding

即可。

let fx=（AppKey as text ，AppSecret as text，Month as date，RecodeName as text，NameList as list ） as table=>lettoken=Json。Document（ Text。FromBinary（Web。Contents（“https：//oapi。dingtalk。com/gettoken？appkey=”&AppKey&“&appsecret=”&AppSecret）））［access_token］，//時間戳轉北京時間===================================================BeijingTime=（x as number） =>lett=DateTime。FromFileTime（（11644473600000+x）*10000）int，//日期轉換為時間戳===================================================UnixTimestampStart=（m as date）=>let源 = （（Number。From（m）-70*365-19）86400-83600）*1000，tf=Text。From（源）intf，//提供日期的月末最後一天轉換為時間戳===================================UnixTimestampEnd=（m as date）=>lett=Date。EndOfMonth（m），st = （（Number。From（t）-70*365-19）86400-83600）*1000+86399999，tf=Text。From（st）intf，//根據cursor獲取每頁recode===========================================fxrl=（n as number，name as text）=>letfx=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］infx，//每頁的recode轉換成表===============================================fxrt = （listr as list）=>letfx=Table。Combine（List。Transform（listr，®=> Table。FromRecords（{Record。TransformFields（ R，{“contents”， each Record。FromList（Table。FromRecords（）［value］，Table。FromRecords（）［key］）}）}）））infx，//獲取corsorlist====================================================//其中100是個引數，表2000條資料，多數需求能滿足，大約這個數用條數除以20得到向上取整數字替換100corsorlist={0} & List。Distinct（ List。Transform（{1…100}，（L）=>List。Accumulate（{1…L}，0，（x，y）=>if y=1 thenJson。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］elseJson。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形式。”］inValue。ReplaceType（fx，Value。Type（fx） meta metadata）

2、自定義函式的說明

function（AppKey as

text

，AppSecret as

text

，Month as

date

，RecodeName as

text

，NameList as

list

） as table