I’m looking for help to systematically extract information from sub-dictionaries provided by an webAPI. In PowerQuery, these subdictionaries appear as Lists within their parent records. If there is a Key-Value pair in that list, I’d like to extract the value into cell within a named column representing the associated Key. This cell would effectively be a value for the parent record.

I have tried a few techniques but I seem to be getting stumped because:

1. The contents of the list vary. Sometimes there are 0, 1, 2, or 3 records in the list. If a Key-value pair is null, the Key-value pair are NOT included in the List/Sub-dictionary
2. If a field is not present in the sub-dictionary for a given parent record, I’d like for it’s value in the related column to be empty/null.

Here’s a Pseudo-Code description of what I’m envisioning.

> For some known set of known/expected keys
>    For each known “Known/expected Key”,
>        Find index of “Known/expected Key” and extract it’s associated value
>        Add this value to the column named “Known Expected key” in line with the row for that parent record
>        If no match is found, add null / empty value to the column named "Known Expected key” for that parent record

^ I expect this same code/logic could be extendable for each new column or key that’s added to my sub-directory dataset over time. <- If there’s some way to tweak the logic such that it can automatically add/handle newly added key-value pairs to these sub-dictionaries, that would be great too.

Here’s a snippet of the JSON Data that I’ve used for testing some solutions:

{
    "resources": [{
            
            "name": "Account and Restricted have values",
            "shipping_name": null,
            "status": "new",
            "uuid": "1c59e25c-7505-429e-9e75-9f3837fa65f2",
            "custom_field_values": [{
                    "custom_field_id": "acccount_number",
                    "value": "5555"
                }, {
                    "custom_field_id": "restricted_access",
                    "value": "true"
                }
            ]
        }, {
            "name": "Account and Restricted have values, Division is """,
            "shipping_name": null,
            "status": "new",
            "uuid": "98ac37cf-6e7b-4032-884e-5a9f969fb366",
            "custom_field_values": [{
                    "custom_field_id": "division_number",
                    "value": ""
                }, {
                    "custom_field_id": "restricted_access",
                    "value": "true"
                }, {
                    "custom_field_id": "acccount_number",
                    "value": "666"
                }
            ]
        }, {
            "name": "Division and Restricted Access have values",
            "shipping_name": null,
            "status": "new",
            "uuid": "f2e0efce-de5d-40ff-b6b1-08c06555456e",
            "custom_field_values": [{
                    "custom_field_id": "division_number",
                    "value": "DIV4"
                }, {
                    "custom_field_id": "restricted_access",
                    "value": "true"
                }
            ]
        }, {
            "name": "Just Account Number has value",
            "shipping_name": null,
            "status": "new",
            "uuid": "ae35f8a7-d130-400c-836a-2cee0fdc8f1b",
            "custom_field_values": [{
                    "custom_field_id": "acccount_number",
                    "value": "1111311"
                }
            ]
        }, {
            "name": "Neither Account Number, Division Number. Restricted Access has value FALSE",
            "shipping_name": null,
            "status": "new",
            "uuid": "a1ff1106-3906-484e-8845-1f2b8d12fdcc",
            "custom_field_values": [{
                    "custom_field_id": "restricted_access",
                    "value": "false"
                }
            ]
        }, {
            "name": "Neither Account Number, Division Number, nor Restricted Access have values (Example A)",
            "shipping_name": null,
            "status": "new",
            "uuid": "11714504-b78f-40b8-9e9d-a72f2a8db1e4",
            "custom_field_values": []
        }, {
            "name": "Account and Division numbers Only have values",
            "shipping_name": null,
            "status": "new",
            "uuid": "734fbb64-5b34-4d48-acc9-67fdfebd19d3",
            "custom_field_values": [{
                    "custom_field_id": "division_number",
                    "value": "LLL7"
                }, {
                    "custom_field_id": "acccount_number",
                    "value": "7777"
                }
            ]
        }, {
            "name": "Just Division Number has value",
            "shipping_name": null,
            "status": "new",
            "uuid": "2792cf7f-4311-4269-bc10-a6e0b5597947",
            "custom_field_values": [{
                    "custom_field_id": "division_number",
                    "value": "Just Division Number 9"
                }
            ]
        }, {
            "name": "Just Restricted Access has value",
            "shipping_name": null,
            "status": "new",
            "uuid": "54b90cf5-3bd4-479f-ac0e-520498929a9e",
            "custom_field_values": [{
                    "custom_field_id": "restricted_access",
                    "value": "true"
                }
            ]
        }, {
            "name": "Account, Division, and Restricted Access ALL have values (Example 2)",
            "shipping_name": null,
            "status": "new",
            "uuid": "1b84625f-37b5-4c1f-8e51-1b35efe4c55d",
            "custom_field_values": [{
                    "custom_field_id": "division_number",
                    "value": "JJJJJ8"
                }, {
                    "custom_field_id": "acccount_number",
                    "value": "88888"
                }, {
                    "custom_field_id": "restricted_access",
                    "value": "true"
                }
            ]
        }, {
            "name": "Account, Division, and Restricted Access ALL have values (Example 1)",
            "shipping_name": null,
            "status": "new",
            "uuid": "60697ec4-23dc-4c2b-aace-4e2e05d7f07d",
            "custom_field_values": [{
                    "custom_field_id": "division_number",
                    "value": "PPPP9"
                }, {
                    "custom_field_id": "acccount_number",
                    "value": "999999"
                }, {
                    "custom_field_id": "restricted_access",
                    "value": "true"
                }
            ]
        }
    ]
}

I’m hoping for a table like this at the “Parent” level (I only sketched out 2 of the examples from my test data set):

1. I’ve tried to figure out a combination of expand / extract values from the list. The closest version resulted in many duplicated rows for each parent record. I couldn’t figure out how to dedupe and retain all of the values that had been extracted.
2. I used a calculated column formula like this to extract the key or value BUT the indexes were static and it failed as soon as my data had varible legnths to the lists. I couldn’t figure out how to add logic like “find the index of IF NOT FOUND return null” within the calculated column.

= Table.AddColumn(#"Renamed Columns2", "Custom", each try Record.Field([custom_field_values]{1}, "value") otherwise "")

3. I tried the answer provided here but couldn’t figure out how to make it fit my case -> Power Query. How can I dynamically transform a list of records to columns?