Import JSON with mixed bag (Record, List, Scalar) in columns

[Anonymous]

I am importing JSON files into PowerBI. There are two columns in the data that can be a Scalar, a Record, or a List. I am a newer PowerBI user, and probably don't understand all I know about the tool. I have read the thread at Transforming json with power query (mix of list and record in a single column), and am still confused on how it all works.

Here is a sample image of the data after import:

After expanding CVEs, manually separating the Records from the List in PrevCVEs, and expanding that list, I'm left with:

in one of the columns. I have other JSON imports that will give a column with a mix of List, Record and Scalar values. I am trying to understand how to separate all these so that the import into PowerBI will work.

Here is a sample JSON:

[
{
"CVAHWID": null,
"InstalledVersion": null,
"PlatformID": "8951",
"SpqID": "sp145",
"SpqName": "(U01)",
"OSVer": "22H2",
"OS": "Win10",
"CVEs": [
"CVE-2022-30704",
"CVE-2022-27541",
"CVE-2022-27539",
"CVE-2022-43777",
"CVE-2022-43778"
],
"CVEMatch": false,
"RelType": "Critical",
"PrevCVEs": {
"SpqVersion": "02.06.00",
"RelType": "Critical",
"SpqName": "(U01)",
"CVEs": [
"CVE-2022-26845",
"CVE-2022-29893",
"CVE-2022-27497",
"CVE-2022-33159",
"CVE-2022-21198",
"CVE-2022-27538"
],
"SpqID": "sp143",
"CVEMatch": false
},
"SpqVersion": "2.07.00"
},
{
"CVAHWID": null,
"InstalledVersion": null,
"PlatformID": "81C5",
"SpqID": "sp1410",
"SpqName": System BIOS",
"OSVer": "22H2",
"OS": "Win10",
"CVEs": [
"CVE-2022-27539",
"CVE-2022-27541",
"CVE-2022-43777",
"CVE-2022-43778"
],
"CVEMatch": false,
"RelType": "Critical",
"PrevCVEs": {

},
"SpqVersion": "2.90"
},
{
"CVAHWID": null,
"InstalledVersion": null,
"PlatformID": "81C5",
"SpqID": "sp147",
"SpqName": System BIOS",
"OSVer": "22H2",
"OS": "Win10",
"CVEs": [
"CVE-2022-27539",
"CVE-2022-27541",
"CVE-2022-43777",
"CVE-2022-43778"
],
"CVEMatch": false,
"RelType": "Critical",
"PrevCVEs": [
{
"SpqVersion": "02.85",
"RelType": "Critical",
"SpqName": System BIOS",
"CVEs": [
"CVE-2022-31635",
"CVE-2022-31636",
"CVE-2022-31637",
"CVE-2022-31638",
"CVE-2022-31639"
],
"SpqID": "sp145",
"CVEMatch": false
},
{
"SpqVersion": "02.82",
"RelType": "Critical",
"SpqName": System BIOS",
"CVEs": [
"CVE-2021-33123",
"CVE-2021-33124",
"CVE-2022-21131",
"CVE-2022-21166"
],
"SpqID": "SP140088",
"CVEMatch": false
},
{
"SpqVersion": "02.79",
"RelType": "Critical",
"SpqName": System BIOS",
"CVEs": [
"CVE-2022-23924",
"CVE-2022-23928",
"CVE-2022-23953",
"CVE-2022-23954",
"CVE-2022-23955",
"CVE-2022-23956",
"CVE-2022-23958"
],
"SpqID": "SP139218",
"CVEMatch": false
},
{
"SpqVersion": "02.78",
"RelType": "Critical",
"SpqName": System BIOS",
"CVEs": [
"CVE-2021-0099",
"CVE-2021-0103",
"CVE-2021-0107",
"CVE-2021-0111",
"CVE-2021-0114",
"CVE-2021-0115",
"CVE-2021-0116",
"CVE-2021-0117",
"CVE-2021-0118",
"CVE-2021-0124",
"CVE-2021-0125",
"CVE-2021-0127",
"CVE-2021-0156",
"CVE-2021-0157",
"CVE-2021-33107"
],
"SpqID": "SP138357",
"CVEMatch": false
},
{
"SpqVersion": "02.75",
"RelType": "Critical",
"SpqName": System BIOS",
"CVEs": [
"CVE-2021-3661",
"CVE-2021-39297",
"CVE-2021-39301"
],
"SpqID": "SP136035",
"CVEMatch": false
},
{
"SpqVersion": "02.73",
"RelType": "Critical",
"SpqName": System BIOS",
"CVEs": "CVE-2021-0144",
"SpqID": "SP114263",
"CVEMatch": false
},
{
"SpqVersion": "02.63",
"RelType": "Critical",
"SpqName": System BIOS",
"CVEs": [
"CVE-2020-0587",
"CVE-2020-0591",
"CVE-2020-0592",
"CVE-2020-0593",
"CVE-2020-6929",
"CVE-2020-8696",
"CVE-2020-8738",
"CVE-2020-8739",
"CVE-2020-8740"
],
"SpqID": "SP110892",
"CVEMatch": false
},
{
"SpqVersion": "02.54",
"RelType": "Recommended",
"SpqName": System BIOS",
"CVEs": [
"CVE-2020-0548",
"CVE-2020-0549"
],
"SpqID": "SP104140",
"CVEMatch": false
},
{
"SpqVersion": "02.40",
"RelType": "Critical",
"SpqName": System BIOS",
"CVEs": [
"CVE-2019-0117",
"CVE-2019-0123",
"CVE-2019-0151",
"CVE-2019-0152",
"CVE-2019-11135",
"CVE-2019-11136",
"CVE-2019-11137",
"CVE-2019-11139"
],
"SpqID": "SP100018",
"CVEMatch": false
}
],
"SpqVersion": "2.90"
}
]

[Anonymous · Accepted solution]

I have come up with the solution, using M in Powerquery.  A combination of actions in the GUI and 'M' code written in Advanced Editor.