Forum Discussion
Parse Dynamic JSON from SQL Column
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.
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
}]
}
]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
Thanks ImkeF - this gets me close. This parses the json column down to a brand new table, but I lose all the rest of my previous sql data.
I guess I may not have been fully clear from the get-go though: I need to do this for each row (thousands of rows) returned from my SQL View, and append the results of the JSON column to the original source table (or have child tables that can be accessed from within the report).
Each unique JSON key found should show up as a column on my original table.
Is there a way to loop through and do this parsing for each object (which holds an array of objects), and get a result that renders all of the JSON values as new columns, appended to my original source table?
For example let's say my source SQL looks like this with only two rows (for simplicity):
| LID | SqlColumn1 | SqlColumn2 | EvalResults |
1 somedata somedata2 evalresultJson1
2 somedata1 somedata22 evalresultJson2
Let's say that evalresultJson1 value was:
{ "values": [{ "abc": 0.98, "xyz": true }] }and evalresultJson2 is:
{ "values": [{ "abc": 1.92, "xyz": false }, { "zzz": true, "vvv": 644801 }] }My desired result would be something like:
| LID | SqlColumn1 | SqlColumn2 | EvalResults.abc | EvalResults.xyz| EvalResults.zzz | EvalResults.vvv |
1 somedata somedata2 0.98 true null null
2 somedata1 somedata22 1.92 false true 644801
I imagine the solution must be to loop through, and do the parsing you have shown, and append back to the original table somehow? Or to create child tables?
Is this kind of thing possible?
Thank you very much for the help so far!
Hm - there appears to be a bug whenever I edit a reply to a form post, my post disappears, so I am resubmitting my reply.
Your most recent reply gets me parsed down properly, but it results in a new table which drops all of my previous columns, and only keeps the JSON column as the new table.
I think maybe I was not clear enough on the ask originally, my apologies:
I need to be able to loop through each SQL Row, parse the JSON column, and store the resulting Keys from the JSON as new columns on the SQL Source Table (or store the JSON key/values as a new table that relates to each row).
A simple example below.
Take my source:
| lid | sqlcol1 | sqlcol 2| EvalResults |
1 yyy iii json1
2 xxx bbb json2Where json1 is:
{ "values": [{ "aaa": 1.92, "bbb": false }] }Where json2 is:
{ "values": [{ "aaa": 2.00, "bbb": true }, { "xxx": "2.2", "yyy": true }] }I would want my resulting table to look like:
| lid | sqlcol1 | sqlcol 2| EvalResults.aaa |EvalResults.bbb|EvalResults.xxx|EvalResults.yyy
1 yyy iii 1.92 false null null
2 xxx bbb 2.00 true 2.2 trueIs this possible? I imagine I will need to loop through each row, but am not familiar enough with Power Query M to be certain that is correct, or even doable.
Thank you so much for the help so far!
Updating for any future seekers of knowledge:
My first SQL row had a JSON column that only contained an array which held a single object.
When I sorted my SQL rows by most recently added, I had a JSON column that contained an array which held more than one object.
Power BI then recgonized that there were multiple objects to Parse. Here is my resulting Power Query M when it appropriately recognized the JSON column:
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"}), #"Expanded EvalResults.values" = Table.ExpandListColumn(#"Expanded EvalResults", "EvalResults.values"), #"Expanded EvalResults.values1" = Table.ExpandRecordColumn(#"Expanded EvalResults.values", "EvalResults.values", {"lid", "jid", "freq_gpsK0", "freq_gpsK0_result", "freq_accK0", "freq_accK0_result", "freq_gyrK0", "freq_gyrK0_result", "freq_camF0", "freq_camR0", "freq_camL0", "freq_camB0", "camFPS_result", "longtrip", "longtrip_result", "build", "build_result", "max_gpsK0_v", "max_gpsK0_v_result", "no_pii", "numcams", "numcams_result", "count_camF0_unordered", "count_camR0_unordered", "count_camL0_unordered", "count_camB0_unordered", "camsOrdered_result", "freq_cbs0", "freq_cbs1", "freq_cbs2", "freq_cbs3", "freq_cbs4", "freq_cbs5", "freq_cbs6", "freq_cbs7", "freq_cbs8", "freq_cbs9", "epoch", "datetime", "datetime_result", "length", "length_result", "inertial", "camera", "inertial_result", "camera_result", "accf0_inbounds", "accb0_inbounds", "accl0_inbounds", "accr0_inbounds", "acck0_inbounds", "gyrf0_inbounds", "gyrb0_inbounds", "gyrl0_inbounds", "gyrr0_inbounds", "gyrk0_inbounds", "camf0_inbounds", "camb0_inbounds", "caml0_inbounds", "camr0_inbounds", "camf0_inbounds_1sec", "camb0_inbounds_1sec", "caml0_inbounds_1sec", "camr0_inbounds_1sec", "camf0_inbounds_lateframes", "camb0_inbounds_lateframes", "caml0_inbounds_lateframes", "camr0_inbounds_lateframes", "ReasonForRecording", "FingerPrint"}, {"EvalResults.values.lid", "EvalResults.values.jid", "EvalResults.values.freq_gpsK0", "EvalResults.values.freq_gpsK0_result", "EvalResults.values.freq_accK0", "EvalResults.values.freq_accK0_result", "EvalResults.values.freq_gyrK0", "EvalResults.values.freq_gyrK0_result", "EvalResults.values.freq_camF0", "EvalResults.values.freq_camR0", "EvalResults.values.freq_camL0", "EvalResults.values.freq_camB0", "EvalResults.values.camFPS_result", "EvalResults.values.longtrip", "EvalResults.values.longtrip_result", "EvalResults.values.build", "EvalResults.values.build_result", "EvalResults.values.max_gpsK0_v", "EvalResults.values.max_gpsK0_v_result", "EvalResults.values.no_pii", "EvalResults.values.numcams", "EvalResults.values.numcams_result", "EvalResults.values.count_camF0_unordered", "EvalResults.values.count_camR0_unordered", "EvalResults.values.count_camL0_unordered", "EvalResults.values.count_camB0_unordered", "EvalResults.values.camsOrdered_result", "EvalResults.values.freq_cbs0", "EvalResults.values.freq_cbs1", "EvalResults.values.freq_cbs2", "EvalResults.values.freq_cbs3", "EvalResults.values.freq_cbs4", "EvalResults.values.freq_cbs5", "EvalResults.values.freq_cbs6", "EvalResults.values.freq_cbs7", "EvalResults.values.freq_cbs8", "EvalResults.values.freq_cbs9", "EvalResults.values.epoch", "EvalResults.values.datetime", "EvalResults.values.datetime_result", "EvalResults.values.length", "EvalResults.values.length_result", "EvalResults.values.inertial", "EvalResults.values.camera", "EvalResults.values.inertial_result", "EvalResults.values.camera_result", "EvalResults.values.accf0_inbounds", "EvalResults.values.accb0_inbounds", "EvalResults.values.accl0_inbounds", "EvalResults.values.accr0_inbounds", "EvalResults.values.acck0_inbounds", "EvalResults.values.gyrf0_inbounds", "EvalResults.values.gyrb0_inbounds", "EvalResults.values.gyrl0_inbounds", "EvalResults.values.gyrr0_inbounds", "EvalResults.values.gyrk0_inbounds", "EvalResults.values.camf0_inbounds", "EvalResults.values.camb0_inbounds", "EvalResults.values.caml0_inbounds", "EvalResults.values.camr0_inbounds", "EvalResults.values.camf0_inbounds_1sec", "EvalResults.values.camb0_inbounds_1sec", "EvalResults.values.caml0_inbounds_1sec", "EvalResults.values.camr0_inbounds_1sec", "EvalResults.values.camf0_inbounds_lateframes", "EvalResults.values.camb0_inbounds_lateframes", "EvalResults.values.caml0_inbounds_lateframes", "EvalResults.values.camr0_inbounds_lateframes", "EvalResults.values.ReasonForRecording", "EvalResults.values.FingerPrint"}) in #"Expanded EvalResults.values1"I fear this is not very scalable, since the JSON can, and will shift in the future.. but maybe it will help someone looking for a solve for a smaller data set.
Now I am fighting with a new issue. Each row is being duplicated. I'll need to figure out a way to join the rows on LID, so that I don't have twice the number of records (or thrice if I happened to ever have three objects to parse).
Will report back when I figure out a path forward without dupes.
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.