Forum Discussion
Solved
Parse Dynamic JSON from SQL Column
I've posted this earlier to Desktop, but apparently that post didn't take, so forgive me if this shows up twice! My goal is to parse a JSON string column that is being served up to power bi via a...
Hi Naesevol
this gets us back to my original solution where I assumed that there must probably more than one JSON object involved. Im just borrowing the last lines of code and combine them with your scenario:
let Source = Sql.Database("mysqlserver", "mysqldb", [Query="SELECT * FROM mySQLView#(lf)"]), #"Filtered Rows" = Table.SelectRows(Source, each ([EvalResults] <> null)), #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"AcquireTime", Order.Descending}}), #"Parsed JSON" = Table.TransformColumns(#"Sorted Rows1",{{"EvalResults", Json.Document}}), #"Expanded EvalResults" = Table.ExpandRecordColumn(#"Parsed JSON", "EvalResults", {"values"}, {"EvalResults.values"}), ListOfRecordsToTable = Table.AddColumn(#"Expanded EvalResults", "Custom", each Record.ToTable(Record.Combine([EvalResults.values]))), #"Expanded Custom" = Table.ExpandTableColumn(ListOfRecordsToTable, "Custom", {"Name", "Value"}, {"Name", "Value"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"values"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value") in #"Pivoted Column"Of course here could be typos or name mismatches, as I couldn't test your code.
But the important thing is NOT to expand the list column (as this will cause the dups), but instead add the column "Custom" instead.
ImkeF
Community Champion
Community ChampionHi Naesevol
not sure if my understanding is correct, but please check what this code does. No hardcoded Field/Column-names in there:
let
Source = "[{#(cr)#(lf)#(tab)""values"": [{#(cr)#(lf)#(tab)#(tab)""lid"": ""someguid1"",#(cr)#(lf)#(tab)#(tab)""jid"": ""someguid2"",#(cr)#(lf)#(tab)#(tab)""freq_gpsK0"": 1.9261547623478617,#(cr)#(lf)#(tab)#(tab)""freq_gpsK0_result"": false,#(cr)#(lf)#(tab)#(tab)""freq_accK0"": 206.7747298420615,#(cr)#(lf)#(tab)#(tab)""freq_accK0_result"": false,#(cr)#(lf)#(tab)#(tab)""freq_gyrK0"": 206.73750946018041,#(cr)#(lf)#(tab)#(tab)""freq_gyrK0_result"": false,#(cr)#(lf)#(tab)#(tab)""freq_camF0"": 61.332985943102443,#(cr)#(lf)#(tab)#(tab)""freq_camR0"": 61.367104626493465,#(cr)#(lf)#(tab)#(tab)""freq_camL0"": 61.162392526147322,#(cr)#(lf)#(tab)#(tab)""freq_camB0"": 61.367104626493465,#(cr)#(lf)#(tab)#(tab)""camFPS_result"": false,#(cr)#(lf)#(tab)#(tab)""longtrip"": 644.808,#(cr)#(lf)#(tab)#(tab)""longtrip_result"": true,#(cr)#(lf)#(tab)#(tab)""build"": 26,#(cr)#(lf)#(tab)#(tab)""build_result"": true,#(cr)#(lf)#(tab)#(tab)""max_gpsK0_v"": 34.427,#(cr)#(lf)#(tab)#(tab)""max_gpsK0_v_result"": true,#(cr)#(lf)#(tab)#(tab)""no_pii"": true,#(cr)#(lf)#(tab)#(tab)""numcams"": 4,#(cr)#(lf)#(tab)#(tab)""numcams_result"": true,#(cr)#(lf)#(tab)#(tab)""count_camF0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""count_camR0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""count_camL0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""count_camB0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""camsOrdered_result"": true,#(cr)#(lf)#(tab)#(tab)""freq_cbs0"": 1583.5442488306596,#(cr)#(lf)#(tab)#(tab)""freq_cbs1"": 563.9663279611915,#(cr)#(lf)#(tab)#(tab)""freq_cbs2"": 1376.9215022146127,#(cr)#(lf)#(tab)#(tab)""freq_cbs3"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs4"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs5"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs6"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs7"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs8"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs9"": null,#(cr)#(lf)#(tab)#(tab)""epoch"": 99#(cr)#(lf)#(tab)}, {#(cr)#(lf)#(tab)#(tab)""datetime"": ""2019-08-07T00:00:00"",#(cr)#(lf)#(tab)#(tab)""lid"": ""ad803d67-e9a4-4a67-87a5-a79c8a5b8f3e"",#(cr)#(lf)#(tab)#(tab)""jid"": ""63021e67-bf31-43f9-8f73-7e9c3e8522c4"",#(cr)#(lf)#(tab)#(tab)""datetime_result"": true,#(cr)#(lf)#(tab)#(tab)""length"": 644801,#(cr)#(lf)#(tab)#(tab)""length_result"": true,#(cr)#(lf)#(tab)#(tab)""inertial"": 100.0,#(cr)#(lf)#(tab)#(tab)""camera"": 99.3,#(cr)#(lf)#(tab)#(tab)""inertial_result"": true,#(cr)#(lf)#(tab)#(tab)""camera_result"": true,#(cr)#(lf)#(tab)#(tab)""accf0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""accb0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""accl0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""accr0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""acck0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrf0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrb0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrl0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrr0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrk0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camf0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camb0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""caml0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camr0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camf0_inbounds_1sec"": 100.0,#(cr)#(lf)#(tab)#(tab)""camb0_inbounds_1sec"": 99.9,#(cr)#(lf)#(tab)#(tab)""caml0_inbounds_1sec"": 99.9,#(cr)#(lf)#(tab)#(tab)""camr0_inbounds_1sec"": 100.0,#(cr)#(lf)#(tab)#(tab)""camf0_inbounds_lateframes"": 100.0,#(cr)#(lf)#(tab)#(tab)""camb0_inbounds_lateframes"": 100.0,#(cr)#(lf)#(tab)#(tab)""caml0_inbounds_lateframes"": 99.5,#(cr)#(lf)#(tab)#(tab)""camr0_inbounds_lateframes"": 100.0#(cr)#(lf)#(tab)}]#(cr)#(lf)}, #(cr)#(lf){#(cr)#(lf)#(tab)""values"": [{#(cr)#(lf)#(tab)#(tab)""lid"": ""someguid1"",#(cr)#(lf)#(tab)#(tab)""jid"": ""someguid2"",#(cr)#(lf)#(tab)#(tab)""freq_gpsK0"": 1.9261547623478617,#(cr)#(lf)#(tab)#(tab)""freq_gpsK0_result"": false,#(cr)#(lf)#(tab)#(tab)""freq_accK0"": 206.7747298420615,#(cr)#(lf)#(tab)#(tab)""freq_accK0_result"": false,#(cr)#(lf)#(tab)#(tab)""freq_gyrK0"": 206.73750946018041,#(cr)#(lf)#(tab)#(tab)""freq_gyrK0_result"": false,#(cr)#(lf)#(tab)#(tab)""freq_camF0"": 61.332985943102443,#(cr)#(lf)#(tab)#(tab)""freq_camR0"": 61.367104626493465,#(cr)#(lf)#(tab)#(tab)""freq_camL0"": 61.162392526147322,#(cr)#(lf)#(tab)#(tab)""freq_camB0"": 61.367104626493465,#(cr)#(lf)#(tab)#(tab)""camFPS_result"": false,#(cr)#(lf)#(tab)#(tab)""longtrip"": 644.808,#(cr)#(lf)#(tab)#(tab)""longtrip_result"": true,#(cr)#(lf)#(tab)#(tab)""build"": 26,#(cr)#(lf)#(tab)#(tab)""build_result"": true,#(cr)#(lf)#(tab)#(tab)""max_gpsK0_v"": 34.427,#(cr)#(lf)#(tab)#(tab)""max_gpsK0_v_result"": true,#(cr)#(lf)#(tab)#(tab)""no_pii"": true,#(cr)#(lf)#(tab)#(tab)""numcams"": 4,#(cr)#(lf)#(tab)#(tab)""numcams_result"": true,#(cr)#(lf)#(tab)#(tab)""count_camF0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""count_camR0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""count_camL0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""count_camB0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""camsOrdered_result"": true,#(cr)#(lf)#(tab)#(tab)""freq_cbs0"": 1583.5442488306596,#(cr)#(lf)#(tab)#(tab)""freq_cbs1"": 563.9663279611915,#(cr)#(lf)#(tab)#(tab)""freq_cbs2"": 1376.9215022146127,#(cr)#(lf)#(tab)#(tab)""freq_cbs3"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs4"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs5"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs6"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs7"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs8"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs9"": null,#(cr)#(lf)#(tab)#(tab)""epoch"": 99#(cr)#(lf)#(tab)}, {#(cr)#(lf)#(tab)#(tab)""datetime"": ""2019-08-07T00:00:00"",#(cr)#(lf)#(tab)#(tab)""lid"": ""ad803d67-e9a4-4a67-87a5-a79c8a5b8f3e"",#(cr)#(lf)#(tab)#(tab)""jid"": ""63021e67-bf31-43f9-8f73-7e9c3e8522c4"",#(cr)#(lf)#(tab)#(tab)""datetime_result"": true,#(cr)#(lf)#(tab)#(tab)""length"": 644801,#(cr)#(lf)#(tab)#(tab)""length_result"": true,#(cr)#(lf)#(tab)#(tab)""inertial"": 100.0,#(cr)#(lf)#(tab)#(tab)""camera"": 99.3,#(cr)#(lf)#(tab)#(tab)""inertial_result"": true,#(cr)#(lf)#(tab)#(tab)""camera_result"": true,#(cr)#(lf)#(tab)#(tab)""accf0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""accb0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""accl0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""accr0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""acck0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrf0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrb0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrl0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrr0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrk0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camf0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camb0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""caml0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camr0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camf0_inbounds_1sec"": 100.0,#(cr)#(lf)#(tab)#(tab)""camb0_inbounds_1sec"": 99.9,#(cr)#(lf)#(tab)#(tab)""caml0_inbounds_1sec"": 99.9,#(cr)#(lf)#(tab)#(tab)""camr0_inbounds_1sec"": 100.0,#(cr)#(lf)#(tab)#(tab)""camf0_inbounds_lateframes"": 100.0,#(cr)#(lf)#(tab)#(tab)""camb0_inbounds_lateframes"": 100.0,#(cr)#(lf)#(tab)#(tab)""caml0_inbounds_lateframes"": 99.5,#(cr)#(lf)#(tab)#(tab)""camr0_inbounds_lateframes"": 100.0#(cr)#(lf)#(tab)}]#(cr)#(lf)}#(cr)#(lf)]",
#"Parsed JSON" = Json.Document(Source),
#"Converted to Table" = Table.FromList(#"Parsed JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"values"}, {"values"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded Column1", "Index", 1, 1),
ListOfRecordsToTable = Table.AddColumn(#"Added Index", "Custom", each Record.ToTable(Record.Combine([values]))),
#"Expanded Custom" = Table.ExpandTableColumn(ListOfRecordsToTable, "Custom", {"Name", "Value"}, {"Name", "Value"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"values"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value")
in
#"Pivoted Column"
Hi ImkeF - this definitely parses both objects in the values array!
It looks like adding an index was probably the thing I was missing. Taking it to a table, expanding, and pivoting back is clever.
That said, it looks like you've changed the JSON structure a little, and you've made the top level an array as opposed to an object, like:
[{
"values": [{
"lid": "someguid1",
"jid": "someguid2",
"freq_gpsK0": 1.9261547623478617,
"freq_gpsK0_result": false,
"freq_accK0": 206.7747298420615,
"freq_accK0_result": false,
"freq_gyrK0": 206.73750946018041,
"freq_gyrK0_result": false,
"freq_camF0": 61.332985943102443,
"freq_camR0": 61.367104626493465,
"freq_camL0": 61.162392526147322,
"freq_camB0": 61.367104626493465,
"camFPS_result": false,
"longtrip": 644.808,
"longtrip_result": true,
"build": 26,
"build_result": true,
"max_gpsK0_v": 34.427,
"max_gpsK0_v_result": true,
"no_pii": true,
"numcams": 4,
"numcams_result": true,
"count_camF0_unordered": 0,
"count_camR0_unordered": 0,
"count_camL0_unordered": 0,
"count_camB0_unordered": 0,
"camsOrdered_result": true,
"freq_cbs0": 1583.5442488306596,
"freq_cbs1": 563.9663279611915,
"freq_cbs2": 1376.9215022146127,
"freq_cbs3": null,
"freq_cbs4": null,
"freq_cbs5": null,
"freq_cbs6": null,
"freq_cbs7": null,
"freq_cbs8": null,
"freq_cbs9": null,
"epoch": 99
}, {
"datetime": "2019-08-07T00:00:00",
"lid": "ad803d67-e9a4-4a67-87a5-a79c8a5b8f3e",
"jid": "63021e67-bf31-43f9-8f73-7e9c3e8522c4",
"datetime_result": true,
"length": 644801,
"length_result": true,
"inertial": 100.0,
"camera": 99.3,
"inertial_result": true,
"camera_result": true,
"accf0_inbounds": 100.0,
"accb0_inbounds": 100.0,
"accl0_inbounds": 100.0,
"accr0_inbounds": 100.0,
"acck0_inbounds": 100.0,
"gyrf0_inbounds": 100.0,
"gyrb0_inbounds": 100.0,
"gyrl0_inbounds": 100.0,
"gyrr0_inbounds": 100.0,
"gyrk0_inbounds": 100.0,
"camf0_inbounds": 100.0,
"camb0_inbounds": 100.0,
"caml0_inbounds": 100.0,
"camr0_inbounds": 100.0,
"camf0_inbounds_1sec": 100.0,
"camb0_inbounds_1sec": 99.9,
"caml0_inbounds_1sec": 99.9,
"camr0_inbounds_1sec": 100.0,
"camf0_inbounds_lateframes": 100.0,
"camb0_inbounds_lateframes": 100.0,
"caml0_inbounds_lateframes": 99.5,
"camr0_inbounds_lateframes": 100.0
}]
},
{
"values": [{
"lid": "someguid1",
"jid": "someguid2",
"freq_gpsK0": 1.9261547623478617,
"freq_gpsK0_result": false,
"freq_accK0": 206.7747298420615,
"freq_accK0_result": false,
"freq_gyrK0": 206.73750946018041,
"freq_gyrK0_result": false,
"freq_camF0": 61.332985943102443,
"freq_camR0": 61.367104626493465,
"freq_camL0": 61.162392526147322,
"freq_camB0": 61.367104626493465,
"camFPS_result": false,
"longtrip": 644.808,
"longtrip_result": true,
"build": 26,
"build_result": true,
"max_gpsK0_v": 34.427,
"max_gpsK0_v_result": true,
"no_pii": true,
"numcams": 4,
"numcams_result": true,
"count_camF0_unordered": 0,
"count_camR0_unordered": 0,
"count_camL0_unordered": 0,
"count_camB0_unordered": 0,
"camsOrdered_result": true,
"freq_cbs0": 1583.5442488306596,
"freq_cbs1": 563.9663279611915,
"freq_cbs2": 1376.9215022146127,
"freq_cbs3": null,
"freq_cbs4": null,
"freq_cbs5": null,
"freq_cbs6": null,
"freq_cbs7": null,
"freq_cbs8": null,
"freq_cbs9": null,
"epoch": 99
}, {
"datetime": "2019-08-07T00:00:00",
"lid": "ad803d67-e9a4-4a67-87a5-a79c8a5b8f3e",
"jid": "63021e67-bf31-43f9-8f73-7e9c3e8522c4",
"datetime_result": true,
"length": 644801,
"length_result": true,
"inertial": 100.0,
"camera": 99.3,
"inertial_result": true,
"camera_result": true,
"accf0_inbounds": 100.0,
"accb0_inbounds": 100.0,
"accl0_inbounds": 100.0,
"accr0_inbounds": 100.0,
"acck0_inbounds": 100.0,
"gyrf0_inbounds": 100.0,
"gyrb0_inbounds": 100.0,
"gyrl0_inbounds": 100.0,
"gyrr0_inbounds": 100.0,
"gyrk0_inbounds": 100.0,
"camf0_inbounds": 100.0,
"camb0_inbounds": 100.0,
"caml0_inbounds": 100.0,
"camr0_inbounds": 100.0,
"camf0_inbounds_1sec": 100.0,
"camb0_inbounds_1sec": 99.9,
"caml0_inbounds_1sec": 99.9,
"camr0_inbounds_1sec": 100.0,
"camf0_inbounds_lateframes": 100.0,
"camb0_inbounds_lateframes": 100.0,
"caml0_inbounds_lateframes": 99.5,
"camr0_inbounds_lateframes": 100.0
}]
}
]Is this necessary? If so, I think I can alter my structure to always have an array at the top level.
I will try to apply this to my actual SQL Source JSON Column and report back with the findings.
Thanks!
- ImkeF
Hi Naesevol
no, there's no need to adjust to that structure. I just assumed that there must be more than one element there.
The latest JSON you've posted looks exactly like mine. So if you actually have a different structure for multiple objects, please post and I'll adjust the code.
Hi ImkeF ,
Sorry for the confusion, I posted yours in my reply. My original JSON is in my original post.Original JSON:
{ "values": [{ "lid": "someguid1", "jid": "someguid2", "freq_gpsK0": 1.9261547623478617, "freq_gpsK0_result": false, "freq_accK0": 206.7747298420615, "freq_accK0_result": false, "freq_gyrK0": 206.73750946018041, "freq_gyrK0_result": false, "freq_camF0": 61.332985943102443, "freq_camR0": 61.367104626493465, "freq_camL0": 61.162392526147322, "freq_camB0": 61.367104626493465, "camFPS_result": false, "longtrip": 644.808, "longtrip_result": true, "build": 26, "build_result": true, "max_gpsK0_v": 34.427, "max_gpsK0_v_result": true, "no_pii": true, "numcams": 4, "numcams_result": true, "count_camF0_unordered": 0, "count_camR0_unordered": 0, "count_camL0_unordered": 0, "count_camB0_unordered": 0, "camsOrdered_result": true, "freq_cbs0": 1583.5442488306596, "freq_cbs1": 563.9663279611915, "freq_cbs2": 1376.9215022146127, "freq_cbs3": null, "freq_cbs4": null, "freq_cbs5": null, "freq_cbs6": null, "freq_cbs7": null, "freq_cbs8": null, "freq_cbs9": null, "epoch": 99 }, { "datetime": "2019-08-07T00:00:00", "lid": "ad803d67-e9a4-4a67-87a5-a79c8a5b8f3e", "jid": "63021e67-bf31-43f9-8f73-7e9c3e8522c4", "datetime_result": true, "length": 644801, "length_result": true, "inertial": 100.0, "camera": 99.3, "inertial_result": true, "camera_result": true, "accf0_inbounds": 100.0, "accb0_inbounds": 100.0, "accl0_inbounds": 100.0, "accr0_inbounds": 100.0, "acck0_inbounds": 100.0, "gyrf0_inbounds": 100.0, "gyrb0_inbounds": 100.0, "gyrl0_inbounds": 100.0, "gyrr0_inbounds": 100.0, "gyrk0_inbounds": 100.0, "camf0_inbounds": 100.0, "camb0_inbounds": 100.0, "caml0_inbounds": 100.0, "camr0_inbounds": 100.0, "camf0_inbounds_1sec": 100.0, "camb0_inbounds_1sec": 99.9, "caml0_inbounds_1sec": 99.9, "camr0_inbounds_1sec": 100.0, "camf0_inbounds_lateframes": 100.0, "camb0_inbounds_lateframes": 100.0, "caml0_inbounds_lateframes": 99.5, "camr0_inbounds_lateframes": 100.0 }] }Your JSON:
[{ "values": [{ "lid": "someguid1", "jid": "someguid2", "freq_gpsK0": 1.9261547623478617, "freq_gpsK0_result": false, "freq_accK0": 206.7747298420615, "freq_accK0_result": false, "freq_gyrK0": 206.73750946018041, "freq_gyrK0_result": false, "freq_camF0": 61.332985943102443, "freq_camR0": 61.367104626493465, "freq_camL0": 61.162392526147322, "freq_camB0": 61.367104626493465, "camFPS_result": false, "longtrip": 644.808, "longtrip_result": true, "build": 26, "build_result": true, "max_gpsK0_v": 34.427, "max_gpsK0_v_result": true, "no_pii": true, "numcams": 4, "numcams_result": true, "count_camF0_unordered": 0, "count_camR0_unordered": 0, "count_camL0_unordered": 0, "count_camB0_unordered": 0, "camsOrdered_result": true, "freq_cbs0": 1583.5442488306596, "freq_cbs1": 563.9663279611915, "freq_cbs2": 1376.9215022146127, "freq_cbs3": null, "freq_cbs4": null, "freq_cbs5": null, "freq_cbs6": null, "freq_cbs7": null, "freq_cbs8": null, "freq_cbs9": null, "epoch": 99 }, { "datetime": "2019-08-07T00:00:00", "lid": "ad803d67-e9a4-4a67-87a5-a79c8a5b8f3e", "jid": "63021e67-bf31-43f9-8f73-7e9c3e8522c4", "datetime_result": true, "length": 644801, "length_result": true, "inertial": 100.0, "camera": 99.3, "inertial_result": true, "camera_result": true, "accf0_inbounds": 100.0, "accb0_inbounds": 100.0, "accl0_inbounds": 100.0, "accr0_inbounds": 100.0, "acck0_inbounds": 100.0, "gyrf0_inbounds": 100.0, "gyrb0_inbounds": 100.0, "gyrl0_inbounds": 100.0, "gyrr0_inbounds": 100.0, "gyrk0_inbounds": 100.0, "camf0_inbounds": 100.0, "camb0_inbounds": 100.0, "caml0_inbounds": 100.0, "camr0_inbounds": 100.0, "camf0_inbounds_1sec": 100.0, "camb0_inbounds_1sec": 99.9, "caml0_inbounds_1sec": 99.9, "camr0_inbounds_1sec": 100.0, "camf0_inbounds_lateframes": 100.0, "camb0_inbounds_lateframes": 100.0, "caml0_inbounds_lateframes": 99.5, "camr0_inbounds_lateframes": 100.0 }] }, { "values": [{ "lid": "someguid1", "jid": "someguid2", "freq_gpsK0": 1.9261547623478617, "freq_gpsK0_result": false, "freq_accK0": 206.7747298420615, "freq_accK0_result": false, "freq_gyrK0": 206.73750946018041, "freq_gyrK0_result": false, "freq_camF0": 61.332985943102443, "freq_camR0": 61.367104626493465, "freq_camL0": 61.162392526147322, "freq_camB0": 61.367104626493465, "camFPS_result": false, "longtrip": 644.808, "longtrip_result": true, "build": 26, "build_result": true, "max_gpsK0_v": 34.427, "max_gpsK0_v_result": true, "no_pii": true, "numcams": 4, "numcams_result": true, "count_camF0_unordered": 0, "count_camR0_unordered": 0, "count_camL0_unordered": 0, "count_camB0_unordered": 0, "camsOrdered_result": true, "freq_cbs0": 1583.5442488306596, "freq_cbs1": 563.9663279611915, "freq_cbs2": 1376.9215022146127, "freq_cbs3": null, "freq_cbs4": null, "freq_cbs5": null, "freq_cbs6": null, "freq_cbs7": null, "freq_cbs8": null, "freq_cbs9": null, "epoch": 99 }, { "datetime": "2019-08-07T00:00:00", "lid": "ad803d67-e9a4-4a67-87a5-a79c8a5b8f3e", "jid": "63021e67-bf31-43f9-8f73-7e9c3e8522c4", "datetime_result": true, "length": 644801, "length_result": true, "inertial": 100.0, "camera": 99.3, "inertial_result": true, "camera_result": true, "accf0_inbounds": 100.0, "accb0_inbounds": 100.0, "accl0_inbounds": 100.0, "accr0_inbounds": 100.0, "acck0_inbounds": 100.0, "gyrf0_inbounds": 100.0, "gyrb0_inbounds": 100.0, "gyrl0_inbounds": 100.0, "gyrr0_inbounds": 100.0, "gyrk0_inbounds": 100.0, "camf0_inbounds": 100.0, "camb0_inbounds": 100.0, "caml0_inbounds": 100.0, "camr0_inbounds": 100.0, "camf0_inbounds_1sec": 100.0, "camb0_inbounds_1sec": 99.9, "caml0_inbounds_1sec": 99.9, "camr0_inbounds_1sec": 100.0, "camf0_inbounds_lateframes": 100.0, "camb0_inbounds_lateframes": 100.0, "caml0_inbounds_lateframes": 99.5, "camr0_inbounds_lateframes": 100.0 }] } ]- ImkeF
No prob!
If it's just one object, the code is even simpler:
let Query2 = "{#(cr)#(lf)#(tab)""values"": [{#(cr)#(lf)#(tab)#(tab)""lid"": ""someguid1"",#(cr)#(lf)#(tab)#(tab)""jid"": ""someguid2"",#(cr)#(lf)#(tab)#(tab)""freq_gpsK0"": 1.9261547623478617,#(cr)#(lf)#(tab)#(tab)""freq_gpsK0_result"": false,#(cr)#(lf)#(tab)#(tab)""freq_accK0"": 206.7747298420615,#(cr)#(lf)#(tab)#(tab)""freq_accK0_result"": false,#(cr)#(lf)#(tab)#(tab)""freq_gyrK0"": 206.73750946018041,#(cr)#(lf)#(tab)#(tab)""freq_gyrK0_result"": false,#(cr)#(lf)#(tab)#(tab)""freq_camF0"": 61.332985943102443,#(cr)#(lf)#(tab)#(tab)""freq_camR0"": 61.367104626493465,#(cr)#(lf)#(tab)#(tab)""freq_camL0"": 61.162392526147322,#(cr)#(lf)#(tab)#(tab)""freq_camB0"": 61.367104626493465,#(cr)#(lf)#(tab)#(tab)""camFPS_result"": false,#(cr)#(lf)#(tab)#(tab)""longtrip"": 644.808,#(cr)#(lf)#(tab)#(tab)""longtrip_result"": true,#(cr)#(lf)#(tab)#(tab)""build"": 26,#(cr)#(lf)#(tab)#(tab)""build_result"": true,#(cr)#(lf)#(tab)#(tab)""max_gpsK0_v"": 34.427,#(cr)#(lf)#(tab)#(tab)""max_gpsK0_v_result"": true,#(cr)#(lf)#(tab)#(tab)""no_pii"": true,#(cr)#(lf)#(tab)#(tab)""numcams"": 4,#(cr)#(lf)#(tab)#(tab)""numcams_result"": true,#(cr)#(lf)#(tab)#(tab)""count_camF0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""count_camR0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""count_camL0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""count_camB0_unordered"": 0,#(cr)#(lf)#(tab)#(tab)""camsOrdered_result"": true,#(cr)#(lf)#(tab)#(tab)""freq_cbs0"": 1583.5442488306596,#(cr)#(lf)#(tab)#(tab)""freq_cbs1"": 563.9663279611915,#(cr)#(lf)#(tab)#(tab)""freq_cbs2"": 1376.9215022146127,#(cr)#(lf)#(tab)#(tab)""freq_cbs3"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs4"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs5"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs6"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs7"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs8"": null,#(cr)#(lf)#(tab)#(tab)""freq_cbs9"": null,#(cr)#(lf)#(tab)#(tab)""epoch"": 99#(cr)#(lf)#(tab)}, {#(cr)#(lf)#(tab)#(tab)""datetime"": ""2019-08-07T00:00:00"",#(cr)#(lf)#(tab)#(tab)""lid"": ""ad803d67-e9a4-4a67-87a5-a79c8a5b8f3e"",#(cr)#(lf)#(tab)#(tab)""jid"": ""63021e67-bf31-43f9-8f73-7e9c3e8522c4"",#(cr)#(lf)#(tab)#(tab)""datetime_result"": true,#(cr)#(lf)#(tab)#(tab)""length"": 644801,#(cr)#(lf)#(tab)#(tab)""length_result"": true,#(cr)#(lf)#(tab)#(tab)""inertial"": 100.0,#(cr)#(lf)#(tab)#(tab)""camera"": 99.3,#(cr)#(lf)#(tab)#(tab)""inertial_result"": true,#(cr)#(lf)#(tab)#(tab)""camera_result"": true,#(cr)#(lf)#(tab)#(tab)""accf0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""accb0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""accl0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""accr0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""acck0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrf0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrb0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrl0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrr0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""gyrk0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camf0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camb0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""caml0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camr0_inbounds"": 100.0,#(cr)#(lf)#(tab)#(tab)""camf0_inbounds_1sec"": 100.0,#(cr)#(lf)#(tab)#(tab)""camb0_inbounds_1sec"": 99.9,#(cr)#(lf)#(tab)#(tab)""caml0_inbounds_1sec"": 99.9,#(cr)#(lf)#(tab)#(tab)""camr0_inbounds_1sec"": 100.0,#(cr)#(lf)#(tab)#(tab)""camf0_inbounds_lateframes"": 100.0,#(cr)#(lf)#(tab)#(tab)""camb0_inbounds_lateframes"": 100.0,#(cr)#(lf)#(tab)#(tab)""caml0_inbounds_lateframes"": 99.5,#(cr)#(lf)#(tab)#(tab)""camr0_inbounds_lateframes"": 100.0#(cr)#(lf)#(tab)}]#(cr)#(lf)}", #"Parsed JSON" = Json.Document(Query2), values = #"Parsed JSON"[values], Custom1 = Record.Combine(values), Custom2 = Table.FromRecords({Custom1}) in Custom2