excelpowerbipowerquery
Expanding a List into columns for Parent Records when List has variable content per parent record
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:
- 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 - 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"
}
]
}
]
}
Here's how the Lists are shown before my attempts to unpack them: Excel PowerQuery showing the List(s) as values within the parent records before being unpacked
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 |
- 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.
- 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 "")
- 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?
Solution
Using above JSON as input file with Powerquery code:
let Source = Json.Document(File.Contents("C:\Temp\a,json.")),
resources = Table.FromRecords(Source[resources]),
convertcolumn = Table.TransformColumns(resources,{{"custom_field_values", each Table.FromRecords(_)}}),
ColumnsToExpand =List.Distinct(List.Combine(List.Transform(Table.Column(convertcolumn , "custom_field_values"), each if _ is table then Table.ColumnNames(_) else {}))),
#"Expanded custom_field_values" = Table.ExpandTableColumn(convertcolumn, "custom_field_values", ColumnsToExpand,ColumnsToExpand )
in #"Expanded custom_field_values"
generates this output:
you could then pivot it, or just be fancy and directly do
let Source = Json.Document(File.Contents("C:\Temp\a,json.")),
resources = Table.FromRecords(Source[resources]),
convertcolumn = Table.TransformColumns(resources,{{"custom_field_values", each Table.PromoteHeaders(Table.Transpose(Table.FromRecords(_)))}}),
ColumnsToExpand =List.Distinct(List.Combine(List.Transform(Table.Column(convertcolumn , "custom_field_values"), each if _ is table then Table.ColumnNames(_) else {}))),
#"Expanded custom_field_values" = Table.ExpandTableColumn(convertcolumn, "custom_field_values", ColumnsToExpand,ColumnsToExpand )
in #"Expanded custom_field_values"
and get
- Constrain a Specman list so it doesn't have identical values in consecutive elements
- How to type cast a list of uint to a list of vr_ahb_data in Specman?
- Static fields/methods in e
- What is the difference between deep_copy and gen keeping in Specman?
- Specman UVM: What is the difference between write_reg { .field == 2;}; and write_reg_fields?
- Does Specman support optional parameters to a method?
- Specman e: When colon equal sign ":=" should be used?
- Specman e: Is there a way to know how many values there is in an enumerated type?
- Specman e error: No match for file when using "for each line in file"
- How to run e file one by one? Not in parallel test
- Specman/e constraint (for each in) iteration
- Specman e: How driver's items queue can be locked from a sequence?
- Specman e: How to print variable's address?
- Specman e: "keep type .. is a" fails to refine the type of a field
- Specman soft select on variable, decimal vs. hexadecimal values
- Specman e: How to print a pointer to a struct?
- Specman e: Is there a way to print unit instance name?
- Specman e: simultaneous events error
- Specman e coverage: ignored values appear in the coverage statistics
- Specman e: How a sequence should be started when gen_and_start_main constrained to FALSE?
- Specman/e list of lists (multidimensional array)
- Does Specman e have struct constructor?
- Specman e: How the predefined sequence.item should be used?
- Specman e: define-as-computed macro error
- Specman e UVM: Why to inherit from uvm_* units?
- Specman e: A sequence drives its BFM also its MAIN was not defined in a test
- e HVL (IEEE 1647): expect expression fails unexpectedly
- Specman e subtyping: How to refer to FALSE value of conditional field in when/extend subtyping?
- e HVL (IEEE 1647): How to set 'X' value?
- Difference between declaring an event that is sensitive to a simple_port value and event_port