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

[TheInvoker]

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:

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

Then this query:

= 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.

= 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

[lbendlin]

Keep in mind that at the end of your Power Query script needs to be a single table with  (ideally) static meta data (column names, column order, column types).  So while you may be able to recursively collect all these tables, it is for nought as you then need to squeeze them into a single result.

XML and JSON are by definition hierarchical.  RDBMS type solutions are not.  Converting one into the other includes making compromises and concessions.