Forum Discussion
expression.error when importing json data with variable content into powerbi table
Hi people!
I'm very new to PowerBi but I already like it a lot! Just tried my first query in this new tool but bumped into an issue for which I don't have an immidiate solution for. A first glance into the existing questions didn't really helped me out.
So, what I would like to have in this specific query is an overview of all our processes with their metadata.
To get this, I have to use 2 web calls (get data from web) which gives back json files.
- get a list of all successful processes : https://blabla.../processes?status=SUCCEEDED --> list of processID’s
- get a list of all metadata for each successful process : https://blabla.../processes/e3a3da17-8dbb-493b-91a0-c3da8fe1a4b1 --> last part ‘e3a3da17-8dbb-493b-91a0-c3da8fe1a4b1’ is processID
I created a function ‘GetMetaData’ which uses the processID column from my first query and use that to perform second query:
(processId as text) as table=>
let
Source = Json.Document(Web.Contents("https://blablabla.../processes/"&processId&"")),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"process-id", type text}, {"description", type text}, {"metadata", type any}, {"status", type text}, {"requested-time", type datetime}, {"started-time", type datetime}, {"ended-time", type datetime}, {"blocked-time", type duration}, {"cost", type any}, {"failures", Int64.Type}, {"process-arguments", type any}, {"resources-arguments", type any}, {"_links", type any}})
in
#"Changed Type"
Where I am stuck now is an error message I get when performing this function. At record 25 we apparently have a process without a description defined in our data. So the result table just stops at this record and gives the error message
Expression.Error: The column 'description' of the table wasn't found.
Details:
description
Is there a way to overcome emty blocks in my source json file?
And how can I make sure I can refresh my result table (make new api calls) without issues?
This is an example of the second API call (json format)
{
- "process-id": "97354810-3300-411a-9a21-dc8220a2ceb2",
- "description": "trunk",
- "metadata": {
- "jira-href": "https://jira/issue/1717782"
},
- "status": "ABORTED",
- "requested-time": "2017-09-18T04:31:00.078Z",
- "started-time": "2017-09-18T04:49:38.542Z",
- "ended-time": "2017-09-18T18:50:45.278Z",
- "blocked-time": "PT2H17M54.948S",
- "failures": 37,
- "process-arguments": {
- "productFamily": "productx",
- "makePcontrolSnapshot": false,
- "product": {
- "productVersion": "99495562",
- "journalVersion": 99495562,
- "location": "s3://${productVersion}/",
- "productMinorVersion": 1,
- "baseline": "b3",
- "productName": "b3"
},
- "productVersion": 1,
- "pdsSnapshotLocation": "s3a:ca/",
- "coreDburl": "http://internal-main-ws",
- "splitRegion": "WO",
- "noTerminate": false,
- "yardServiceUrl": "http://webservice",
- "overwritePdsSnapshot": true
},
- "resources-arguments": {
- "PdsMnrEmrCoreInstanceType": "4xlarge",
- "PdsMnrEmrTerminateAfterIdleMinutes": 500,
- "PControlEcsClusterInstanceType": "016.00-GB",
- "ServicesEcsClusterDesiredInstances": 13,
- "PControlEcsClusterDesiredInstances": 8,
- "PdsMnrEmrCoreCount": 30,
- "ZoningServiceDesiredServiceCount": 8,
- "WebControlDesiredServiceCount": 3,
- "PdsMnrEmrEbsVolumeSize": 600
},
- "_links": {
- "self": {
},
- "process-definition": {
},
- "resources": {
- "href": "https://b-070f4461c9a4"
}
- }
}
In step #"Changed Type", you still refer to #"Promoted Headers". This must be adjusted to your new step: SelectedColumns.
7 Replies
After step #"Promoted Headers", you can add a step in which you select the columns you require, like:
SelectedColumns = Table.SelectColumns(#"Promoted Headers", {"Column Name1", "Column Name 2", etc.}, MissingField.UseNull)
The argument MissingField.UseNull will create columns with null values for columns in the list of column names, that are not present in the table.
Hi Marcel, thanks for your quick reply!
I tried your proposal right away like this:
= (processId as text) as table=>
let
Source = Json.Document(Web.Contents("https://blablabla.../processes/"&processId&"")),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
SelectedColumns = Table.SelectColumns(#"Promoted Headers", {"process-id", "description", "metadata", "status", "requested-time", "started-time", "ended-time", "blocked-time", "cost", "failures", "process-arguments", "resources-arguments", "_links"}, MissingField.UseNull),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"process-id", type text}, {"description", type text}, {"metadata", type any}, {"status", type text}, {"requested-time", type datetime}, {"started-time", type datetime}, {"ended-time", type datetime}, {"blocked-time", type duration}, {"cost", type any}, {"failures", Int64.Type}, {"process-arguments", type any}, {"resources-arguments", type any}, {"_links", type any}})
in
#"Changed Type"but when I invoke the function with 1 processid which does not have a cost value I get this:
An error occurred in the ‘’ query. Expression.Error: The column 'cost' of the table wasn't found.
Details:
costSo, it doesn't seem to resolve my issue I guess? Or am I missing something?
thanks in advance!
Muki
Hi Marcel, thanks for your quick reply!
I tried your proposal right away like this:
= (processId as text) as table=>
let
Source = Json.Document(Web.Contents("https://blablabla.../processes/"&processId&"")),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
SelectedColumns = Table.SelectColumns(#"Promoted Headers", {"process-id", "description", "metadata", "status", "requested-time", "started-time", "ended-time", "blocked-time", "cost", "failures", "process-arguments", "resources-arguments", "_links"}, MissingField.UseNull),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"process-id", type text}, {"description", type text}, {"metadata", type any}, {"status", type text}, {"requested-time", type datetime}, {"started-time", type datetime}, {"ended-time", type datetime}, {"blocked-time", type duration}, {"cost", type any}, {"failures", Int64.Type}, {"process-arguments", type any}, {"resources-arguments", type any}, {"_links", type any}})
in
#"Changed Type"but when I invoke the function with 1 processid which does not have a cost value I get this:
An error occurred in the ‘’ query. Expression.Error: The column 'cost' of the table wasn't found.
Details:
costSo, it doesn't seem to resolve my issue I guess? Or am I missing something?
thanks in advance!
Muki
Hi Marcel, thanks for your quick reply!
I tried your proposal right away like this:
= (processId as text) as table=>
let
Source = Json.Document(Web.Contents("https://blablabla.../processes/"&processId&"")),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
SelectedColumns = Table.SelectColumns(#"Promoted Headers", {"process-id", "description", "metadata", "status", "requested-time", "started-time", "ended-time", "blocked-time", "cost", "failures", "process-arguments", "resources-arguments", "_links"}, MissingField.UseNull),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"process-id", type text}, {"description", type text}, {"metadata", type any}, {"status", type text}, {"requested-time", type datetime}, {"started-time", type datetime}, {"ended-time", type datetime}, {"blocked-time", type duration}, {"cost", type any}, {"failures", Int64.Type}, {"process-arguments", type any}, {"resources-arguments", type any}, {"_links", type any}})
in
#"Changed Type"
but when I invoke the function with 1 processid which does not have a cost value I get this:
An error occurred in the ‘’ query. Expression.Error: The column 'cost' of the table wasn't found.
Details:
cost
So, it doesn't seem to resolve my issue .... I'm sure I'm missing something :/
thanks in advance!
MukiIn step #"Changed Type", you still refer to #"Promoted Headers". This must be adjusted to your new step: SelectedColumns.
