Expanding Lists of Records into columns for Parent Records (List size can vary/change)

[Chris080]

I connecting to a webAPI providing key-value pairs within a subdicitonary for a given parent record. I'm looking for help to systematically extract the values of these pairs. 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. Ideally, this cell would effectively become a value for the parent record's row.

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:

```json

{
"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"
}
]
}
]
}

```

Here's how this json looks within PowerQuery before my attempts to unpack it:

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

|name|shipping name|status|uuid|division_number|account_number|restricted_access|
|-|-|-|-|-|-|-|
|Account, Division, and Restricted Access ALL have values (Example 1)||new|60697ec4-23dc-4c2b-aace-4e2e05d7f07d|PPPP9|999999|TRUE|
|Just Account Number has value||new|2792cf7f-4311-4269-bc10-a6e0b5597947||1111311||

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 <KnownKeyOne> IF NOT FOUND return null" within the calculated column.

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

[Chris080]

Gao's answer appears to be on the right track with a way to solve this.

I was able to find some help elsewhere to solve this problem with a different technique. Since it wasn't my code to do it, I'm going to refrain from posting here unless posting and citing the help is okay with the forum guidelines. 

Thanks, Gao and Pwer for attempting to help me with this problem!

[Anonymous · Accepted solution]

Hi @Chris080 ,

How about expanding and then pivoting?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZJRa8MgEMe/ivgcR0xjmvSt0A0Gow8b7KUpwei5CqktUbuHsu++tCZlW1cY3ZjCIXf3/+v9cLHA+9J0Gxu+hhJPUImnQmy8cYgbiR7BulYLBxKt+A7QjjcebImjg8Su9HarzUs1aI1vmlBx3Hkb7Ay89v3eaxlyVLACEibImMWMpEkBpIAxI4Ua5aOx4hlTSS8S3rrNulIaGlkN10/QYn9WHMy5CANUxq9raHufozQ0sG6VuDRvEbrs0p4mrzpDsPbcx7WHU+dTmmUX8TIKND+wnOmdtnpjvsKcHi3/lKlKIAYlgEhgkqSxUqTOakriXMRZN3HKMriaqeznuMh0dv+c/hPTOWi3ghYN/3R+fFKETqxD4uZb3jbgRnfTh6fbXzPnVClK44yMii6keQokz1NGqErqXNJESSGuZv4DWoo39jOu5Ts=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Parsed JSON" = Table.TransformColumns(#"Changed Type",{},Json.Document),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"name", "shipping_name", "status", "uuid", "custom_field_values"}, {"name", "shipping_name", "status", "uuid", "custom_field_values"}),
    #"Expanded custom_field_values" = Table.ExpandListColumn(#"Expanded Column1", "custom_field_values"),
    #"Expanded custom_field_values1" = Table.ExpandRecordColumn(#"Expanded custom_field_values", "custom_field_values", {"custom_field_id", "value"}, {"custom_field_id", "value"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded custom_field_values1", List.Distinct(#"Expanded custom_field_values1"[custom_field_id]), "custom_field_id", "value")
in
    #"Pivoted Column"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[PwerQueryKees]

What do you want to do when multiple records are returned? Can you add a screenshot when you just expand each record to the lowers level? Because that would be probably be you starting point anyway and it would sure increase our understanding of your data...