(Help) Working with large sets of multi dimensional JSON files from DocumentDB (Cosmos DB)

Hi otjena,

You can refer to below steps to manual analysis these data and split them to new tables:

1. Convert json source to table.

Query:

let
    Source = Json.Document(File.Contents("C:\Users\xxxxx\Desktop\new 5.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

2. Use Table.SelectColumns function to create the split tables.

3. Analysis and expand these data.

Full query:

Record:
let
    Source = Table.SelectColumns(SourceTable,{"id","success"})
in
    Source

Stats:
let
    Source = Table.SelectColumns(SourceTable,{"id","data"}),
    #"Added Custom" = Table.SelectColumns(Table.AddColumn(Source, "stats", each Record.FieldValues([data][stats])),{"id","stats"}),
    #"Expanded stats" = Table.ExpandListColumn(#"Added Custom", "stats")
in
    #"Expanded stats"

Tests:
let
    Source = Table.SelectColumns(SourceTable,{"id","tests"}),
    #"Expanded tests" = Table.ExpandRecordColumn(Table.ExpandListColumn(Source, "tests"), "tests", {"data"}, {"data"})
in
    #"Expanded tests"

Regards,

Xiaoxin Sheng