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 v...
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

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"