Microsoft.PowerBI.PowerShell.psm1

# Copyright © Microsoft Corporation. All Rights Reserved.
# This code released under the terms of the
# Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
# Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment.
# THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED,
# INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
# We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that.
# You agree:
# (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded;
# (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded;
# and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code

# Connection Functions
function Connect-PowerBI{

<#
 .SYNOPSIS
 Stores parameters to scrip variable to be used in other functions.

 .DESCRIPTION
 The Connect-PowerBI cmdlet lets you store parameters to scrip variable to be used in other functions.

 .parameter AuthorityName
 Azure Active Directory Name or Guid. i.e.)contoso.onmicrosoft.com

 .parameter ClientId
 A registerered ClientId as application to the Azure Active Directory.

 .parameter UserName
 A username to login to PowerBI.com.

 .parameter Password
 A password for UserName.

 .parameter GroupId
 A guid of Group.

 .EXAMPLE
 Connect-PowerBI -AuthorityName contoso.onmicrosoft.com -ClientId bf922382-cdc4-43d4-995c-0f90ecdeda21 -UserName user@contoso.onmicrosoft.com -Password password

 This examples connects to PowerBI instance of contoso.onmicrosoft.com by using specified UserName/Password.

 .EXAMPLE
 Connect-PowerBI -AuthorityName contoso.onmicrosoft.com -ClientId bf922382-cdc4-43d4-995c-0f90ecdeda21 -UserName user@contoso.onmicrosoft.com -Password password -GroupdId ce88923a-b885-4d11-997a-a240e73fb6b5

 This examples connects to a group of PowerBI instance of contoso.onmicrosoft.com by using specified UserName/Password.

 .EXAMPLE
 Connect-PowerBI -AuthorityName contoso.onmicrosoft.com -ClientId bf922382-cdc4-43d4-995c-0f90ecdeda21 -RedirectUrl http://localhost/powerbi

 This examples connects to PowerBI instance of contoso.onmicrosoft.com by using RedirectUrl, which may popup for authentication

  .EXAMPLE
 Connect-PowerBI -AuthorityName contoso.onmicrosoft.com -ClientId bf922382-cdc4-43d4-995c-0f90ecdeda21 -RedirectUrl http://localhost/powerbi -ForcePromptSignIn

 This examples connects to PowerBI instance of contoso.onmicrosoft.com by using RedirectUrl, which popup for authentication
#>


    PARAM(
        [parameter(Mandatory=$true)]
        [string] $AuthorityName,
        [parameter(Mandatory=$true)]
        [string] $ClientId,
        [parameter(Mandatory=$true, ParameterSetName="UserName")]
        [string]$UserName,
        [parameter(Mandatory=$true, ParameterSetName="UserName")]
        [string]$Password,
        [parameter(Mandatory=$true, ParameterSetName="RedirectUri")]
        [string]$RedirectUri,
        [parameter(Mandatory=$false, ParameterSetName="RedirectUri")]
        [switch]$ForcePromptSignIn,
        [parameter(Mandatory=$false)]
        [string] $GroupId
    )

    # Storing variable to script level
    $script:authorityName = $AuthorityName
    $script:PowerBIClientId = $ClientId
    $script:PowerBIUserName = $UserName
    $script:PowerBIPassword = $Password
    $script:PowerBIRedirectUri = $RedirectUri
    $script:PowerBIForcePromptSignIn = $ForcePromptSignIn
    $script:PowerBIResourceId = "https://analysis.windows.net/powerbi/api"
    $script:PowerBIBaseAddress = "https://api.powerbi.com/v1.0/myorg/"
    if($GroupId -ne '')
    {
        $script:PowerBIBaseAddress += "groups/$GroupId/"
    }

    $script:PowerBIheader = @{"Content-Type"="application/json";"Authorization"="Bearer " + (Get_PowerBIAccessToken)} 
}

function Switch-PowerBIContext{

<#
 .SYNOPSIS
 Switches PowerBI context to a group or me.

 .DESCRIPTION
 The Switch-PowerBIContext cmdlet lets you switches PowerBI context to a group or me.

 .parameter GroupId
 A guid of Group. You can get them by running Get-PowerBIGroups once you run Connect-PowerBIApi without GroupId.

 .EXAMPLE
 Switch-PowerBIContext -GroupId ce88923a-b885-4d11-997a-a240e73fb6b5

 This example switches PowerBI address to https://api.powerbi.com/v1.0/myorg/groups/ce88923a-b885-4d11-997a-a240e73fb6b5/

 .EXAMPLE
 Switch-PowerBIContext -Me

 This example switches PowerBI address to https://api.powerbi.com/v1.0/myorg/

#>


    PARAM(
        [parameter(Mandatory=$true, parameterSetName="GroupId")]
        [string] $GroupId,
        [parameter(Mandatory=$true, parameterSetName="Me")]
        [switch] $Me
    )
      
    $script:PowerBIBaseAddress = "https://api.powerbi.com/v1.0/myorg/"
    if($GroupId -ne '')
    {
        $script:PowerBIBaseAddress += "groups/$GroupId/"
    }
    if($Me)
    {
        $script:PowerBIBaseAddress = "https://api.powerbi.com/v1.0/myorg/"
    }
    
    Write-Verbose "Current PowerBI address is $PowerBIBaseAddress."
}

# Private AccessToken obtain function
function Get_PowerBIAccessToken{
    if($PowerBIUserName -ne '')
    {
        return Get-ADALAccessToken -AuthorityName $authorityName -ClientId $PowerBIClientId `
        -ResourceId $PowerBIResourceId `
        -UserName $PowerBIUserName -Password $PowerBIPassword 
    }
    else
    {
        if($PowerBIForcePromptSignIn)
        {
            return Get-ADALAccessToken -AuthorityName $authorityName -ClientId $PowerBIClientId `
            -ResourceId $PowerBIResourceId `
            -RedirectUri $PowerBIRedirectUri -ForcePromptSignIn
        }
        else
        {
            return Get-ADALAccessToken -AuthorityName $authorityName -ClientId $PowerBIClientId `
            -ResourceId $PowerBIResourceId `
            -RedirectUri $PowerBIRedirectUri 
        }
    }
}

# DataSet Operations
# https://msdn.microsoft.com/en-us/library/mt203562.aspx
function Add-PowerBIDataSet{
<#
 .SYNOPSIS
 Adds PowerBI dataset.

 .DESCRIPTION
 The Add-PowerBIDataSet cmdlet lets you add PowerBI dataset. You need to include Table definition as well.

 .parameter DataSet
 DataSet object to be created. You need to include Table definition as well.

 .EXAMPLE
 PS C:\>$col1 = New-PowerBIColumn -ColumnName ID -ColumnType Int64
 PS C:\>$col2 = New-PowerBIColumn -ColumnName Data -ColumnType String
 PS C:\>$table1 = New-PowerBITable -TableName SampleTable1 -Columns $col1,$col2
 PS C:\>
 PS C:\>$col3 = New-PowerBIColumn -ColumnName ID -ColumnType Int64
 PS C:\>$col4 = New-PowerBIColumn -ColumnName Date -ColumnType DateTime
 PS C:\>$col5 = New-PowerBIColumn -ColumnName Detail -ColumnType String
 PS C:\>$col6 = New-PowerBIColumn -ColumnName Result -ColumnType Double
 PS C:\>$table2 = New-PowerBITable -TableName SampleTable2 -Columns $col3,$col4,$col5,$col6
 PS C:\>
 PS C:\>$dataset = New-PowerBIDataSet -DataSetName SampleDataSet -Tables $table1,$table2
 PS C:\>
 PS C:\>Add-PowerBIDataSet -DataSet $dataset

 This example instantiate a table with two columns and another table with four columns, and instantiate a dataset.
 Then, it creates the dataset in PowerBI.
#>

    PARAM(
        [parameter(Mandatory=$true)]
        [string]$DataSet
    )
        
    # Send request and return result only.
    $result = Invoke-RestMethod -Method Post -Uri ($PowerBIBaseAddress + "datasets") -Headers $PowerBIheader -Body $DataSet
    return $result.id
}

# https://msdn.microsoft.com/en-us/library/mt203567.aspx
function Get-PowerBIDataSets{
<#
 .SYNOPSIS
 Gets all PowerBI datasets.

 .DESCRIPTION
 The Get-PowerBIDataSets cmdlet lets you retrieve PowerBI datasets for your organization.

 .EXAMPLE
 Get-PowerBIDataSets

 id name
 -- ----
 4b644350-f745-48dd-821c-f008350199a8 DataSet1
 d77cd0fc-f310-4547-97fa-47c5ccf7f9e1 DataSet2
 3f08bb1b-4f9e-4be7-939f-750ddbb629de DataSet3
 ...
#>


    $result = Invoke-RestMethod -Method Get -Uri ($PowerBIBaseAddress + "datasets") -Headers $PowerBIheader
    return $result.value
}

# Table Operations
# https://msdn.microsoft.com/en-us/library/mt203556.aspx
function Get-PowerBITables{
<#
 .SYNOPSIS
 Gets all PowerBI Tables for specified DataSet.

 .DESCRIPTION
 The Get-PowerBIDataSets cmdlet lets you retrieve PowerBI datasets for your organization.

 .parameter DataSetId
 The Id of dataset.

 .EXAMPLE
 Get-PowerBITables -DataSetId 4b644350-f745-48dd-821c-f008350199a8

 name
 ----
 PowerBISampleTable1
 PowerBISampleTable2
 ...
#>

    PARAM(
        [parameter(Mandatory=$true)]
        [string]$DataSetId
    )

    $result = Invoke-RestMethod -Method Get -Uri ($PowerBIBaseAddress + "datasets/$DataSetID/tables") -Headers $PowerBIheader
    return $result.value
}

# https://msdn.microsoft.com/en-us/library/mt203560.aspx
function Update-PowerBITableSchema{
<#
 .SYNOPSIS
 Updates PowerBI Table Schema.

 .DESCRIPTION
 The Update-PowerBITableSchema cmdlet lets you update PowerBI Table Schema.

 .parameter DataSetId
 A DataSetId of the table.

 .parameter TableName
 Updating Table name

 .parameter TableSchema
 A Table object

 .EXAMPLE
 PS C:\>$col1 = New-PowerBIColumn -ColumnName ID -ColumnType Int64
 PS C:\>$col2 = New-PowerBIColumn -ColumnName Data -ColumnType String
 PS C:\>$col3 = New-PowerBIColumn -ColumnName Date -ColumnType DateTime
 PS C:\>$table1 = New-PowerBITable -TableName SampleTable1 -Columns $col1,$col2,$col3
 PS C:\>
 PS C:\>Update-PowerBITableSchema -DataSetId 4b644350-f745-48dd-821c-f008350199a8 -TableName SampleTable1

 This example update SampleTable1 Table Schema with three columns.
#>

    PARAM(
        [parameter(Mandatory=$true)]
        [string]$DataSetId,
        [parameter(Mandatory=$true)]
        [string]$TableName,
        [parameter(Mandatory=$true)]
        [string]$TableSchema

    )
        
    # Send request and return result only.
    $result = Invoke-RestMethod -Method Put -Uri ($PowerBIBaseAddress + "datasets/$DataSetId/tables/$TableName") -Headers $PowerBIheader -Body $TableSchema
}

# Row Operations
# https://msdn.microsoft.com/en-us/library/mt203561.aspx
function Add-PowerBIRows{
<#
 .SYNOPSIS
 Adds Rows to PowerBI table.

 .DESCRIPTION
 The Add-PowerBIRows cmdlet lets you add rows to PowerBI table.

 .parameter DataSetId
 A ataSet Id which the table resides.

 .parameter TableName
 A Table Name to insert data.

 .parameter Rows
 Actual Data to be inserted. Rows are array of hashtable. i.e.) @{"Column1"="Value1;"Column2"="Value2"}

 .EXAMPLE
 Add-PowerBIRows -DataSetId 4b644350-f745-48dd-821c-f008350199a8 -TableName Table1 -Rows @{"Column1"="Value1";"Column2"="Value2"},@{"Column1"="Value1";"Column2"="Value2"}

 This example inserts two rows to Table1.

 .EXAMPLE
 Add-PowerBIRows -DataSetId 4b644350-f745-48dd-821c-f008350199a8 -TableName Table1 -Rows (Import-Csv -Path ".\data.csv")

 This example inserts rows from CSV to Table1.

#>

    PARAM(
        [parameter(Mandatory=$true)]
        [string]$DataSetId,
        [parameter(Mandatory=$true)]
        [string]$TableName,
        [parameter(Mandatory=$true)]
        [array]$Rows
    )

    $rows = "{'rows': " + (ConvertTo-Json $Rows) + "}"
    $result = Invoke-RestMethod -Method Post -Uri ($PowerBIBaseAddress + "datasets/$DataSetId/tables/$TableName/rows") -Headers $PowerBIheader -Body $rows
    return $result.id
}

# https://msdn.microsoft.com/en-us/library/mt238041.aspx
function Remove-PowerBIRows{
<#
 .SYNOPSIS
 Removes all Rows from PowerBI table.

 .DESCRIPTION
 The Remove-PowerBIRows cmdlet lets you remove rows from PowerBI table.

 .parameter DataSetId
 DataSet Id which the table resides.

 .parameter TableName
 Table Name to delete rows.

 .EXAMPLE
 Remove-PowerBIRows -DataSetId 4b644350-f745-48dd-821c-f008350199a8 -TableName Table1

 OK.
#>

    PARAM(
        [parameter(Mandatory=$true)]
        [string]$DataSetId,
        [parameter(Mandatory=$true)]
        [string]$TableName
    )
    
    try
    {
        Invoke-RestMethod -Method Delete -Uri ($PowerBIBaseAddress + "datasets/$DataSetId/tables/$TableName/rows") -Headers $PowerBIheader
    }
    catch
    {
        Write-Warning "Falied to delete rows."
    }
}

# Group Operations
# https://msdn.microsoft.com/en-us/library/mt243842.aspx
function Get-PowerBIGroups{
<#
 .SYNOPSIS
 Gets all PowerBI groups.

 .DESCRIPTION
 The Get-PowerBIGroups cmdlet lets you retrieve PowerBI groups.

 .EXAMPLE
 Get-PowerBIGroups
 id name
 -- ----
 ce88923a-b885-4d11-997a-a240e73fb6b5 PowerBIGroup

 This example gets groups which current user belongs to.
#>


    $result = Invoke-RestMethod -Method Get -Uri ($PowerBIBaseAddress + "groups") -Headers $PowerBIheader
    return $result.value
}

# Other Util functions
function New-PowerBIDataSet{
<#
 .SYNOPSIS
 Creates New PowerBI dataset object.

 .DESCRIPTION
 The New-PowerBIDataSet cmdlet lets you create New PowerBI dataset object.

 .parameter DataSetName
 DataSet Name.

 .parameter Tables
 An array of Table objects

 .EXAMPLE
 PS C:\>$col1 = New-PowerBIColumn -ColumnName ID -ColumnType Int64
 PS C:\>$col2 = New-PowerBIColumn -ColumnName Data -ColumnType String
 PS C:\>$table1 = New-PowerBITable -TableName SampleTable1 -Columns $col1,$col2
 PS C:\>
 PS C:\>$col3 = New-PowerBIColumn -ColumnName ID -ColumnType Int64
 PS C:\>$col4 = New-PowerBIColumn -ColumnName Date -ColumnType DateTime
 PS C:\>$col5 = New-PowerBIColumn -ColumnName Detail -ColumnType String
 PS C:\>$col6 = New-PowerBIColumn -ColumnName Result -ColumnType Double
 PS C:\>$table2 = New-PowerBITable -TableName SampleTable2 -Columns $col3,$col4,$col5,$col6
 PS C:\>
 PS C:\>$dataset = New-PowerBIDataSet -DataSetName SampleDataSet -Tables $table1,$table2
 PS C:\>
 PS C:\>Add-PowerBIDataSet -DataSet $dataset

 This example instantiate a table with two columns and another table with four columns, and instantiate a dataset.
 Then, it creates the dataset in PowerBI.
#>

    PARAM(
        [parameter(Mandatory=$true)]
        [string]$DataSetName,
        [parameter(Mandatory=$true)]
        [array]$Tables
    )

    return "{'name': '$DataSetName', 'tables': [" + ($Tables -join ",") + "]}"
}

function New-PowerBITable{
<#
 .SYNOPSIS
 Creates New PowerBI table object.

 .DESCRIPTION
 The New-PowerBITable cmdlet lets you create New PowerBI table object.

 .parameter TableName
 Table Name.

 .parameter Columns
 An array of Column objects.

 .EXAMPLE
 PS C:\>$col1 = New-PowerBIColumn -ColumnName ID -ColumnType Int64
 PS C:\>$col2 = New-PowerBIColumn -ColumnName Data -ColumnType String
 PS C:\>$table1 = New-PowerBITable -TableName SampleTable1 -Columns $col1,$col2
 PS C:\>
 PS C:\>$col3 = New-PowerBIColumn -ColumnName ID -ColumnType Int64
 PS C:\>$col4 = New-PowerBIColumn -ColumnName Date -ColumnType DateTime
 PS C:\>$col5 = New-PowerBIColumn -ColumnName Detail -ColumnType String
 PS C:\>$col6 = New-PowerBIColumn -ColumnName Result -ColumnType Double
 PS C:\>$table2 = New-PowerBITable -TableName SampleTable2 -Columns $col3,$col4,$col5,$col6
 PS C:\>
 PS C:\>$dataset = New-PowerBIDataSet -DataSetName SampleDataSet -Tables $table1,$table2
 PS C:\>
 PS C:\>Add-PowerBIDataSet -DataSet $dataset

 This example instantiate a table with two columns and another table with four columns, and instantiate a dataset.
 Then, it creates the dataset in PowerBI.
#>

    PARAM(
        [parameter(Mandatory=$true)]
        [string]$TableName,
        [parameter(Mandatory=$true)]
        [array]$Columns
    )

    return "{'name': '$TableName', 'columns': [" + ($Columns -join ",") + "]}"
}

function New-PowerBIColumn{
<#
 .SYNOPSIS
 Creates new PowerBI column object.

 .DESCRIPTION
 The New-PowerBIColumn cmdlet lets you create new PowerBI column object.

 .parameter ColumnName
 A column name.

 .parameter ColumnType
 A type of the column. Type can be String, Int64, DateTime, Boolean, or Double

 .EXAMPLE
 PS C:\>$col1 = New-PowerBIColumn -ColumnName ID -ColumnType Int64
 PS C:\>$col2 = New-PowerBIColumn -ColumnName Data -ColumnType String
 PS C:\>$table1 = New-PowerBITable -TableName SampleTable1 -Columns $col1,$col2
 PS C:\>
 PS C:\>$col3 = New-PowerBIColumn -ColumnName ID -ColumnType Int64
 PS C:\>$col4 = New-PowerBIColumn -ColumnName Date -ColumnType DateTime
 PS C:\>$col5 = New-PowerBIColumn -ColumnName Detail -ColumnType String
 PS C:\>$col6 = New-PowerBIColumn -ColumnName Result -ColumnType Double
 PS C:\>$table2 = New-PowerBITable -TableName SampleTable2 -Columns $col3,$col4,$col5,$col6
 PS C:\>
 PS C:\>$dataset = New-PowerBIDataSet -DataSetName SampleDataSet -Tables $table1,$table2
 PS C:\>
 PS C:\>Add-PowerBIDataSet -DataSet $dataset

 This example instantiate a table with two columns and another table with four columns, and instantiate a dataset.
 Then, it creates the dataset in PowerBI.
#>

    PARAM(
        [parameter(Mandatory=$true)]
        [string]$ColumnName,
        [parameter(Mandatory=$true)]
        [ValidateSet("String","Int64","DateTime","Boolean","Double")]
        [string]$ColumnType
    )
    
    return "{'name':'$ColumnName','dataType':'$ColumnType'}"
}