transform json to table friendly format.

[trivender]

Hi Team - Using powerquery a web api returns json in below format

{
"responses": [{
"1": {
"id": "1",
"submitdate": "1980-01-01"
}
}, {
"2": {
"id": "2",
"submitdate": "1980-01-01"
}
}, {
"3": {
"id": "3",
"submitdate": "1980-01-01"
}
}]
}
which I am not able to convert to table using powerquery user interface interactions. I guess if some how I can convert it to below json format then powerquery will easily understand it and convert it to table. Please let me know how can I transform above json format to the below one.


{
"responses": [{
"id": "1",
"submitdate": "1980-01-01"
}, {
"id": "2",
"submitdate": "1980-01-01"
}, {
"id": "3",
"submitdate": "1980-01-01"
}]
}
Thanks

 

[anvg]

Hi
Try

let
    source = Json.Document(File.Contents("C:\Path\1.json")),
    listValue = source[responses],
    toTable = Table.FromColumns({listValue}, {"data"}),
    getIdCol = Table.AddColumn(toTable, "RecNum", each Number.From(Record.FieldNames(_[data]){0}), type number),
    getRecord = Table.AddColumn(getIdCol, "record", each Record.Field(_[data], Record.FieldNames(_[data]){0}), type record),
    expandRecord = Table.ExpandRecordColumn(getRecord, "record", {"id", "submitdate"}, {"id", "submitdate"}),
    return = Table.RemoveColumns(expandRecord,{"data"})
in
    return

Regards,

 

[trivender]

Thank you very much.. I want link one more answer here. https://social.technet.microsoft.co...son-to-table-friendly-format?forum=powerquery