How to use SQL query data to create a Power BI Workspaces via PowerShell or Rest API Script

[Sania198]

Hi,

1. I want to create a Power BI workspaces by referencing to the view created in our SQL DB.
2. I am successful in pulling the data by connecting to SQL DB where we have a table with DatabaseName, by using that DatabaseName from DB PBI workspaces should be created if the workspace doesn't exists.
3. If the workspace already exists then based on the workspace state like (if the workspace is in DELETE State then it should be activated) or If it is already in ACTIVE state then no operation should be performed.

However, when trying it with below PS Script I am getting errors. Below is the error screenshot 

PowerShell Script I'm using:

$Query = '

SELECT TOP (3) [DatabaseName]

  FROM [dbo].[vw_ListToAddToPBIws]

'

$SQLFile = Invoke-Sqlcmd -ServerInstance $SqlServer -Database $Database -Query $Query -Username $SqlAuthLogin -Password $SqlAuthPw

$SQLFile = Select-Object

Write-Host

Connect-PowerBIServiceAccount | Out-Null

$workspaceName = $SQLFile

$workspace = Get-PowerBIWorkspace -Name $workspaceName

if($workspace) {

  Write-Host "The workspace named $workspaceName already exists" -ForegroundColor Green

}

else {

  Write-Host "Creating new workspace named $workspaceName" -ForegroundColor DarkBlue

  $workspace = New-PowerBIGroup -Name $workspaceName

}

I really appreciate if someone can help me with the above ask.

Thanks in Advance!

[Sania198]

I played with the script and tried to pass Foreach as you said and it resolved the issue for me

Thanks @lbendlin 

[lbendlin · Accepted solution]

$SQLFile returns an object (the table of the rows for your query result). Now you need to run a ForEach on this to address the individual workspaces.

[Sania198]

Thanks for the response @lbendlin 

If possible, can you please help me with the script to use the ForEach loop for the above ask