Expand custom column nested in JSON record - API

[Anonymous]

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 custom columns (vs 2 records here or potentially n custom columns).

I've been able to solve for this by adding custom columns, filter, then merge back into the original dataset but thought there might be a simpler way?

I've seen a few functions from other comments from @hugoberry and @ImkeF but I don't think they work for this situation. 

Data: 

{
    "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",
            "Value": "xyz123" 
        }],
        "DocNumber": "1026",
        "TxnDate": "2014-09-02",
    }
}

Using the UI it is very easy to expand out the lists and records

let
    Source = Json.Document(File.Contents("C:\Users\Desktop\example_credit2.json")),
    #"Converted to Table1" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table1", "Value", {"RemainingCredit", "domain", "sparse", "Id", "SyncToken", "CustomField", "DocNumber", "TxnDate"}, {"RemainingCredit", "domain", "sparse", "Id", "SyncToken", "CustomField", "DocNumber", "TxnDate"}),
    #"Expanded CustomField" = Table.ExpandListColumn(#"Expanded Value", "CustomField"),
    #"Expanded CustomField1" = Table.ExpandRecordColumn(#"Expanded CustomField", "CustomField", {"DefinitionId", "Name", "Type", "Value"}, {"DefinitionId", "Name.1", "Type", "Value"})
in
    #"Expanded CustomField1"

It gets me this

Ideally I want to 

 

[ImkeF · Accepted solution]

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]

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]

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"

[ImkeF]

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!

[Anonymous]

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!!