Expanded data and list Problem

[Anonymous]

Hi guys, I'm expanding some data and I came across the following problem, in the image below appears the icon to expand the data and I can expand normally.

After expanding there is another step which is more "records" and "lists".
if I click on record the following data appears.
Take Start

if I click on Lists the following data appears.

How can i expanded or extract all data?
Tks

[Anonymous]

@lbendlin tks for explanation, the @Anonymous solution works for me.

[Anonymous · Accepted solution]

Hi @Anonymous 

Here is a post with similar problem like yours. You may refer to this and I hope it could help you.

For reference: Transforming json with power query (mix of list and record in a single column)

This code may help you.

let
    source = Json.Document(File.Contents("d:\path\filename.json")),
    tabled = Table.FromRecords({source}),
    expandListField = Table.ExpandListColumn(tabled, "thingstodo"),
    expandRecField  = Table.ExpandRecordColumn(expandListField, "thingstodo", {"propCode", "hours"}, {"propCode", "hours"}),
    expandList2  = Table.ExpandListColumn(expandRecField, "hours"),
    fieldForRec  = Table.AddColumn(expandList2,"Rec",each if Value.Is([hours], type record) then [hours] else null,type record),
    fieldForList = Table.AddColumn(fieldForRec, "List",each if Value.Is([hours], type list) then [hours] else null,type list),
    removed = Table.RemoveColumns(fieldForList, {"hours"}),
    expandRecField2 = Table.ExpandRecordColumn(removed, "Rec", {"day", "time"}, {"day", "time"}),
    expandList3 = Table.ExpandListColumn(expandRecField2, "List")
in
    expandList3

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

@Anonymous  Looks like you are trying to squeeze a square peg (JSON) through a round hole (Power Query table).  Which of the fields from the source data do you actually need for your reporting? Can that data be meaningfully flattened into a table or should it stay in its hierarchical form?