Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Expanded data and list Problem

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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