Unable To Apply Query - OLE DB or ODBC error: [Expression:Error] expected a RenameOperations value

[al358]

When I try to apply this query (see text file) I get the following error:

OLE DB or ODBC error: [Expression:Error] We expected a RenameOperations value

I think the error stems from the below formula:

Table.AddColumn(#"Sorted Rows", "Custom", each let
thisrecord = Json.Document([options_input]),
sortedrecord = Record.ReorderFields(thisrecord, List.Sort(Record.FieldNames(thisrecord))),
renamedrecord = Record.RenameFields(sortedrecord, List.Zip({Record.FieldNames(sortedrecord), {"Storage_Key", "Network_Key", "Condition_Key"}}))
in
renamedrecord)

Any ideas how I can resolve so I can apply my query to my model?

[al358]

@AlexisOlson I think you're onto something there. There are some instances of thisrecord not having 3 fields.

How can I stop this from preventing me from applying my query to my model though?

[AlexisOlson]

Well, if you're missing fields, then it's hard to know how to rename the ones you do have and what to do for the remainder of the query where all three fields are referenced. How should this query work for such cases?

[al358]

Instances where there are not 3 fields are edge cases (years-old entries made under a legacy system, which are unlikely to be included in any analysis).

A cursory glance at the legacy entries suggests there is never fewer than 3 fields, and I'm trying to think of the simplist solution to allow the query to apply to the model, so perhaps always name the first 3 fields as per the current query, and return a null value for subsequent fields?

Would that work? Or can you think of a more elegant solution?

[AlexisOlson · Accepted solution]

I'd probably choose to null all fields if there aren't exactly 3.

Something like this:

renamedrecord =
    if Record.FieldCount(sortedrecord) <> 3
    then [Storage_Key = null, Network_Key = null, Condition_Key = null]
    else Record.RenameFields(sortedrecord, <...existing code here...> )

[al358]

Alexis, you are indeed a Super User and a credit to the community; this worked perfectly! Thank you for your help.

[AlexisOlson]

It's hard to say without being able to see it in action, but if thisrecord has more or less than three fields, then I'd expect the renaming step to fail since you'd get invalid oldname/newname pair(s) from List.Zip.