How to add Identity column on PowerShell Result

%3CLINGO-SUB%20id%3D%22lingo-sub-2272323%22%20slang%3D%22en-US%22%3EHow%20to%20add%20Identity%20column%20on%20PowerShell%20Result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2272323%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20executed%20below%20command%20and%20data%20has%20been%20captured.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%24Workspaces%20%3D%20Get-PowerBIWorkspace%3C%2FP%3E%3CP%3E%24Workspaces%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOutput%20is%20shown%20as%20below%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23800000%22%3EId%20%3A%205eyh4u7e-11e1-4c53-95f9-938fhf7d5af%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EName%20%3A%20WorkSpace-1%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EIsReadOnly%20%3A%20False%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EIsOrphaned%20%3A%20False%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EIsOnDedicatedCapacity%20%3A%20True%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3ECapacityId%20%3A%2097jsuh6u-87hy-4k78-CF45B-C34ff354r41D%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23800000%22%3EId%20%3A%20b3j756b5-r745-3t56-q234-frbrf481a84f7%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EName%20%3A%20Workspace-2%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EIsReadOnly%20%3A%20False%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EIsOrphaned%20%3A%20False%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EIsOnDedicatedCapacity%20%3A%20True%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3ECapacityId%20%3A%2097jsuh6u-87hy-4k78-CF45B-C34ff354r41D%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23000000%22%3EI%20want%20to%20add%20one%20more%20column%20of%20Identity%2C%20sat%20ColA%20%3D1%20etc%20as%20shown%20as%20below%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23333399%22%3EColA%20%3D1%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23800000%22%3EId%20%3A%205eyh4u7e-11e1-4c53-95f9-938fhf7d5af%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EName%20%3A%20WorkSpace-1%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EIsReadOnly%20%3A%20False%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EIsOrphaned%20%3A%20False%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EIsOnDedicatedCapacity%20%3A%20True%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3ECapacityId%20%3A%2097jsuh6u-87hy-4k78-CF45B-C34ff354r41D%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23333399%22%3EColA%20%3D2%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23800000%22%3EId%20%3A%20b3j756b5-r745-3t56-q234-frbrf481a84f7%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EName%20%3A%20Workspace-2%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EIsReadOnly%20%3A%20False%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EIsOrphaned%20%3A%20False%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3EIsOnDedicatedCapacity%20%3A%20True%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23800000%22%3ECapacityId%20%3A%2097jsuh6u-87hy-4k78-CF45B-C34ff354r41D%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23800000%22%3EPlease%20suggest%20how%20to%20achieve%20this.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EThanks%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EAmit%20Srivastava%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2272323%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Contributor

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

6 Replies

@amsrivas28 

 

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
})

}

 

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

@amsrivas28 

 

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
}

 

 

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

 

@amsrivas28 

 

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
}

}

 

 

 

 

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