Error handling: JSON field can either be logical or record

[jatpola]

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")

[PhilipTreacy · Accepted solution]

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]

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]

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"