Solved: Parse Dynamic JSON from SQL Column

Naesevol · ‎08-13-2019

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 SQL source column. I can successfully parse this column by clicking through and expanding, but it only parses the first object found in my JSON array. It does not parse the second object.

I have a SQL View as my source with roughly 30 columns in it. The columns for each row are a Unique ID, 28 other attributes, and a JSON string which is Dynamic in that I won't know the contents, and it could be nested.

The SQL layout looks like:

LID | ~28 Attributes | EvalResults

EvalResults is the JSON in question, which looks 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
	}]
}

I will always have the "values" array, but anything after that is up in the air from nothing in the Array to 15 object in the array. As you can see, I also won't know the names of the keys in the JSON object.

When I successfully parse out the first object in this array, the Power Query M looks like this:

let
    Source = Sql.Database("mysqlserver", "mydbname", [Query="SELECT * FROM myViewName#(lf)"]),
    #"Parsed JSON" = Table.TransformColumns(Source,{{"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", "epoch"}, {"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.epoch"})
in
    #"Expanded EvalResults.values1"

This would be OK if I only ever needed to grab one object from the array, but it fails at getting down to the second object.

I have tried a lot of finessing with Power Query M, but I am new to it.

Is this possible to achieve? I would be open to either one big table with all of the columns in it, or sub tables under each row (as long as the performance wasn't an issue). Whatever allows me to get at the data!

Thank you in advance!

ImkeF · ‎08-14-2019

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

ImkeF · ‎08-13-2019

Hi @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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Naesevol · ‎08-13-2019

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 · ‎08-13-2019

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Naesevol · ‎08-13-2019

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 · ‎08-13-2019

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Naesevol · ‎08-13-2019

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:

1 yyy iii json1
2 xxx bbb json2

Where 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:

1 yyy iii 1.92 false null null
2 xxx bbb 2.00 true 2.2 true

Is 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!

Naesevol · ‎08-14-2019

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.

ImkeF · ‎08-14-2019

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Naesevol · ‎08-14-2019

@IMK you're a hero! Just a slight tweak for my specific names:

let
    Source = Sql.Database("mysqldb, "mysqlserver", [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",{"EvalResults.values"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"AcquireTime", Order.Descending}})
in
    #"Sorted Rows"

The key was indeed:

But the important thing is NOT to expand the list column (as this will cause the dups), but instead add the column "Custom" instead.

And then of course the removal of the EvalResults.values (original expanded column) and the pivot back.

Really appreciate it!

Naesevol · ‎08-13-2019

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):

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:

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!

Parse Dynamic JSON from SQL Column

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024