A collection cmdlets for working with the PowerBI API's
PowerShell

README.md

PowerBIETL.psm1

A PowerShell module to quickly copy data from PBI Desktop into SQL Server

Sample of usage here:

PowerBIPS.psm1

A PowerShell module for the new PowerBI developer REST APIs.

More samples of usage here:

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

Sample Script 1 (Send CSV Data To PowerBI)

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
}

Sample Script 2 (Manual DataSet creation)

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

Out-PowerBI - Simply send any data to PowerBI in a single line of code

# 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 

Get-PBIAuthToken - Get's the OAuth PowerBI Authentication Token

$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)))

Get-PBIGroup - Get's the PowerBI groups in the user workspace

$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

Get-PBIDataSet - Get a DataSet or a List of DataSets

# 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

Test-PBIDataSet - Test the existence of a DataSet

if (Test-PBIDataSet -authToken $authToken -dataSetName "TestDataSet")
{
    Write-Host "true"
}
else
{
    Write-Host "false"
}

New-PBIDataSet - Create a DataSet

$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

Add-PBITableRows - Add Rows to a table

$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 - Delete Rows of a table

Clear-PBITableRows -authToken $authToken -dataSetName "TestDataSet" -tableName "TestTable" -Verbose

Update-PBITableSchema - Update a Table Schema

$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

Get-PBIDashboard - Gets a Dashboard collection

$dashboards = Get-PBIDashboard 

Get-PBIDashboardTile - Gets a Tile collection

$tiles = Get-PBIDashboardTile -dashboardId "XXX-XXX-XXX"