Apr 14 2021 06:00 AM
Hi Guys!!
I have executed below command and data has been captured.
$Workspaces = Get-PowerBIWorkspace
$Workspaces
Output is shown as below
Id : 5eyh4u7e-11e1-4c53-95f9-938fhf7d5af
Name : WorkSpace-1
IsReadOnly : False
IsOrphaned : False
IsOnDedicatedCapacity : True
CapacityId : 97jsuh6u-87hy-4k78-CF45B-C34ff354r41D
Id : b3j756b5-r745-3t56-q234-frbrf481a84f7
Name : Workspace-2
IsReadOnly : False
IsOrphaned : False
IsOnDedicatedCapacity : True
CapacityId : 97jsuh6u-87hy-4k78-CF45B-C34ff354r41D
I want to add one more column of Identity, sat ColA =1 etc as shown as below
ColA =1
Id : 5eyh4u7e-11e1-4c53-95f9-938fhf7d5af
Name : WorkSpace-1
IsReadOnly : False
IsOrphaned : False
IsOnDedicatedCapacity : True
CapacityId : 97jsuh6u-87hy-4k78-CF45B-C34ff354r41D
ColA =2
Id : b3j756b5-r745-3t56-q234-frbrf481a84f7
Name : Workspace-2
IsReadOnly : False
IsOrphaned : False
IsOnDedicatedCapacity : True
CapacityId : 97jsuh6u-87hy-4k78-CF45B-C34ff354r41D
Please suggest how to achieve this.
Thanks
Amit Srivastava
Apr 15 2021 12:58 AM
Try the below commands with Get-PowerBIWorkspace to get the required result
$result=@()
$id=0
$processes = Get-Process
ForEach ($process in $processes)
{
$id++
$result+=New-Object PsObject -Property ([ordered]@{
ColA=$id
OtherColumn1=$process.Name
})
}
Apr 15 2021 03:31 AM - edited Apr 15 2021 03:46 AM
Hi @Kevin Morgan ,
I am able to add new Identity column into the Variable $Workspaces however when i tried to pass workspace into another command, it crashes, detail as below.
First Part of the PowerShell Command ; works and added new column as desired.
$Workspaces=@()
$processes = Get-PowerBIWorkspace
$id=0
ForEach ($process in $processes)
{$id++
$Workspaces+=New-Object PsObject -Property ([ordered]@{
Identity=$id
id=$process.Id
Name=$process.Name
})}
# Second part of the command
$array = @()
foreach ($workspace in $Workspaces )
{
$Dataflow = Get-PowerBIDataFlow -Workspace $workspace
}
Here, when i am passing values from $workspaces (created above) , it throws below error
Error as below
Get-PowerBIDataflow : Cannot bind parameter 'Workspace'. Cannot convert value "@{Identity=136; id=b2wewe8b5-wew2-4we-awew-awewe81a84f7; Name=PowerBI-Test}" to type "Microsoft.PowerBI.Common.Api.Workspaces.Workspace". Error: "Cannot convert the "@{Identity=136;
id=b2wewe8b5-wew2-4we-awew-awewe81a84f7; Name=PowerBI-Test }" value of type "System.Management.Automation.PSCustomObject" to type
"Microsoft.PowerBI.Common.Api.Workspaces.Workspace"."
At line:18 char:57
+ $Dataflow = Get-PowerBIDataFlow -Workspace $workspace
+ ~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Get-PowerBIDataflow], ParameterBindingException
+ FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerBI.Commands.Data.GetPowerBIDataflow
Any suggestion on how to get rid of this error.
Thanks
Amit
Apr 16 2021 01:37 AM
Try the below script. The error message indicates that the command can't able to convert the custom ps object into Workspace object. So, we can use the parameter "WorkspaceId" and pass Workspace Id to get only dataflows belonging to that workspace.
$array = @()
foreach ($workspace in $Workspaces )
{
$Dataflow = Get-PowerBIDataFlow -WorkspaceId $workspace.id
}
Apr 16 2021 04:01 AM - edited Apr 16 2021 04:04 AM
Hi @Kevin Morgan , Thanks for the help.
Can understand, am asking too much :( but with your help, it seems I will achieve my requirement.
To be precise, your suggestion works and below code is able to retrieve data as required however need your expertise/help to achieve one more requirement related to the same PowerShell Command, detailed as below
First Part of Code : This is fine :)
$Workspaces=@()
$processes = Get-PowerBIWorkspace
$id=0
ForEach ($process in $processes)
{$id++
$Workspaces+=New-Object PsObject -Property ([ordered]@{
Identity=$id
id=$process.Id
Name=$process.Name
})}
Second Part of Code : Requirement is to Iterate FOREACH for 10 records based on IDENTITY column (Identity is a column which is been created in First Part of the code)
$array = @()
foreach ($workspace in $Workspaces)
{
$wspid = $workspace.id
$wspiden = $workspace.Identity
$Dataflow = Get-PowerBIDataFlow -WorkspaceId $wspid
$dflowId = $Dataflow.ID
$array += "$wspiden,$wspid , $dflowId"
}
$body = $array -join "`n"
Actual requirement is to - I need to process records from identity 1 to 10 in one batch, and next records with identity (11-20 ) on another batch and so on. To be precise, I need to come out of the FOREACH after every 10 records.
Please suggest!!
Thanks
Amit Srivastava
Apr 16 2021 05:37 AM - edited Apr 16 2021 05:41 AM
I can't understand your requirement clearly. But what my suggestion is, instead of breaking loop, use the counter variable, temporary array and result array., process 10 items and add the processed items in result array, reset the counter variable and temporary array once the count reached to 10.
$resultArray = @()
$counter=1
$tempArray = @()
foreach ($workspace in $Workspaces)
{
### Your inner loop script ###
$tempArray += "$wspiden,$wspid , $dflowId"
if($counter -eq 10)
{
#Add temp array in result array
$resultArray += $tempArray
#Reset counter and temp array
$array = @()
$counter=1
}
}
Apr 16 2021 09:18 AM
Hi @Kevin Morgan ,
Let me explain more on requirement.
There is one limitation on PowerShell command, i.e. when passing more than 200 record in a FOREACH loop, loop will crash, specifically for few command which will be executed by Power BI Tenant Admin.
So what requires is to move out from the FOREACH loop after 200th records and loop in for another 200 records (201-400) and so on, i,e, to pass records in the batch of 200 records and each time after 200 record, control move from loop.
That's the reason why I have included identity column in the result set so that on the basis of that, I can iterate the same.
But anyhow, I am not able to achieve my requirement.
Hopefully, this will define my requirement more clearer.
Anyway, via which I can do that will be very helpful.
Thanks
Amit