Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
gxharr2
Frequent Visitor

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

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:

 
gxharr2_2-1678739391797.png

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

gxharr2_3-1678739412533.png

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"
}
]

1 ACCEPTED SOLUTION
gxharr2
Frequent Visitor

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.  

View solution in original post

1 REPLY 1
gxharr2
Frequent Visitor

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.  

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.