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
