Format JSON Output to Readable Text

PowerShell Help
1

Hello,

I have the following PS line that outputs to JSON format:

[pre]

$RefreshHistoryURL = ‘groups/’ + ‘workspace_id’ + ‘/datasets/’ + ‘dataset_id’ + ‘/refreshes’

Invoke-PowerBIRestMethod -Url $RefreshHistoryURL -Method Get | ConvertTo-Json

[/pre]

There’s a little more to it than that, but this covers the gist of things.

Any ideas on how I can make the JSON output readable? I tried a few things, but it’s not readable.

Thanks,

Frank

2

If I use the following variable and the ‘ConvertFrom-Json’ cmdlet, it will display in a table format:

[pre]

$j = Invoke-PowerBIRestMethod -Url $RefreshHistoryURL -Method Get | ConvertFrom-Json

[/pre]

The output is two columns, where the ‘value’ column has all the data I need. Now I’m unsure how to manipulate that column to pull the info.

3

What exactly does ‘readable’ mean? My assumption is that Invoke-PowerBiRestMethod is a wrapper for Invoke-RestMethod. When Invoke-RestMethod receives a JSON response, it will automatically convert JSON to a Powershell object and then you are converting it back to JSON. If you are simply wanting to parse JSON to look a specific way or get something specific, then post an example of the JSON you are receiving, what is you are seeing and what the expected outcome would be.

4

Yes, I want to parse the output being returned.

Here’s an example:

[pre]

@odata.context value

http://wabi-us-redirect.analysis.windows.net/v1.0/myorg/groups/29-b01e-597c030859c6/$metadata#refreshes {@{id=911995; refreshType=Scheduled; startTime=2020-01-29T14:02:10.947Z; endTime=2020-01-29T14:11:34.027Z; status=Com…

[/pre]

The values I need are under the ‘value’ column.

I was able to run the following to output to a table format:

[pre]

$j = Invoke-PowerBIRestMethod -Url $RefreshHistoryURL -Method Get | ConvertFrom-Json

$data = $j.value | ft

[/pre]

Thanks!

5

I still have more to do with this, and I’m going a different route. I have to report the refresh history of PowerBI datasets. PowerBI has multiple workspaces, which in turn have multiple datasets. We want to grab the refresh history of the datasets, but we need to use the invoke REST API method to pull the data.

In order to do so, you need to grab the Workspace ID, then each Dataset ID under that workspace. The code I have pulls the datasets from 1 workspace and builds the URL needed to use in the invoke-powerbirestmethod:

[pre]

$GetWorkspace = Get-PowerBIWorkspace -Id a1009c4e-3a7b-4629-b01

$GetDataset = Get-PowerBIDataset -WorkspaceId a1009c4e-3a7b-4629-b01

$vDatasets = $GetDataset.Id

create array to store output

$OutputArr = @()

displays each dataset

$findDatasets = ForEach ($i in $vDatasets )
{
$RefreshHistoryURL = ‘groups/’ + ‘a1009c4e-3a7b-4629-b01’ + ‘/datasets/’ + $i + ‘/refreshes’
#Write-Host ($i)
$output = $OutputObj = New-Object -TypeName PSobject
$outputObj | Add-Member -MemberType NoteProperty -Name URL -Value $RefreshHistoryURL

$OutputArr += $OutputObj
Write-Verbose $OutputObj

if ($OutputArr.Count -gt 0) {
Write-Host($jOutput = Invoke-PowerBIRestMethod -Url $RefreshHistoryURL -Method Get | ConvertFrom-Json)
}
else {
Write-Host(“0”)
}
}

[/pre]

The first part of the loop works correctly and the output displays the URL needed for each dataset. However, I need to grab those URLs and build a command such as:

[pre]

$jOutput = Invoke-PowerBIRestMethod -Url $RefreshHistoryURL -Method Get | ConvertFrom-Json

[/pre]

For some reason, I’m having difficulty building this string for each dataset. Once each string is built, then I need to capture the values for the refresh history.

Thanks for any help or advice.

Frank

6

Try something like this:

$id = 'a1009c4e-3a7b-4629-b01'
$Workspace = Get-PowerBIWorkspace -Id $id
$dataset = Get-PowerBIDataset -WorkspaceId $id

$results = foreach ( $ds in ($dataset | Select-Object -ExpandProperty Id) ) {
    $RefreshHistoryURL = 'groups/{0}/datasets/{1}/refreshes' -f $id, $ds
    Invoke-PowerBIRestMethod -Url $RefreshHistoryURL -Method Get
}
7

The loop you wrote only returns the refresh history for 1 dataset instead of the 4 under that workspace. It returns the necessary values, but many workspaces have multiple datasets.

8

I am not testing the code, just providing suggestions. If this were in a function, I’d use Write-Verbose and Write-Host to debug and see that it is looping through the datasets. I’d also see what is returned from the Invoke and if it was similar to your 3rd post, you’d need to get the value of that response. If there are multiple workspaces that have multiple datasets then it would be something like this:

$id = 'a1009c4e-3a7b-4629-b01'
$Workspace = Get-PowerBIWorkspace -Id $id

$results = foreach ( $ws in ($Workspace | Select-Object -ExpandProperty Id ) ) {

    $dataset = Get-PowerBIDataset -WorkspaceId $ws

    foreach ( $ds in ($dataset | Select-Object -ExpandProperty Id) ) {
        Write-Host 'Processing dataset {0}' -f $ds
        $RefreshHistoryURL = 'groups/{0}/datasets/{1}/refreshes' -f $id, $ds
        $response = Invoke-PowerBIRestMethod -Url $RefreshHistoryURL -Method Get

        $response.value
    }
}

$results
9

Convertto-json has a default depth of 2.