Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Expand custom column nested in JSON record - API

I'm working with the QuickBooks API but this could apply to other nested custom columns in a json API call. Is there a function that will expand the CustomFields for me on 1 line for n number of cust...
Need Help
  • ImkeF's avatar
    ImkeF
    8 years ago

    Yes, a shorter way is this:

     

    let
    Source = Json.Document(File.Contents("C:\Users\Desktop\example_credit2.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"RemainingCredit", "domain", "sparse", "Id", "SyncToken", "CustomField", "DocNumber", "TxnDate"}, {"RemainingCredit", "domain", "sparse", "Id", "SyncToken", "CustomField", "DocNumber", "TxnDate"}),
    ToTable = Table.AddColumn(#"Expanded Value", "Custom", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(Table.FromRecords([CustomField]), {"Name", "Value"})))),
    #"Expanded Custom" = Table.ExpandTableColumn(ToTable, "Custom", Table.ColumnNames(ToTable[Custom]{0}))
in
    #"Expanded Custom"

    It is also dynamic: If there will be more "columns" in the CustomField, they will be expanded automatically as well.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

This works great except there are times when a nth column is listed but doesn't have a value (i.e. null).  I receive this error. Any thoughts on how to adjust the Table.ExpandTableColumn function to prevent it from removing the record?

 

Expression.Error: The field 'Value' of the record wasn't found.
Details:
DefinitionId=2
Name=Other
Type=StringType

 

Here is what the JSON could look like. The second nested record has Id, Name, Type, but no Value. What do you think MarcelBeug?

 

{
    "CreditMemo": {
        "RemainingCredit": 0,
        "domain": "QBO",
        "sparse": false,
        "Id": "73",
        "SyncToken": "3",
        "CustomField": [{
            "DefinitionId": "1",
            "Name": "Crew #",
            "Type": "StringType",
            "Value": "12345"
        }, {
           "DefinitionId": "2",
            "Name": "second#",
            "Type": "StringType"
        
        }],
        "DocNumber": "1026",
        "TxnDate": "2014-09-02",
    }
}

 

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

My first thought is that is not very polite to ImkeF if she solves your issue, and next you address someone else (i.c. me) for follow up.

 

Anyhow, I added a step "Completed Records" to Imke's code, so the fields "Name" and "Value" will always be present.

I'm pretty sure Imke would have come up with something similar.

 

let
    Source = Json.Document(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\example_credit2.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"RemainingCredit", "domain", "sparse", "Id", "SyncToken", "CustomField", "DocNumber", "TxnDate"}, {"RemainingCredit", "domain", "sparse", "Id", "SyncToken", "CustomField", "DocNumber", "TxnDate"}),
    #"Completed Records" = Table.TransformColumns(#"Expanded Value",{{"CustomField", each List.Transform(_, each Record.SelectFields(_,{"Name","Value"}, MissingField.UseNull))}}),
    ToTable = Table.AddColumn(#"Completed Records", "Custom", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(Table.FromRecords([CustomField]), {"Name", "Value"})))),
    #"Expanded Custom" = Table.ExpandTableColumn(ToTable, "Custom", Table.ColumnNames(ToTable[Custom]{0}))
in
    #"Expanded Custom"
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hey Marcel. Didn't want to hurt anyone's feelings. I've learned a lot in 24 hours reading hundreds of help topics. I saw some similar posts you commented on and felt that this was very similar (hence the @) ImkeF I figured you deserved a break for the evening.  Thanks again to both of you and your dedication to this forum. Its helping me become the ETL expert I want to be.

     

    Hopefully I can rise to your level some day!! 

  • ImkeF's avatar
    ImkeF
    Community Champion
    8 years ago

    Hi MarcelBeug: I don't consider it as impolite to suggest solutions anywhere. (But that might actually be a minority standpoint ;-) )

    I cannot state often enough how much I value your solutions: using "MissingField.UseNull" is new to me, so thanks a lot for stepping in here!