none
Handle null values in JSON arrays - Power Query RRS feed

  • Question

  • Hello,

    I have a following problem - I created a connection to web API in Excel 2016 (with dynamic parameter) and it returning me a proper results, however things starts to be nasty when i expand columns with nested arrays....

    For example i expand a column with nested records, here is example of two columns, one have child records, other dont:

    fields.Organ_Nadzoru || fields.Organ_Nadzoru2

    Record                           null

    and then i do next operations there.

    So as long as fields contain a data it is fine, all my query is successfully loaded. But when i change a parameter in my web API URL and it happens that for particular query this field is null and not a "record", it gives me an error:

    "we cannot apply field access to the type null"

    Well, sounds logical but i need to find a workaround. For example if this field is null, create an empty table instead, just dont break the whole operation...

    Here is my code for the reference:

    let
        Source = Json.Document(Web.Contents("https://rejestr.io/api/v1/krs/"& Excel.CurrentWorkbook(){[Name="id_2"]}[Content]{0}[Column1] &"?chapter=basic", [Headers=[Authorization="xxxxxx"]])),
        #"Converted to Table" = Record.ToTable(Source),
        #"Transposed Table" = Table.Transpose(#"Converted to Table"),
        #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
        #"Expanded address" = Table.ExpandRecordColumn(#"Promoted Headers", "address", {"city", "code", "country", "house_no", "post_office", "street"}, {"address.city", "address.code", "address.country", "address.house_no", "address.post_office", "address.street"}),
        #"Expanded fields" = Table.ExpandRecordColumn(#"Expanded address", "fields", {"wysokosc_kapitalu_zakladowego","organ_nadzoru"}),
        #"fields organ_nadzoru1" = #"Expanded fields"{0}[fields.organ_nadzoru], <---- Here it can be null or nested record.
        _childs = #"fields organ_nadzoru1"[_childs],
        #"1" = _childs[1],
        dane_osob = #"1"[dane_osob],
        _childs1 = dane_osob[_childs],
        #"Converted to Table1" = Record.ToTable(_childs1),
        #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table1", "Value", {"person"}, {"Value.person"}),
        #"Expanded Value.person" = Table.ExpandRecordColumn(#"Expanded Value", "Value.person", {"_value"}, {"Value.person._value"}),
        #"Expanded Value.person._value" = Table.ExpandRecordColumn(#"Expanded Value.person", "Value.person._value", {"name", "person_id"}, {"Value.person._value.name", "Value.person._value.person_id"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Value.person._value",{"Name"})
    in
        #"Removed Columns"

    Many thanks for the answers.

    Wednesday, June 10, 2020 6:15 PM

Answers

  • Hi there. If the value you're indexing into can possibly be null, you can use null indexing. (This works for both list and record indexing, as shown below.)

    #"Expanded fields"{0}?[fields.organ_nadzoru]?

    Ehren

    Thursday, June 11, 2020 5:52 PM
    Owner

All replies

  • I cannot dig in your code now, what you can do is manage the errors. You can use for example:

    if not  (try #"your step#) [HasError] 
    then #"your step"
    else #"your alternative"

    Check the code I suggested here: Multiple/Nested If Then Else statement or recursive function

    Wednesday, June 10, 2020 7:59 PM
  • Hi there. If the value you're indexing into can possibly be null, you can use null indexing. (This works for both list and record indexing, as shown below.)

    #"Expanded fields"{0}?[fields.organ_nadzoru]?

    Ehren

    Thursday, June 11, 2020 5:52 PM
    Owner
  • Hi, thanks for the answers,

    Ehren - that did the job. However, can you further instruct me how to do something similar with:

       #"Expanded ceo" = Table.ExpandRecordColumn(#"Expanded _value1", "ceo", {"name"}, {"ceo.name"}),

    if the ceo Column is null or even doesnt exist for particular API call?

    Many thanks

    Alex

    Monday, June 15, 2020 8:34 AM
  • Table.ExpandRecordColumn should still work even if a column value is null. Are you seeing something different?

    Regarding the column not existing, you would have to add some conditional logic to either add the column (with all nulls) prior to expanding, or to avoid the expansion when the column doesn't exist.

    In either case, you'd want to use functions like Table.ColumnNames and List.Contains to do the check.

    Ehren

    Monday, June 15, 2020 5:47 PM
    Owner