I am working on a process to extract data from Power BI datasets via the REST API
Datasets - Execute Queries - REST API (Power BI Power BI REST APIs) | Microsoft Docs
This works nicely and I can receive the resulting JSON fine, with the rows and columns of the result set coded as needed. However if the result set has blanks in some cells then the JSON will omit those properties (column values) for the affected rows.
{
"results": [
{
"tables": [
{
"rows": [
{
"[Week_Starting]": "12 Dec",
"[Report Date]": "2021-12-08T00:00:00",
"[Amount]": 14
},
{
"[Week_Starting]": "05 Dec",
"[Report Date]": "2021-12-08T00:00:00",
"[Amount]": 88
},
{
"[Report Date]": "2021-12-08T00:00:00",
"[Amount]": 52
},
{
"[Week_Starting]": "Previous",
"[Report Date]": "2021-12-08T00:00:00",
"[Amount]": 41
}
]
}
]
}
]
}
Note that the third row is missing the "[Week_Starting]" property as that data point is indeed BLANK in the Power BI Dataset.
Now I want to push that JSON result to a SQL server table. Unfortunately that server runs on compatibility level 110 so OPENJSON is not available. So my workaround was to ingest the JSON into a powershell PSObject
$rows = $res.Substring(3) | ConvertFrom-Json | Select-Object -Expand results | Select-Object -Expand tables | Select-Object -Expand rows
and then push that PSObject into the SQL table via
$rows | Write-SqlTableData -ServerInstance "server" -DatabaseName "detabase" -SchemaName "dbo" -TableName "table"
This works for all rows except for the row with the blank - it will not get imported.
I am looking for options to either replenish the missing property (for example by patching the PSObject) or to ingest the data anyway. I had thought about changing the DAX query but haven't been successful yet as that particular BLANK would require me to rewrite the entire data model.
Has anyone worked on this topic (fetching query results into SQL Server table) yet?
