expression.error when importing json data with variable content into powerbi table

[Muki]

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.

1. get a list of all successful processes : https://blabla.../processes?status=SUCCEEDED --> list of processID’s
2. 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": {
• "href": "https://api54810-3300-411a-9a21-dc8220a2ceb2"

},

• "process-definition": {
• "href": "https://apie5-37cd-9475-ad82f59e9959"

},

• "resources": {
• "href": "https://b-070f4461c9a4"

}

• }

}

[MarcelBeug]

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.

[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!

Muki

[MarcelBeug · Accepted solution]

In step #"Changed Type", you still refer to #"Promoted Headers". This must be adjusted to your new step: SelectedColumns.

[Muki]

Hi @MarcelBeug

I would like to use the MissingField.UseNull option in below query but don't  know where exactly to add it and which columns to include.

In the previous example I added this right after 'promoted headers' record, the next line just contained the ultimate columns I wanted in my table. But in this example I removed and renamed a lot of columns so which columns should I list up? Can I insert this function after the promoted headers and list up the current columns in my table?   

thanks in advance!

Muki

let
Source = Json.Document(Web.Contents("......c/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",{"metadata", "status", "requested-time", "started-time", "ended-time"}, MissingField.UseNull),
#"Removed Columns" = Table.RemoveColumns(#"SelectedColumns",{"metadata", "status", "requested-time", "started-time", "ended-time"}),
#"Expanded cost" = Table.ExpandRecordColumn(#"Removed Columns", "cost", {"amount", "currency"}, {"cost.amount", "cost.currency"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded cost",{"resources-arguments"}),
#"Expanded _links" = Table.ExpandRecordColumn(#"Removed Columns1", "_links", {"process-definition"}, {"_links.process-definition"}),
#"Expanded process-arguments" = Table.ExpandRecordColumn(#"Expanded _links", "process-arguments", {"yaml-file"}, {"process-arguments.yaml-file"}),
#"Expanded process-arguments.yaml-file" = Table.ExpandRecordColumn(#"Expanded process-arguments", "process-arguments.yaml-file", {"iterationType", "oldMnrProduct", "mnrProduct"}, {"iterationType", "oldMnrProduct", "mnrProduct"}),
#"Expanded oldMnrProduct" = Table.ExpandRecordColumn(#"Expanded process-arguments.yaml-file", "oldMnrProduct", {"productVersion", "journalVersion", "baseline"}, {"oldMnrProduct.productVersion", "oldMnrProduct.journalVersion", "oldMnrProduct.baseline"}),
#"Expanded mnrProduct" = Table.ExpandRecordColumn(#"Expanded oldMnrProduct", "mnrProduct", {"productVersion", "journalVersion", "baseline", "productName"}, {"mnrProduct.productVersion", "mnrProduct.journalVersion", "mnrProduct.baseline", "mnrProduct.productName"}),
#"Expanded _links.process-definition" = Table.ExpandRecordColumn(#"Expanded mnrProduct", "_links.process-definition", {"href"}, {"processdefinition.href"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded _links.process-definition",{{"mnrProduct.productVersion", "productVersion"}, {"mnrProduct.journalVersion", "journalVersion"}, {"mnrProduct.baseline", "baseline"}, {"mnrProduct.productName", "productName"}, {"oldMnrProduct.productVersion", "productVersion2"}, {"oldMnrProduct.journalVersion", "journalVersion2"}, {"oldMnrProduct.baseline", "baseline2"}})
in
#"Renamed Columns"

[Muki]

Can't believe it justed worked! Great 🙂 It's much appreciated Marcel!

Grtz

Muki

[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 guess? Or am I missing something?

thanks in advance!

Muki

[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 guess? Or am I missing something?

thanks in advance!

Muki