Invoke-PowerBIRestMethod -Url:" String value not translating to looping values-CodePudding

I am looping through a csv file to set variables. When I try to use my changing variables, I get just the variable name not its value when trying to use in the Invoke-PowerBIRestMethod -Url $url Can someone please help with my issue? I want to change several server names and database values programatically.

$csv = import-csv "C:\Temp\MyTest.csv"

Import-Module MicrosoftPowerBIMgmt
Import-Module MicrosoftPowerBIMgmt.Profile

Connect-PowerBIServiceAccount 

$d = $_.DatasetId
$w = $_.WorkspaceId
$url = "groups/$w/datasets/$d/Default.UpdateDatasources"
$server =  $_.Server

$csv | foreach-object {
     Invoke-PowerBIRestMethod -Url $url -Method Post -Body '{
  "updateDetails": [
    {
      "datasourceSelector": {
        "datasourceType": "Sql",
        "connectionDetails": {
          "server": "sql1",
          "database": "db"
        }
      },
      "connectionDetails": {
        "server": "sql01",
        "database": "Database"
      }
    }
  ]
}'
}

Disconnect-PowerBIServiceAccount

DATA COLS: WorkspaceID, DatasetId, Server

This is not working for me either:

Import-Module MicrosoftPowerBIMgmt
Import-Module MicrosoftPowerBIMgmt.Profile

$url='groups/9e55d5cf-26ec-4016-bfed-000000000/datasets/ffa73aa7-223c-4f73-b084-000000000/Default.UpdateDatasources'
Connect-PowerBIServiceAccount #-Credential $credential
  Invoke-PowerBIRestMethod -Url $($url) -Method Post -Body '{
  "updateDetails": [

Please help.

CodePudding user response：

You need to set the variables inside the foreach loop, and you need to interpolate the $server variable into the request body using a here-string. EG

$csv = import-csv "C:\Temp\MyTest.csv"

Import-Module MicrosoftPowerBIMgmt
Import-Module MicrosoftPowerBIMgmt.Profile

Connect-PowerBIServiceAccount 



$csv | foreach-object {

    $d = $_.DatasetId
    $w = $_.WorkspaceId
    $url = "groups/$w/datasets/$d/UpdateParameters"
    $server =  $_.Server

    $body = @"
{
    "updateDetails": [
        {
            "datasourceSelector": {
                "datasourceType": "Sql",
                "connectionDetails": {
                    "server": "$server",
                    "database": "db"
                }
            },
            "connectionDetails": {
                "server": "$server",
                "database": "Database"
            }
        }
    ]
}
"@
    #$body
    #$url

    
    Invoke-PowerBIRestMethod -Url $url -Method Post -Body $body
}

Disconnect-PowerBIServiceAccount