Power BI REST API を使って Push をサポートするデータセットを作成

APIを介してデータセットにデータを追加できるんですよということなのだけど、Power BI Service で作成できる ストリーミング データセットとちょっと違うのです。ついでに Power Query で JSONフォーマットのデータを作るようなことも。

なにが違うのか

Power BI Service で作成できる ストリーミング データセットは、刻々と変化する ストリーミング データ タイルを利用でき、[履歴データの解析]オプションを有効にすることで "RealTimeData" というテーブルが用意されるのでたまったデータでレポートも作れる。ストリーミング データのポストに使用するエンドポイントが key パラメータ付きURLで用意されるのでこの後はとても簡単なのですよね。ただ、テーブルはひとつのみで書式設定ができず集計は暗黙のメジャーのみとなってしまいます。

defaultMode	API アクセス	ダッシュボード	テーブル	レポート
Streaming	ストリーミング	○
PushStreaming	ハイブリッド	○	○(ひとつだけ)	○
Push	プッシュ	○	○	○

defaultMode が "Push" のデータセットは、Power BI Desktop で作成されたデータセットと同じということ。ただ、データの追加(Push) はPower BI REST API 経由のみ。

そもそもで読むべき

What can developers do with the Power BI API? - Microsoft Docs
Push data into a Power BI dataset - Microsoft Docs
Dataset properties - Microsoft Docs
Power BI REST API limitations - Microsoft Docs
Power BI REST APIs - Microsoft Docs

いざ、まいる

いくつか試してみる

Power BI 用 PowerShell モジュールが便利だった

Microsoft Power BI Cmdlets for Windows PowerShell and PowerShell Core - GitHub
Power BI 用 PowerShell モジュール - Qiita by @kenakamu

Bearerトークンの取得ができるので POSTMAN でもあれこれしやすい。

Bearerトークンの取得

Get-PowerBIAccessToken -AsString

データセットのポスト

Datasets PostDataset

Login-PowerBI

$col1 = New-PowerBIColumn -Name DateTime -DataType DateTime
$col2 = New-PowerBIColumn -Name Value -DataType Int64
$table1 = New-PowerBITable -Name Table1 -Columns $col1, $col2
$dataset1 = New-PowerBIDataset -Name PushDataset -Tables $table1
$PushDataset = Add-PowerBIDataset -Dataset $dataset1

データのポスト

とてもシンプルなテーブル

Datasets PostRows

$rows = 
    @{DateTime = "2018-10-12 11:30:00"; Value = 20},
    @{DateTime = "2018-10-13 15:30:00"; Value = 10}

Add-PowerBIRow -Dataset $PushDataset -TableName Table1 -Rows $rows

表示書式とメジャーをデータセットに定義する

ここからは JSON形式でデータセットを定義。
表示形式 : formatString
メジャー : measures ( name / expression )

PostDatasets PostRows

$body = @"
{
    "name": "PushDataset",
    "defaultMode": "Push",
    "tables": [
        {
            "name": "Table1",
            "columns": [
                {
                    "name": "ID",
                    "dataType": "Int64",
                    "isHidden": true
                },
                {
                    "name": "Date",
                    "dataType": "DateTime",
                    "formatString": "yyyy-mm-dd"
                },
                {
                    "name": "Value",
                    "dataType": "Int64",
                    "formatString": "0;\"\u25b2\"0"
                }
            ],
            "measures": [
                {
                    "name": "Sum of Value",
                    "expression": "SUM( 'Table1'[Value] )",
                    "formatString": "0;\"\u25b2\"0"
                },
                {
                    "name": "Count of Rows",
                    "expression": "COUNTROWS( 'Table1' )",
                    "formatString": "0"
                }
            ]
        }
    ]
}
"@

$PushDataset = Invoke-PowerBIRestMethod -Url datasets -Method Post -Body $body

$rows = 
    @{ID = 1; Date = "2018-10-13 00:30:00"; Value = 20},
    @{ID = 2; Date = "2018-10-15 00:00:00"; Value = -100},
    @{ID = 3; Date = "2018-10-14 00:00:00"; Value = 60},
    @{ID = 4; Date = "2018-10-14 00:00:00"; Value = -10}

Add-PowerBIRow -DatasetId ($PushDataset|ConvertFrom-Json).id -TableName Table1 -Rows $rows

列の非表示、メジャーの定義、普段ではできない書式設定ができた。計算列は定義できないと考えておけばよいか。

リレーションシップ/列で並び替え/既定の集計とか

リレーションシップ : relationships
列で並び替え : sortByColumn
既定の集計 : summarizeBy

{
    "name": "PushDataset",
    "defaultMode": "Push",
    "tables": [
        {
            "name": "Category",
            "columns": [
                {
                    "name": "CategoryId",
                    "dataType": "Int64",
                    "isHidden": true
                },
                {
                    "name": "CategoryName",
                    "dataType": "String",
                    "sortByColumn": "CategoryId"
                }
            ]
        },
        {
            "name": "Item",
            "columns": [
                {
                    "name": "ItemName",
                    "dataType": "String",
                },
                {
                    "name": "CategoryId",
                    "dataType": "Int64",
                    "isHidden": true
                },
                {
                    "name": "ItemValue",
                    "dataType": "Int64",
                    "formatString": "0;\"\u25b2\"0",
                    "summarizeBy": "sum"
                }
            ],
            "measures": [
                {
                    "name": "Sum of Value",
                    "expression": "SUM( 'Item'[ItemValue] )",
                    "formatString": "0;\"\u25b2\"0"
                }
            ]
        }
    ],
    "relationships": [
        {
            "fromTable": "Item",
            "fromColumn": "CategoryId",
            "toTable": "Category",
            "toColumn": "CategoryId",
            "crossFilteringBehavior": "OneDirection",
            "name": "2f8e72a0-995f-4c13-9816-4a52e98846a0"
        }
    ]
}

データをポストする実際の手段

ひとまず PowerShell で作業したけれども、Microsoft Flow や LogicApps とかでポストするのがお手軽って感じでしょうか。そうでなければ、
Step 1: Register an app with Azure AD - Microsoft Docs
Step 2: Get an authentication access token - Microsoft Docs

必要な定義をPower Query で

こんな感じでワークシートに用意しまして、

こんな感じにドン

いい感じに Power Query トレーニングになった

// Tables
let
    Source = Excel.CurrentWorkbook(){[Name="Tables"]}[Content],
    ChangedType = 
        Table.TransformColumnTypes(
            Source,
            {{"isHidden", type logical}, {"name", type text}}
        ),
    FilteredRows = Table.SelectRows(ChangedType, each [name] <> null),
    AddedColumns = 
        Table.AddColumn(
            FilteredRows, 
            "columns", 
            each Columns{[TableName = _[name]]}[columns], 
            type table
            ),
    AddedMeasures = 
        Table.AddColumn(
            AddedColumns, 
            "measures", 
            each try Measures{[TableName = _[name]]}[measures] otherwise {}
        )
in
    AddedMeasures

// Columns
let
    Source = Excel.CurrentWorkbook(){[Name="Columns"]}[Content],
    ChangedType = 
        Table.TransformColumnTypes(
            Source,
            {
                {"TableName", type text}, 
                {"name", type text}, 
                {"dataType", type text}, 
                {"formatString", type text}, 
                {"summarizeBy", type text}, 
                {"isHidden", type logical}
            }
        ),
    GroupedRows = 
        Table.Group(
            ChangedType, 
            {"TableName"}, 
            {
                "columns", 
                each Table.RemoveColumns(_,{"TableName"}), 
                type table}
        )
in
    GroupedRows

// Measures
let
    Source = Excel.CurrentWorkbook(){[Name="Measures"]}[Content],
    ChangedType = 
        Table.TransformColumnTypes(
            Source,
            {
                {"TableName", type text}, 
                {"name", type text}, 
                {"expression", type text}, 
                {"formatString", type text}, 
                {"isHidden", type logical}
            }
        ),
    GroupedRows = 
        Table.Group(
            ChangedType, 
            {"TableName"}, 
            {
                "measures", 
                each Table.ToRecords(
                    Table.RemoveColumns(_,{"TableName"})
                )
            }
        )
in
    GroupedRows

// Dataset
let
    Source = Excel.CurrentWorkbook(){[Name="Dataset"]}[Content],
    ChangedType = 
        Table.TransformColumnTypes(
            Source,
            {
                {"name", type text}, 
                {"defaultMode", type text}
            }
        ),
    AddedTables = 
        Table.AddColumn(
            ChangedType, "tables", each Tables
        ),
    AddedRelationships = 
        Table.AddColumn(
            AddedTables, "relationships", each Relationships
        )
in
    AddedRelationships

// Relationships
let
    Source = Excel.CurrentWorkbook(){[Name="Relationships"]}[Content],
    ChangedType = 
        Table.TransformColumnTypes(
            Source,
            {
                {"fromTable", type text}, 
                {"fromColumn", type text}, 
                {"toTable", type text}, 
                {"toColumn", type text}, 
                {"crossFilteringBehavior", type text}
            }
        ),
    AddedName = 
        Table.AddColumn(
            ChangedType, "name", each Text.NewGuid(), type text
        ),
    FilteredRows = 
        Table.SelectRows(
            AddedName, each [fromTable] <> null
        )
in
    FilteredRows

// JsonFromTable
let
    Source = Text.FromBinary( Json.FromValue( Dataset{0} ) )
in
    Source