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

[sankallada]

I'm trying to transform a (I'm a newbie in power query transformation) json data. Our aim is to merge and transform a few json files to create a report. The json files are provided by different feed exposed by our web application. As part of learning, we tried to transform a single json file.
But the one of the field in our json data is is recognized by power query as a mix of record and list types (one of the column in power query is showing a mix of list and record).
In this case, I'm unable to grab the values of these line items.

Any pointers to solve this issue would be very helpful
Thanks in advance

 

[anvg]

Please, try

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

But I did not see a like structure of data between records and lists
Regards,

 

[sankallada]

Hi anvg,

Thanks a lot for your help and consideration and really sorry for the delayed reply.