Expanding List of Records into Columns

[JustinAiken]

I have gone through other community responses and videos regarding expanding record values. BI Elite was helpful in regards to the dynamic expansion of records.

However, I have been unable to find a solution that achieves my intended goal. Following my connection to an API to our company's CRM, and subsequent JSON, I am left with a column that contains a list:

Which contains 44 records per row when expanded:

Each Record contains one Field and Value. If possible, I would like to expand the list into columns named by the Field and populate the columns with each Value. Any help would be greatly appreciated!

[ImkeF · Accepted solution]

Hi @JustinAiken ,

if my understanding is correct, you can use this approach:

let
    Source = #table({"CustomFields"}, {{{[FirstRecordField = 1], [SecondRecordField = 2]}},  {{[FirstRecordField = 1], [SecondRecordField = 2]}}}),
    #"Added Custom" = Table.AddColumn(Source, "ToTables", each Record.Combine([CustomFields])),
    #"Expanded ToTables" = Table.ExpandRecordColumn(#"Added Custom", "ToTables", {"FirstRecordField", "SecondRecordField"}, {"FirstRecordField", "SecondRecordField"})
in
    #"Expanded ToTables"

 It  combines the records into one big record that can then easily be expanded. 
No need to create a table, as it would just hold one row.

If you want the fields expansion to be dynamic, you can grab the record field names from the first record like so:

Table.ExpandRecordColumn(#"Added Custom", "ToTables", Record.FieldNames(#"Added Custom"{0}[ToTables]))

Replace the code from the last step with it. This assumes like you said, that all rows contain the same record field names.

[JustinAiken]

Thank you @edhans and @ImkeF for your quick suggestions. As my HTTP GET does not contain any sensitive information, I have copied it below. Because the system only allows 50 rows per call, the formula utilizes the LastTimeStamp to obtain all 2,000+ records.

let
Final = List.Generate
(
()=>
[
Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/0", [Headers=[Accept="application/json"]])),
TotalCount = 1
],
each [TotalCount]>0,
each [
Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/" & Text.From([Result][MetaCollectionResult][LastTimeStamp]), [Headers=[Accept="application/json"]])),
TotalCount = [Result][MetaCollectionResult][TotalCount]
],
each [Result][Clients]
),
FinalTable = Table.FromRecords(List.Combine(Final))
in
FinalTable

If I was to use your approach @ImkeF, where would I input your formula? I am a bit of a novice with M.

[edhans]

It won't let me have anonymous access, so I still cannot see into the lists and records it provides.

[JustinAiken]

Unfortunately I can't provide access to the API anonymously. However, after removing some columns for brevity's sake, here is the view from my GET:

After expanding "CustomFields" to New Rows, I get the 44 rows of the following:

After expanding "CustomFields," I get the following:

This is a dummy client, so there are is no values in the Values column for this. But the Value column will have text values. Unfortunately, there are some null values that will have to be filtered out.

[edhans]

You can try reviewing this video or googling around. I cannot help with any particular M code as I don't have data to play with. 

Often {0} after a record will expand it, and the square brackets [] can be used to manipulate a list, but that is very generic. I'd have to have data to actually apply to your use case.

[edhans]

I am sure I can do it, but I'm gonna need that JSON file. I'm not going to figure out how to spend time created embedded lists with records in your format to expand them out. It is a matter of referencing the lists, records, and getting them back out to the table. I'm sure there will be a Table.Transform or List.Tranform in here too to get the 44 items into a field. But I'm speculating here because I cannot see into those values without the data.

You can put fake data in the JSON. Share via onedrive, dropbox, etc.

Would also need your expected results. Mock something up in Excel and post a screencap of it.

Imke can probably do it off the top of her head. 😁

[Greg_Deckler]

@ImkeF @edhans