Hello; I am cross-posting this question from SO.
I'm using an API (for a project management database) that returns a result in the following form (in JSON format):
NOTE: in reality there are tens of thousands of entries in each of these inner/nested lists; and each entry is actually not a string but a JSON object (i.e., Power Query record).
The API limits the database request to about 2000 projects at a time, so I have to break the request up into chunks. So instead of getting a single JSON object (PQ record) back with the entire response, I am getting multiple of them in a list.
So I have something like this, which is a list of JSON objects (PQ list of PQ records):
My question is: how can I write an efficient Power Query function to recombine these separate responses back into the original response, with the nested lists recombined the way they should be?
I attempted just using the [`Record.Combine`][1] function, but it does not work:
(NOTE: I'd prefer to do this in Python but for reasons I won't go into, we do need to do this in PQ.)
[1]: Record.Combine - PowerQuery M
I'm using an API (for a project management database) that returns a result in the following form (in JSON format):
JSON:
{'Content':
{
'Projects': ['project1', 'project2', 'project3', 'project4'],
'InvoiceGroups': ['group1', 'group2', 'group3', 'group4'],
'Phases': ['phase1', 'phase2', 'phase3', 'phase4'],
'Resources': ['resource1', 'resource2', 'resource3', 'resource4'],
}
}NOTE: in reality there are tens of thousands of entries in each of these inner/nested lists; and each entry is actually not a string but a JSON object (i.e., Power Query record).
The API limits the database request to about 2000 projects at a time, so I have to break the request up into chunks. So instead of getting a single JSON object (PQ record) back with the entire response, I am getting multiple of them in a list.
So I have something like this, which is a list of JSON objects (PQ list of PQ records):
JSON:
[
{
'Content':
{
'Projects': ['project1'],
'InvoiceGroups': ['group1'],
'Phases': ['phase1'],
'Resources': ['resource1'],
}
},
{
'Content':
{
'Projects': ['project2'],
'InvoiceGroups': ['group2'],
'Phases': ['phase2'],
'Resources': ['resource2'],
}
},
{
'Content':
{
'Projects': ['project3'],
'InvoiceGroups': ['group3'],
'Phases': ['phase3'],
'Resources': ['resource3'],
}
},
{
'Content':
{
'Projects': ['project4'],
'InvoiceGroups': ['group4'],
'Phases': ['phase4'],
'Resources': ['resource4'],
}
},
]My question is: how can I write an efficient Power Query function to recombine these separate responses back into the original response, with the nested lists recombined the way they should be?
I attempted just using the [`Record.Combine`][1] function, but it does not work:
Power Query:
let
recombined = Record.Combine(list_of_records)
in
recombined(NOTE: I'd prefer to do this in Python but for reasons I won't go into, we do need to do this in PQ.)
[1]: Record.Combine - PowerQuery M
Last edited by a moderator:
