Forum Discussion
expression.error when importing json data with variable content into powerbi table
In step #"Changed Type", you still refer to #"Promoted Headers". This must be adjusted to your new step: SelectedColumns.
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
In step #"Changed Type", you still refer to #"Promoted Headers". This must be adjusted to your new step: SelectedColumns.
Can't believe it justed worked! Great :-) It's much appreciated Marcel!
Grtz
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"