Forum Discussion

jatpola's avatar
jatpola
Frequent Visitor
5 years ago
Solved

Error handling: JSON field can either be logical or record

Hi, I have a JSON field ("sentiment" in the screenshots ) which can either be "FALSE" or contain a Record.

If it's a Record I'd like to access it, otherwise I need some error handling (return null values, or something like that).

The first two screenshots show the two possible scenarios for the field "sentiment", and the third shows the error I'm getting (in English it would be something like "Can't apply field access to Logical type")

 

 

 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi jatpola 

    You can separate the steps that come after the try ... otherwise into a function and then call it only if sentiment is not null

    let

    ContinueTransforms = (level_1) =>
    let 
        level_2 = level_1[stats],
        level_3 = level_2[metrics],
        level_4 = level_3[score],
        Result = Record.ToTable(level_4)

    in Result,

    Source = API_JSON,
    level_1 = try Source[sentiment] otherwise null,

    #"To Table" = if level_1 is null then null else ContinueTransforms(level_1)
in
    #"To Table"

    Regards

    Phil

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    Hi jatpola 

    In your query you need to add a step like this

    Value = try Record.ToTable(#"PreviousStepName"{10}[Value]) otherwise null

    But this willjust result in null if there is an error (becasue there's no record, just FALSE).

    If you want to still have a table after an erroroccurs use this

    Value = try Record.ToTable(#"PreviousStepName"{10}[Value]) otherwise #"PreviousStepName"

    You'll need to insert the PreviousStepName from your query into these snippets.

    Regards

    Phil

    • jatpola's avatar
      jatpola
      Frequent Visitor
      5 years ago

      Thanks PhilipTreacy , your answer was useful but I still need some help to achieve the desired output.

      After accessing the 'sentiment' record I still perform some steps. (see code)

      How do I avoid trying to perform those steps if "sentiment" doesn't contain data? Do I have to include a "try" & "otherwise" logic in every line of code?

      Thanks a lot

      let
    Source = API_JSON,
    level_1 = try Source[sentiment] otherwise null,
    level_2 = level_1[stats],
    level_3 = level_2[metrics],
    level_4 = level_3[score],
    #"To table" = Record.ToTable(level_4)
in
    #"To table"

       

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    Hi jatpola 

    You can separate the steps that come after the try ... otherwise into a function and then call it only if sentiment is not null

    let

    ContinueTransforms = (level_1) =>
    let 
        level_2 = level_1[stats],
        level_3 = level_2[metrics],
        level_4 = level_3[score],
        Result = Record.ToTable(level_4)

    in Result,

    Source = API_JSON,
    level_1 = try Source[sentiment] otherwise null,

    #"To Table" = if level_1 is null then null else ContinueTransforms(level_1)
in
    #"To Table"

    Regards

    Phil