How to add Identity column on PowerShell Result

Question

Hi Guys!!&nbsp;I have executed below command and data has been captured.&nbsp;$Workspaces = Get-PowerBIWorkspace$Workspaces&nbsp;Output is shown as below&nbsp;&nbsp;Id : 5eyh4u7e-11e1-4c53-95f9-938fhf7d5afName : WorkSpace-1IsReadOnly : FalseIsOrphaned : FalseIsOnDedicatedCapacity : TrueCapacityId : 97jsuh6u-87hy-4k78-CF45B-C34ff354r41D&nbsp;Id : b3j756b5-r745-3t56-q234-frbrf481a84f7Name : Workspace-2IsReadOnly : FalseIsOrphaned : FalseIsOnDedicatedCapacity : TrueCapacityId : 97jsuh6u-87hy-4k78-CF45B-C34ff354r41D&nbsp;I want to add one more column of Identity, sat ColA =1 etc as shown as below&nbsp;ColA =1Id : 5eyh4u7e-11e1-4c53-95f9-938fhf7d5afName : WorkSpace-1IsReadOnly : FalseIsOrphaned : FalseIsOnDedicatedCapacity : TrueCapacityId : 97jsuh6u-87hy-4k78-CF45B-C34ff354r41D&nbsp;ColA =2Id : b3j756b5-r745-3t56-q234-frbrf481a84f7Name : Workspace-2IsReadOnly : FalseIsOrphaned : FalseIsOnDedicatedCapacity : TrueCapacityId : 97jsuh6u-87hy-4k78-CF45B-C34ff354r41D&nbsp;Please suggest how to achieve this.&nbsp;ThanksAmit Srivastava

kevin_morgan · Answer

amsrivas28&nbsp;&nbsp;Try the below commands with&nbsp;&nbsp;Get-PowerBIWorkspace to get the required result&nbsp;$result=@()
$id=0
$processes = Get-Process
ForEach ($process in $processes)
{
$id++
$result+=New-Object PsObject -Property ([ordered]@{
ColA=$id
OtherColumn1=$process.Name
})

}&nbsp;

amsrivas28 · Answer

Hi&nbsp;Kevin_Morgan&nbsp;,I am able to add new Identity column into the Variable&nbsp;$Workspaces however when i tried to pass workspace into another command, it crashes, detail as below.&nbsp;First Part of the PowerShell Command ; works and added new column as desired.$Workspaces=@()$processes = Get-PowerBIWorkspace$id=0ForEach ($process in $processes){$id++$Workspaces+=New-Object PsObject -Property ([ordered]@{Identity=$idid=$process.IdName=$process.Name})}&nbsp;# Second part of the command$array = @()foreach ($workspace in $Workspaces ){$Dataflow = Get-PowerBIDataFlow -Workspace $workspace}&nbsp;Here, when i am passing values from $workspaces (created above) , it throws below error&nbsp;&nbsp;Error as below&nbsp;&nbsp;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&nbsp;&nbsp;Any suggestion&nbsp;on how to get rid of this error.&nbsp;ThanksAmit

kevin_morgan · Answer

amsrivas28&nbsp;&nbsp;Try the below script. The error message indicates that the command can't able to convert the custom ps object into&nbsp;Workspace object. So, we can use the parameter "WorkspaceId" and pass&nbsp;Workspace Id to get only dataflows belonging to that workspace.&nbsp;$array = @()
foreach ($workspace in $Workspaces )
{
$Dataflow = Get-PowerBIDataFlow -WorkspaceId $workspace.id
}&nbsp;&nbsp;

amsrivas28 · Answer

Hi Kevin_Morgan&nbsp;, Thanks for the help.Can understand, am asking too much 😞 but with your help, it seems I will achieve my requirement.&nbsp;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&nbsp;First Part of Code&nbsp; : This is fine 🙂$Workspaces=@()$processes = Get-PowerBIWorkspace$id=0ForEach ($process in $processes){$id++$Workspaces+=New-Object PsObject -Property ([ordered]@{Identity=$idid=$process.IdName=$process.Name})}&nbsp;Second Part of Code&nbsp; : 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"&nbsp;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.&nbsp;Please suggest!!ThanksAmit Srivastava&nbsp;

kevin_morgan · Answer

amsrivas28&nbsp;&nbsp;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&nbsp;&nbsp;temporary array once the count reached to 10.&nbsp;$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}}&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

How to add Identity column on PowerShell Result

Share

Resources