A PowerShell module for the new PowerBI developer REST APIs.
More samples of usage here:
- https://ruiromanoblog.wordpress.com/2015/03/03/create-a-real-time-it-dashboard-with-powerbips/
- https://github.com/DevScope/powerbi-powershell-modules/blob/master/Modules/PowerBIPS/Samples
Module also available on PowerShell Gallery:
https://www.powershellgallery.com/packages/PowerBIPS
To install just type "Install-Module -Name PowerBIPS"
Cmdlets present in the module:
Cmdlet | Description |
---|---|
Out-PowerBI | The most easy way for you to send data into PowerBI |
Get-PBIAuthToken | Gets the authentication token required to communicate with the PowerBI APIs |
Get-PBIGroup | Gets the PowerBI groups in the user workspace |
New-PBIDataSet | Create a new DataSet |
Add-PBITableRows | Add's a collection of rows into a powerbi dataset table in batches |
Get-PBIDataSet | Gets a DataSet collection, includes definition and tables |
Test-PBIDataSet | Test the existence of a DataSet by name |
Clear-PBITableRows | Delete all the rows of a PowerBI dataset table |
Update-PBITableSchema | Updates a table schema |
Get-PBIDashboard | Gets a Dashboard collection |
Get-PBIDashboardTile | Gets a Tile collection from a dashboard |
For a better experience please copy this module on your UserProfile directory:
- %USERPROFILE%\Documents\WindowsPowershell\Modules\PowerBIPS
Or just import it to your PowerShell session by typing:
- Import-Module ".\Modules\PowerBIPS" -Force
while($true)
{
# Iterate each CSV file and add to a hashtable with a key for each table that will later be sent to PowerBI
Get-ChildItem "$currentPath\CSVData" -Filter "*.csv" |% {
$tableName = $_.BaseName.Split('.')[0]
$data = Import-Csv $_.FullName
# Send data to PowerBI
$data | Out-PowerBI -dataSetName "CSVSales" -tableName "Sales" -types @{"Sales.OrderDate"="datetime"; "Sales.SalesAmount"="double"; "Sales.Freight"="double"} -batchSize 300 -verbose
# Archive the file
Move-Item $_.FullName "$currentPath\CSVData\Archive" -Force
}
Write-Output "Sleeping..."
Sleep -Seconds 5
}
cls
# Get the authentication token using ADAL library (OAuth)
$authToken = Get-PBIAuthToken
# Test the existence of the dataset
if (-not (Test-PBIDataSet -authToken $authToken -dataSetName "TestDataSet"))
{
# If cannot find the DataSet create a new one with this schema
$dataSetSchema = @{
name = "TestDataSet"
; tables = @(
@{ name = "TestTable"
; columns = @(
@{ name = "Id"; dataType = "Int64" }
, @{ name = "Name"; dataType = "String" }
, @{ name = "Date"; dataType = "DateTime" }
, @{ name = "Value"; dataType = "Double" }
)
})
}
$createdDataSet = New-PBIDataSet -authToken $authToken -dataSet $dataSetSchema -Verbose
}
else
{
# Clear all the rows of the dataset table
Clear-PBITableRows -authToken $authToken -dataSetName "TestDataSet" -tableName "TestTable" -Verbose
}
# Create a array of sample rows with the same schema of the dataset table
$sampleRows = 1..53 |% {
@{
Id = $_
; Name = "Record $_"
; Date = [datetime]::Now
; Value = (Get-Random -Minimum 10 -Maximum 1000)
}
}
# Insert the sample rows in batches of 10
$sampleRows | Add-PBITableRows -authToken $authToken -dataSetName "TestDataSet" -tableName "TestTable" -batchSize 10 -Verbose
# Upload local computer windows process data to PowerBI
Get-Process | Out-PowerBI -verbose
# Upload CSV data to PowerBI dataset named "CSVSales" and with the types specified
Import-Csv "c:\csvData.csv" | Out-PowerBI -dataSetName "CSVSales" -tableName "Sales" -types @{"Sales.OrderDate"="datetime"; "Sales.SalesAmount"="double"; "Sales.Freight"="double"} -batchSize 300 -verbose
$authToken = Get-PBIAuthToken
# To use username+password authentication you need to create an Azure AD Application and get it's id
$authTokenWithUsername = Get-PBIAuthToken -ClientId "C0E8435C-614D-49BF-A758-3EF858F8901B" -Credential (Get-Credential -username "<username>"
$authTokenWithUsernameAndPassword = Get-PBIAuthToken -ClientId "C0E8435C-614D-49BF-A758-3EF858F8901B" -Credential (new-object System.Management.Automation.PSCredential("<username>",(ConvertTo-SecureString -String "<password>" -AsPlainText -Force)))
$authToken = Get-PBIAuthToken
$group = Get-PBIGroup -authToken $authToken -name "SalesGroup"
# Gets the datasets of the group
Set-PBIGroup -id $group.id
$dataSetsOfGroup = Get-PBIDataSet -authToken $authToken
# Clear the group and all the requests now are for the default workspace
Set-PBIGroup -clear
# All DataSets
$dataSets = Get-PBIDataSet -authToken $authToken
# By Name
$dataSets = Get-PBIDataSet -authToken $authToken -dataSetName "TestDataSet"
# With tables and definition (retentionPolicy,...)
$dataSets = Get-PBIDataSet -authToken $authToken -dataSetName "TestDataSet" -includeTables -includeDefinition
if (Test-PBIDataSet -authToken $authToken -dataSetName "TestDataSet")
{
Write-Host "true"
}
else
{
Write-Host "false"
}
$dataSetSchema = @{
name = "TestDataSet"
; tables = @(
@{ name = "TestTable"
; columns = @(
@{ name = "Id"; dataType = "Int64" }
, @{ name = "Name"; dataType = "String" }
, @{ name = "Date"; dataType = "DateTime" }
, @{ name = "Value"; dataType = "Double" }
)
})
}
$createdDataSet = New-PBIDataSet -authToken $authToken -dataSet $dataSetSchema -Verbose
$sampleRows = 1..53 |% {
@{
Id = $_
; Name = "Record $_"
; Date = [datetime]::Now
; Value = (Get-Random -Minimum 10 -Maximum 1000)
}
}
# Push the rows into PowerBI in batches of 10 records
$sampleRows | Add-PBITableRows -authToken $authToken -dataSetName "TestDataSet" -tableName "TestTable" -batchSize 10 -Verbose
Clear-PBITableRows -authToken $authToken -dataSetName "TestDataSet" -tableName "TestTable" -Verbose
$tableSchema = @{
name = "Sales"
; columns = @(
@{ name = "Col1"; dataType = "Int64" }
, @{ name = "Col2"; dataType = "string" }
, @{ name = "NewColumn"; dataType = "string" }
)
}
Update-PBITableSchema -authToken $authToken -dataSetId "<dataSetId>" -table $tableSchema -verbose
$dashboards = Get-PBIDashboard
$tiles = Get-PBIDashboardTile -dashboardId "XXX-XXX-XXX"