Power BIでkintoneのレコードを取り出す(REST API)

最近Kintoneを使い始めデータが溜まってきたので分析をしたくなりました。Power BI Desktopの標準機能「Power Query M言語」を使って、Kintoneからデータを取り出します。

用意するもの

URI: https://(サブドメイン名).cybozu.com/k/v1/records.json
アプリID
APIトークン
PowerBIに読み込みたい変数名

クエリ編集画面を開く

PowerBI Desktopを起動後、「データの取得」から「空のクエリ」を開きます。

「クエリエディタ」が開いたら「詳細エディタ」へ。以降が詳細エディタに貼り付ける内容です。

スクリプト

丸括弧で書かれているところは書き直してください。COLUMNSは好きなだけ並べられます。取り出したい変数名のリストを作るのが大変なときは、Excel 2016 や Power BI から kintone のレコードを REST API で取り出すで紹介されている方法でColumn1を展開して、ソースコードで1つ目の'Table.ExpandRecordColumn'をコピペすると楽だと思います。

let 
  // Author: Chachay https://blog.chachay.org/2020/04/kintone-power-bi.html
  BaseUrl         = "https://(サブドメイン名).cybozu.com/k/v1/records.json?",
  APPID           = "app=(アプリID)",
  Token           = "(APIトークン)",
  COLUMNS         = {"(取り出したい変数1)", "(取り出したい変数2)", "(取り出したい変数3)"},
  EntitiesPerPage = 500,

  GetJson = (Url) =>
      let Options = [Headers=[ #"X-Cybozu-API-Token" =  Token ]],
          RawData = Web.Contents(Url, Options),
          Source  = Json.Document(RawData),
          records = Source[records]
      in  records,

  Table.GenerateByPage = (getNextPage as function) as table =>
      let        
          listOfPages = List.Generate(
              () => getNextPage(null),
              (lastPage) => lastPage <> null,
              (lastPage) => getNextPage(lastPage)
          ),
          tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"})
      in
          Table.ExpandTableColumn(tableOfPages, "Column1", COLUMNS),

  CheckNextPage = (response, Index) as number => 
      if (response=EntitiesPerPage) then Index+1 else -1,

  GetPage = (Index) =>
      let Skip  = "offset " & Text.From(Index * EntitiesPerPage),
          Limit = "limit " & Text.From(EntitiesPerPage),
          Url   = BaseUrl & APPID & "&query=" & Limit & " " & Skip,
          Json  = GetJson(Url),
          data  = Table.FromRecords(Json),
          next  = CheckNextPage(Table.RowCount(data), Index)
      in  
          data meta [Next = next],
  
  GetAllPages = () as table =>
      Table.GenerateByPage((previous) => 
          let
              next_index = if (previous = null) then 0 else Value.Metadata(previous)[Next]?,
              page = if (next_index > -1) then GetPage(next_index) else null
          in
              page
      ),

  ExpandRecordColumns = (Table, columns, fieldName) =>
      if List.Count(columns)=0 then Table
      else 
          let tmp    = Table.ExpandRecordColumn(Table, List.First(columns), {fieldName}, {List.First(columns)}),
              Result = @ExpandRecordColumns(tmp, List.Skip(columns,1), fieldName)
          in Result,

  Table       = GetAllPages(),
  Data        = ExpandRecordColumns(Table, COLUMNS, "value")
in
  Data

類似手法

Power BI でのデータ取得からビジュアライズまで - Qiita: KintoneからCSVエクスポートを使った方法とKintone ODBC Driverを使った方法の紹介。後述のCDATAさんのプラグイン利用‥？
Excel 2016 や Power BI から kintone のレコードを REST API で取り出す: Power Queryを使ってKintoneのREST APIを使って取り出す方法の紹介。取り出し件数は500件まで。
Power BI から R スクリプトを介して kintone データにアクセスする - R3 Cloud Journey: Visual Studioを導入した上で、Rスクリプトを利用する方法の紹介。500件以上のデータも取り出せるが、ソフトウェアのインストール規模が大きくなるのが難点。
kintone Power BI Connector - Power BI | Connectors | Direct Query: Kintoneのデータを吸い出してPower BIにわたすプラグイン CDATAさん。ローカルからAPIをたたいてPower BI Desktopに読み出すソフトとクラウド上にデータマートを構築してKintoneからデータを複製するサービスの二段構え。

参考文献

レコードの取得（GET） – cybozu developer network: Kintone REST APIのリファレンス。取り出す内容に一工夫したいときはGetPage関数の中でqueryを工夫しましょう。
Power Query M 言語仕様 - PowerQuery M | Microsoft Docs: Power Query M言語の仕様
TripPin 5 - Paging | Microsoft Docs: 複数ページにまたがるJSONの取得例. Pagenation × PowerQuery.
How To Do Pagination In Power Query - Mark Tiedemann - Medium: クエリ設計の参考にした