Json missing field - existing solutions not working for Record field type

[dapster105]

Hi,

I'm close to giving up on this one and resorting some some raw text manipulation but I hope you can help me to solve this the right way.

I'm bringing in some json over a REST api. The problem I have is that the json structure is sometimes missing fields which are records (nested objects) and all the many varied fixes / workarounds I've tried (like SelectFields or List.Transform) work well if a regular field is missing but not if the missing field is a Record type.

Example json:

{
  "individual_appointments": [
    {
      "id": 1,
      "lotsofotherfields": "stuff",
      "practitioner": {
        "links": {
          "self": "https://api.someapp.com/v1/practitioners/1"
          }
      }
    },
    { 
      "id": 2,
      "lotsofotherfields": "morestuff"
    } 
  ],
}

In the above example, the practitioner field is sometimes missing, causing errors. I've tried solutions based on Record.SelectFields, List.Transform, Record.TransformFields and I can't get rid of the errors (The field 'practitioner' of the record wasn't found) or similar.

Really I want the practitioner->links->self value into the 'individual_appointments' table in place of practitioner if it is present.

I felt my best bet was a transform at the List stage but I just can't get it to work.

I'd be sooooo grateful for a solution to this.

Thanks!

Tim

[v-piga-msft]

Hi @dapster105 ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[v-piga-msft]

Hi @dapster105 ,

More details will be much helpful.

If it is convenient, could you share the screenshot of your error message and your M query which you could get it from Advanced Editor? Please not do mask the sensitive data before uploading.

@ImkeF is good at M query, hope she could have other ideas for your scenario.

Best  Regards,

Cherry

[ImkeF · Accepted solution]

Hi @ojedap ,

not sure what you're actually after here, but this method would replace missing records with a record of your choice before expanding:

let
    Source = "{#(cr)#(lf)  ""individual_appointments"": [#(cr)#(lf)    {#(cr)#(lf)      ""id"": 1,#(cr)#(lf)      ""lotsofotherfields"": ""stuff"",#(cr)#(lf)      ""practitioner"": {#(cr)#(lf)        ""links"": {#(cr)#(lf)          ""self"": ""https://api.someapp.com/v1/practitioners/1""#(cr)#(lf)          }#(cr)#(lf)      }#(cr)#(lf)    },#(cr)#(lf)    { #(cr)#(lf)      ""id"": 2,#(cr)#(lf)      ""lotsofotherfields"": ""morestuff""#(cr)#(lf)    } #(cr)#(lf)  ],#(cr)#(lf)}",
    #"Parsed JSON" = Json.Document(Source),
    individual_appointments = #"Parsed JSON"[individual_appointments],
    #"Converted to Table" = Table.FromList(individual_appointments, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "lotsofotherfields", "practitioner"}, {"id", "lotsofotherfields", "practitioner"}),
    #"Expanded practitioner" = Table.ExpandRecordColumn(#"Expanded Column1", "practitioner", {"links"}, {"links"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded practitioner",null, [self = "SelfIsMissingHere"],Replacer.ReplaceValue,{"links"}),
    #"Expanded links" = Table.ExpandRecordColumn(#"Replaced Value", "links", {"self"}, {"self"})
in
    #"Expanded links"

[dapster105]

Thanks all - apologies I have been on vacation!

Imke your answer contained the solution for me. I am very grateful. The magic function call is:

Table.FromList(individual_appointments, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

I say magic because I have studied the documentation for this function and I literally have no idea why this works, it just does. SplitByNothing() makes no sense to me. Passing null for both the following 2 parameters also seems undocumented magic.

Nevertheless it works and I'm very grateful!

Tim