Forum Discussion

LJonesStripe's avatar
LJonesStripe
Regular Visitor
1 year ago
Solved

Update Dataset Connections using Power BI APIs

I am trying to update the connection of a Power BI dataset after uploading the Power BI report. I have tested the connection and it works when I manually select the connection through the GUI, but I...
Dataflow
  • FarhanJeelani's avatar
    FarhanJeelani
    1 year ago

    Your JSON request body should look like this:

     

    {
"updateDetails": [
{
"datasourceType": "Sql",
"connectionDetails": {
"database": "NAME_OF_DATABASE",
"server": "NAME_OF_SERVER.database.windows.net"
},
"datasourceId": "XYZ123"
}
]
}

     

     For cloud sources like Azure SQL Database, leave this field empty or remove it. The API does not require a gateway for cloud connections.

     

    PowerShell Script:

     

    # Parameters
$workspaceId = "YOUR_WORKSPACE_ID"
$datasetId = "YOUR_DATASET_ID"
$url = "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/datasets/$datasetId/Default.UpdateDatasources"

# Connection Details
$ConnectionDetailsObject = @{
    updateDetails = @(
        @{
            datasourceType = "Sql"
            connectionDetails = @{
                database = "NAME_OF_DATABASE"
                server = "NAME_OF_SERVER.database.windows.net"
            }
        }
    )
}

# Convert to JSON and Make the API Call
Invoke-PowerBIRestMethod -Url $url -Method Post -Body ($ConnectionDetailsObject | ConvertTo-Json -Depth 10) -ContentType "application/json"

     

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

FarhanJeelani's avatar
FarhanJeelani
Super User
1 year ago

Your JSON request body should look like this:

 

{
"updateDetails": [
{
"datasourceType": "Sql",
"connectionDetails": {
"database": "NAME_OF_DATABASE",
"server": "NAME_OF_SERVER.database.windows.net"
},
"datasourceId": "XYZ123"
}
]
}

 

 For cloud sources like Azure SQL Database, leave this field empty or remove it. The API does not require a gateway for cloud connections.

 

PowerShell Script:

 

# Parameters
$workspaceId = "YOUR_WORKSPACE_ID"
$datasetId = "YOUR_DATASET_ID"
$url = "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/datasets/$datasetId/Default.UpdateDatasources"

# Connection Details
$ConnectionDetailsObject = @{
    updateDetails = @(
        @{
            datasourceType = "Sql"
            connectionDetails = @{
                database = "NAME_OF_DATABASE"
                server = "NAME_OF_SERVER.database.windows.net"
            }
        }
    )
}

# Convert to JSON and Make the API Call
Invoke-PowerBIRestMethod -Url $url -Method Post -Body ($ConnectionDetailsObject | ConvertTo-Json -Depth 10) -ContentType "application/json"

 

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

LJonesStripe's avatar
LJonesStripe
Regular Visitor
1 year ago

Hi, thanks for your response. I get the following error message:

Invoke-PowerBIRestMethod: 
Line |
6 | Invoke-PowerBIRestMethod -Url $url -Method Post -Body ($Connectio …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| One or more errors occurred. ({
"code": "InvalidRequest",
"message": "The given connection details for selector # are invalid"
})
Invoke-PowerBIRestMethod: 
Line |
6 | Invoke-PowerBIRestMethod -Url $url -Method Post -Body ($Connectio …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Encountered errors when invoking the command: {
"code": "InvalidRequest",
"message": "The given connection details for selector # are invalid"
}

I have verified by printing out the JSON that the ConnectionDetialsObject is formatted as expected.