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

[gxharr2]

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": "sp144095", "SpqName": "HP BIOS and System Firmware (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": "HP BIOS and System Firmware (U01)", "CVEs": [ "CVE-2022-26845", "CVE-2022-29893", "CVE-2022-27497", "CVE-2022-33159", "CVE-2022-21198", "CVE-2022-27538" ], "SpqID": "sp143123", "CVEMatch": false }, "SpqVersion": "2.07.00" }, { "CVAHWID": null, "InstalledVersion": null, "PlatformID": "81C5", "SpqID": "sp144810", "SpqName": "HP Z4-G4 Core-X WKS 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": "sp144807", "SpqName": "HP Z4-G4 Xeon Workstation 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": "HP Z4-G4 Xeon Workstation System BIOS", "CVEs": [ "CVE-2022-31635", "CVE-2022-31636", "CVE-2022-31637", "CVE-2022-31638", "CVE-2022-31639" ], "SpqID": "sp141585", "CVEMatch": false }, { "SpqVersion": "02.82", "RelType": "Critical", "SpqName": "HP Z4-G4 Xeon Workstation 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": "HP Z4-G4 Xeon Workstation 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": "HP Z4-G4 Xeon Workstation 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": "HP Z4-G4 Xeon Workstation System BIOS", "CVEs": [ "CVE-2021-3661", "CVE-2021-39297", "CVE-2021-39301" ], "SpqID": "SP136035", "CVEMatch": false }, { "SpqVersion": "02.73", "RelType": "Critical", "SpqName": "HP Z4-G4 Xeon Workstation System BIOS", "CVEs": "CVE-2021-0144", "SpqID": "SP114263", "CVEMatch": false }, { "SpqVersion": "02.63", "RelType": "Critical", "SpqName": "HP Z4-G4 Xeon Workstation 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": "HP Z4-G4 Xeon Workstation System BIOS", "CVEs": [ "CVE-2020-0548", "CVE-2020-0549" ], "SpqID": "SP104140", "CVEMatch": false }, { "SpqVersion": "02.40", "RelType": "Critical", "SpqName": "HP Z4G4 Xeon Workstation 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" } ]

Any help is GREATLY appreciated!

Thanks!
-gxharr2