How to convert all records in a nested list of records, into tables?

Sneaky

New member
Joined
Dec 19, 2023
Messages
1
Reaction score
0
Points
1
Excel Version(s)
latest
I have an expression which produces a list of records. Each record can have properties whose values are another list of records or a record. I want to convert it into a Table, where each list of records gets transformed into a table, and each record gets transformed into a table too. All recursively.

For example, if the data structure looks like this:

Code:
{
    "include": [
        {
            "relation": "ActivityCodeSet",
            "scope": {
                "limit": 2
            }
        }
    ]
}


Then this query:

Code:
= Table.FromRecords(
    List.Transform(
        Json.Document(Web.Contents("localhost:64977/ActivityCode?filter={""include"":[{""relation"":""ActivityCodeSet"",""scope"":{""limit"":2}}]}")),
        each Record.TransformFields(_,
            {
                {"ActivityCodeSet", (x) => Table.FromRecords({x})}
            }
        )
    )
)

Does the job I want, but the problem is, it is very hardcoded to that json structure. How can I do this more generically where I don't hardcode any field names? It should just work recursively with any json structure.

I got this so far, but it doesn't seem to work recursively. The nested ones remain a list of records.

Code:
= let
    Source = ...,
    fxFactorial = (x as list) =>
         let
            Check = Table.FromRecords(List.Transform(x, each Record.TransformFields(_,
                let
                    Row = _,
                    Fields = Record.FieldNames(Row),
                    RFields = List.Select(Fields, each Value.Is(Record.Field(Row, _), type record)),
                    LFields = List.Select(Fields, each Value.Is(Record.Field(Row, _), type list)),
                    RFieldsTr = List.Transform(RFields, each {_, (x) => Table.FromRecords({x})}),
                    LFieldsTr = List.Transform(LFields, each {_, @fxFactorial}),
                    Full = List.Combine({RFieldsTr, LFieldsTr})
                in
                    Full
            )))
          in
            Check,
    Custom1 = fxFactorial(Source)
in
    Custom1
 
Last edited:
Back
Top