Problem Statement

Setting up a Power BI Streaming DataSet in the portal can be a cumbersome task prone to errors due to misspelling and incorrect casing.

In a recent customer project, I encountered this challenge and developed a PowerShell script to automate and streamline the process.

Automate Power BI DataSet Creation With PowerShell


param(
    [Parameter(Mandatory=$true)]
    [string]$newWorkspaceName,
    [Parameter(Mandatory=$true)]
    [string]$user,
    [Parameter(Mandatory=$true)]
    [SecureString]$password
)

# User credentials for
$credential = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $user, $password

# Connect to AAD
$azureProfile = Connect-AzAccount -Credential $credential

# Get an AccessToken to the Power BI service
$accessTokenDetails = Get-AzAccessToken -ResourceUrl 'https://analysis.windows.net/powerbi/api' -DefaultProfile $azureProfile

$headers = @{
    'Authorization' = "Bearer $($accessTokenDetails.Token)"
}

# Create a new workspace

$workspaceName = "Workspace $newWorkspaceName"

$url = "https://api.powerbi.com/v1.0/myorg/groups?workspaceV2=true"
$body = '{
    "name": "' + $workspaceName + '"
}'
$response = Invoke-RestMethod -Method 'Post' -Headers $headers -Uri $url -Body $body -ContentType 'application/json'

if ($null -ne $response.error) {
    Write-Host "Error creating workspace: $($response.error.code) - $($response.error.message)"
    exit
}

$workspaceId = $response.id
Write-Host "Workspace created: $($workspaceId)"

# Create Internal dataset
$url = "https://api.powerbi.com/v1.0/myorg/groups/"+ $workspaceId + "/datasets"
$body = '{
    "name": "' + $newWorkspaceName + ' (internal)",
    "defaultMode": "Streaming",
    "tables": [
        {
            "name": "roll",
            "columns": [
                {"name": "timestamp", "dataType": "DateTime" },
                {"name": "field1", "dataType": "String" },
                {"name": "field2", "dataType": "Int64" },
                {"name": "field3", "dataType": "Int64" }
            ]
        }
    ]
}'
$response = Invoke-RestMethod -Method 'Post' -Headers $headers -Uri $url -Body $body -ContentType 'application/json'
$internalDataSetId = $response.id
Write-Host "Internal dataset created: $($internalDataSetId)"

Summarize

You can utilize this code to automate and replicate the creation of Power BI Datasets directly from the command line, eliminating the need for manual intervention.