Power BI workout - Export To File In Group

Power BI service からレポートをエクスポートする。PDF / PPTX まで。

# Power BI Cmdlets for Windows PowerShell and PowerShell Core
https://docs.microsoft.com/en-us/powershell/power-bi/overview

https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.profile/connect-powerbiserviceaccount

https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.workspaces/get-powerbiworkspace

https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.reports/get-powerbireport

https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.profile/invoke-powerbirestmethod

https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.profile/disconnect-powerbiserviceaccount

# Power BI REST API
https://docs.microsoft.com/en-US/rest/api/power-bi/

https://docs.microsoft.com/en-us/rest/api/power-bi/reports/export-to-file-in-group

https://docs.microsoft.com/en-us/rest/api/power-bi/reports/get-export-to-file-status-in-group

https://docs.microsoft.com/en-us/rest/api/power-bi/reports/get-file-of-export-to-file-in-group

https://docs.microsoft.com/en-us/rest/api/power-bi/reports/get-pages-in-group
# Reports - Export To File In Group
```text
POST https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/ExportTo
```
## Request Body
|Name|Required|Type|Description|
|:--|:--|:--|:--|
|format|True|FileFormat|The requested format for the exported file|
|powerBIReportConfiguration||PowerBIReportExportConfiguration|The configuration used to export a Power BI report|
|paginatedReportConfiguration||PaginatedReportExportConfiguration|The configuration used to export a paginated report|

### FileFormat
|Name|Type|Description|
|:--|:--|:--|
|PDF|string|PDF|
|PPTX|string|Microsoft PowerPoint|
|ACCESSIBLEPDF|string|Accessible PDF (only supported for paginated reports)|
|CSV|string|CSV (only supported for paginated reports)|
|DOCX|string|Microsoft Word (only supported for paginated reports)|
|IMAGE|string|BMP, EMF, GIF, JPEG, PNG, or TIFF image formats (only supported for paginated reports)|
|MHTML|string|MHTML (only supported for paginated reports)|
|PNG|string|PNG (only supported for Power BI reports)|
|XLSX|string|Microsoft Excel (only supported for paginated reports)|
|XML|string|XML (only supported for paginated reports)|

### PowerBIReportExportConfiguration
|Name|Type|Description|
|:--|:--|:--|
|settings|ExportReportSettings|The settings to be applied for the export to file job|
|pages|ExportReportPage[]|A list of pages to export and their properties. The same page may appear more than once with different visuals.|
|defaultBookmark|PageBookmark|A default bookmark to apply on all pages that don't have a specific bookmark|
|identities|EffectiveIdentity[]|A list of identities to use for row-level security rules|
|reportLevelFilters|ExportFilter[]|A list of report level filters to apply. Currently, only one filter is supported.|

#### ExportReportSettings
|Name|Type|Description|
|:--|:--|:--|
|locale|string|The locale to apply|
|includeHiddenPages|boolean|Whether to include hidden pages when exporting an entire report. If not provided, the default behavior is to exclude hidden pages. This property will be ignored when specific pages are exported.|

#### Pages
|Name|Type|Description|
|:--|:--|:--|
|pageName|string|The page name|
|visualName|string|The name of the visual to export. Specify a name, in case only a single visual from this page is exported.|
|bookmark|PageBookmark|The bookmark to apply on the page|

## Responses
|Name|Type|Description|
|:--|:--|:--|
|202 Accepted|Export|Accepted|

### Export
|Name|Type|Description|
|:--|:--|:--|
|ResourceFileExtension|string|The extension of the exported file
|createdDateTime|string|The start date and time of the export to file job|
|expirationTime|string|The expiration date and time of the retrieval URL|
|id|string|The export to file job ID|
|lastActionDateTime|string|The date and time of the last change to the export to file job|
|percentComplete|integer|Job progress as a percentage|
|reportId|string|The ID of the exported report|
|reportName|string|The name of the exported report|
|resourceLocation|string|The retrieval URL for the exported file|
|status|ExportState|The current state of the export to file job|
# DEMO
## Define
```PowerShell
$WorkspaceName = "DEMO2"
$ReportName = "Report1"
$FileFormat = "pdf"
# $FileFormat = "pptx"
$Locale = "ja-JP"
$PageDisplayName = "Page 1"
```
## Login to Power BI
https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.profile/connect-powerbiserviceaccount
```PowerShell
Login-PowerBI | Out-Null
```
## Get Power BI workspace, report
https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.workspaces/get-powerbiworkspace

https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.reports/get-powerbireport
```PowerShell
$Workspace = Get-PowerBIWorkspace -Name $WorkspaceName
$Report = Get-PowerBIReport -Workspace $Workspace -Name $ReportName |
    Select-Object -First 1
```
## Reports - Export To File In Group
### Request body
#### Settings
```PowerShell
$RequestBody = @"
{
    "format": "$FileFormat",
    "powerBIReportConfiguration": {
        "settings": {
            "locale": "$Locale"
        }
    }
}
"@
```
#### Pages
Reports - Get Pages In Group
```text
GET https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/pages
```
**Response**
|Name|Type|Description|
|:--|:--|:--|
|200 OK|Pages|OK

**Pages**
|Name|Type|Description|
|:--|:--|:--|
|odata.context|string|OData context|
|value|Page[]|The page collection|

**Page**
|Name|Type|Description|
|:--|:--|:--|
|displayName|string|The display name of the report page|
|name|string|The name of the report page|
|order|integer|The order of the report page|
```PowerShell
$Url = "groups/$($Workspace.Id)/reports/$($Report.Id)/pages"
$Pages = Invoke-PowerBIRestMethod -Method Get -Url $Url |
    ConvertFrom-Json
$PageName = $Pages.value | Where-Object displayName -EQ $PageDisplayName |
    Select-Object Name
$RequestBody = @"
{
    "format": "$FileFormat",
    "powerBIReportConfiguration": {
        "settings": {
            "locale": "$Locale"
        },
        "pages": [
            {
                "pageName": "$($PageName.Name)"
            }
        ]
    }
}
"@
```
### Invoke REST request
```PowerShell
$Url = "groups/$($Workspace.Id)/reports/$($Report.Id)/ExportTo"
$ResponseExportTo =
    Invoke-PowerBIRestMethod -Method Post -Url $Url -Body $RequestBody |
        ConvertFrom-Json
```
## Reports - Get Export To File Status In Group
```text
GET https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/exports/{exportId}
```

### Response
|Name|Type|Description|
|:--|:--|:--|
|200 OK|Export|OK|
|202 Accepted|Export|Accepted|

#### Export
|Name|Type|Description|
|:--|:--|:--|
|ResourceFileExtension|string|The extension of the exported file|
|createdDateTime|string|The start date and time of the export to file job|
|expirationTime|string|The expiration date and time of the retrieval URL|
|id|string|The export to file job ID|
|lastActionDateTime|string|The date and time of the last change to the export to file job|
|percentComplete|integer|Job progress as a percentage|
|reportId|string|The ID of the exported report|
|reportName|string|The name of the exported report|
|resourceLocation|string|The retrieval URL for the exported file|
|status|ExportState|The current state of the export to file job|

##### ExportState
|Name|Type|Description|
|:--|:--|:--|
|Failed|string|The export to file job failed|
|NotStarted|string|The export to file job didn't start|
|Running|string|The export to file job is running|
|Succeeded|string|The export to file job finished succesfully|
|Undefined|string|The state of the export to file job is undefined|
### Invoke REST request
```PowerShell
$Url =
    "groups/$($Workspace.Id)/reports/$($Report.Id)/exports/$($ResponseExportTo.ID)"
$ResponseExportStatus = Invoke-PowerBIRestMethod -Method Get -Url $Url |
    ConvertFrom-Json

"Export status : $($ResponseExportStatus.status)"
"Percent complete : $($ResponseExportStatus.percentComplete)"
```
## Reports - Get File Of Export To File In Group
```text
GET https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/exports/{exportId}/file
```
### Response
|Name|Type|Description|
|:--|:--|:--|
|200 OK|file|The exported file<br>Media Types: "application/\*", "image/\*", "text/csv", "text/xml", "multipart/related"|
### Invoke REST request
```PowerShell
Invoke-PowerBIRestMethod -Method Get `
    -Url $ResponseExportStatus.resourceLocation `
    -OutFile ".\Output\$ReportName$($ResponseExportStatus.resourceFileExtension)"
```
## Logout of Power BI
https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.profile/disconnect-powerbiserviceaccount
```powershell
Logout-PowerBI
```

# 思ったこと🙄
やればわかる。やらなければ、わからないことがわからない。
# その他
https://powerbi.connpass.com/

- [Power BI 勉強会 - connpass](https://powerbi.connpass.com/)
- [Power BI Desktop を使い始めるには](http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981)
- [Excel で Power Query を使い始めるには](http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9)