使用PowerBI API更新Azure Blob Storage数据源时遇参数错误求助

问题背景 #

我有多个存储账户，每个账户存储相似但来自不同环境的数据，需要自动为每个账户生成报表。现有一个包含数据转换和可视化效果的PBIX文件，通过PowerShell调用Power BI API基于该文件创建多份报表（此步骤可正常运行），但切换报表关联数据集的数据源到新存储账户时遇到问题，收到模糊错误：

Parameter UpdateDetails is missing or invalid.

已通过GET方法反向验证，认为UpdateDetails请求payload是有效的，但仍无法解决问题。

执行代码 #

# Path to my pbix file.
$pbixPath = "C:\path\to\my\demo.pbix"

# List of storage accounts with valid sas tokens.
$storageAccounts = @(
    @{"AccountName"= "account1"; "SasToken"="validSas"},
    @{"AccountName"= "account2"; "SasToken"="validSas"},
    @{"AccountName"= "account3"; "SasToken"="validSas"}
)

# Get the workspace
$workspaceObject = ( Get-PowerBIWorkspace -Name "my workspace" )
$groupid = $workspaceObject.id

foreach($storageAccount in $storageAccounts){
    $reportName = $storageAccount.AccountName.Replace("reporting", "")
    Write-Host "Publishing $reportName"

    # Publish the report based on our pbix file.
    $result = New-PowerBIReport -Path $pbixPath -Name $reportName -Workspace $workspaceObject -ConflictAction CreateOrOverwrite

    # Get the dataset associated with the newly published report.
    $dataset = Get-PowerBIDataset -Workspace $workspaceObject | Where-Object {$_.Name -eq $reportName}
    $datasetid = $dataset.id


    # Getting the storage account name currently used by the report's dataset.
    # This account is inherited from the pbix file and needs to be overwritten with $storageAccount.AccountName.
    $DefaultStorageAccount = (Invoke-PowerBIRestMethod -Method GET -Url "datasets/$DataSetId/datasources" | convertfrom-json).value.connectionDetails.account

    # Preparing the body of our request to point to a new storage account
    $body = @"
    {
      "updateDetails": [
        {
          "datasourceSelector": {
            "datasourceType": "AzureBlobs",
            "connectionDetails": {
              "account": "$DefaultStorageAccount",
              "domain": "blob.core.windows.net"
            }
          },
          "connectionDetails": {
            "account": "$($storageAccount.AccountName)",
            "domain": "blob.core.windows.net"
          }
        }
      ]
    }
    "@

    # Attempting to point the dataset to a new storage account. This is where the error is thrown.
    Invoke-PowerBIRestMethod -Url "datasets/$DataSetId/Default.UpdateDatasources" -Method Post -Body $body
}

解决方案 #

1. 修复JSON格式问题 #

PowerShell Here-String可能引入多余前导空格，导致JSON解析失败。改用PowerShell对象转JSON的方式生成请求体，确保格式标准：

# 构建updateDetails对象
$updateDetails = @(
    @{
        datasourceSelector = @{
            datasourceType = "AzureBlobs"
            connectionDetails = @{
                account = $DefaultStorageAccount
                domain = "blob.core.windows.net"
            }
        }
        connectionDetails = @{
            account = $storageAccount.AccountName
            domain = "blob.core.windows.net"
        }
    }
)

# 转换为JSON，指定深度避免嵌套截断
$body = @{ updateDetails = $updateDetails } | ConvertTo-Json -Depth 5

2. 补充工作区ID到API URL #

针对工作区中的数据集，API URL必须包含groups/{groupId}/前缀，否则API无法定位正确的数据集：

$apiUrl = "groups/$groupid/datasets/$DataSetId/Default.UpdateDatasources"

3. 添加Content-Type请求头 #

显式指定请求内容类型为application/json，确保API正确解析请求体：

Invoke-PowerBIRestMethod -Url $apiUrl -Method Post -Body $body -Headers @{"Content-Type" = "application/json"}

4. 验证数据源选择器准确性 #

输出$DefaultStorageAccount的值，确认其与原始数据集的存储账户名称完全匹配（注意大小写和拼写）：

Write-Host "原始存储账户名称：$DefaultStorageAccount"

修正后的关键代码片段 #

# 获取原始存储账户（补充工作区ID到API路径）
$DefaultStorageAccount = (Invoke-PowerBIRestMethod -Method GET -Url "groups/$groupid/datasets/$DataSetId/datasources" | ConvertFrom-Json).value.connectionDetails.account
Write-Host "原始存储账户：$DefaultStorageAccount"

# 构建标准JSON请求体
$updateDetails = @(
    @{
        datasourceSelector = @{
            datasourceType = "AzureBlobs"
            connectionDetails = @{
                account = $DefaultStorageAccount
                domain = "blob.core.windows.net"
            }
        }
        connectionDetails = @{
            account = $storageAccount.AccountName
            domain = "blob.core.windows.net"
        }
    }
)
$body = @{ updateDetails = $updateDetails } | ConvertTo-Json -Depth 5

# 执行更新请求
$apiUrl = "groups/$groupid/datasets/$DataSetId/Default.UpdateDatasources"
Invoke-PowerBIRestMethod -Url $apiUrl -Method Post -Body $body -Headers @{"Content-Type" = "application/json"}

内容的提问来源于stack exchange，提问作者Tim Groothuis