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

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

# なにが違うのか
![datasets overview](https://qiita-image-store.s3.amazonaws.com/0/193509/09a7d864-1106-a359-e428-e62a340997d2.png)
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](https://docs.microsoft.com/en-us/power-bi/developer/overview-of-power-bi-rest-api)
[Push data into a Power BI dataset - Microsoft Docs](https://docs.microsoft.com/en-us/power-bi/developer/walkthrough-push-data)
[Dataset properties - Microsoft Docs](https://docs.microsoft.com/en-us/power-bi/developer/api-dataset-properties)
[Power BI REST API limitations - Microsoft Docs](https://docs.microsoft.com/en-us/power-bi/developer/api-rest-api-limitations)
[Power BI REST APIs - Microsoft Docs](https://docs.microsoft.com/ja-jp/rest/api/power-bi)

# いざ、まいる
いくつか試してみる
## Power BI 用 PowerShell モジュールが便利だった
[Microsoft Power BI Cmdlets for Windows PowerShell and PowerShell Core - GitHub](https://github.com/Microsoft/powerbi-powershell)
[Power BI 用 PowerShell モジュール - Qiita](https://qiita.com/kenakamu/items/6a1adc9b106bd21b1fd0) by @kenakamu

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

```powershell:Bearerトークンの取得
Get-PowerBIAccessToken -AsString
```
### データセットのポスト

```PowerShell:Datasets&nbsp;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
```
![tablefields](https://qiita-image-store.s3.amazonaws.com/0/193509/1c566acb-a51d-3f68-91a3-babd87b83a5e.png)

## データのポスト
### とてもシンプルなテーブル
```PowerShell:Datasets&nbsp;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
```
![report1](https://qiita-image-store.s3.amazonaws.com/0/193509/8da1c43f-6937-6756-5cf3-3cf91e866b9c.png)

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

```PowerShell:PostDatasets&nbsp;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
```
![report](https://qiita-image-store.s3.amazonaws.com/0/193509/1bcbec31-725f-f899-6d0e-8c6f7fa246fa.png)
列の非表示、メジャーの定義、普段ではできない書式設定ができた。計算列は定義できないと考えておけばよいか。

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

```json
{
    "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](https://docs.microsoft.com/en-us/power-bi/developer/walkthrough-push-data-register-app-with-azure-ad)
[Step 2: Get an authentication access token - Microsoft Docs](https://docs.microsoft.com/en-us/power-bi/developer/walkthrough-push-data-get-token)

# 必要な定義をPower Query で
こんな感じでワークシートに用意しまして、
![image.png](https://qiita-image-store.s3.amazonaws.com/0/193509/37e1ce5d-4e9a-8147-1a17-119fe8104d1b.png)

こんな感じにドン
![image.png](https://qiita-image-store.s3.amazonaws.com/0/193509/047b0893-0ebc-f9d3-30a2-6a2a0589de4c.png)

いい感じに Power Query トレーニングになった
![image.png](https://qiita-image-store.s3.amazonaws.com/0/193509/2d3028be-732d-fad1-b480-3c9fd2c5b542.png)

```text
// 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
```