1 ACCEPTED SOLUTION
Here is a way to check for record vs. list as part of your parsing. As stated previously, you can simply wrap the records you find into a list and then treat all outputs the same.
let
Source = Folder.Files("<local file location with your json examples saved>"),
//parse the json, if a single record, wrap in list so they are all list of records
ParseJson =
List.Transform(
Source[Content] , //binaries are in "Content" column
each let
json = Json.Document( _ ),
valTypeIsRecord = Type.Is( Value.Type( json ), type record )
in
if valTypeIsRecord //if it's a record
then { json } //wrap in a list
else json //otherwise leave as is
),
//list of list of records --> list of records
CombineRecords = List.Combine( ParseJson ),
//uniform transformation - Table.FromRecords is designed for list of records
ToTable =
Table.FromRecords(
CombineRecords,
//provide exact types here so we don't have to later
type table [
datasourceType=text,
connectionDetails=[server=text,database=text],
datasourceId=text,
gatewayId=text
]
),
ExpandConnectionDetails =
Table.ExpandRecordColumn(
ToTable,
"connectionDetails",
{"server", "database"},
{"connectionDetails.server", "connectionDetails.database"}
)
in
ExpandConnectionDetails
Pics of steps so you can see better what's going on:
Here is a way to check for record vs. list as part of your parsing. As stated previously, you can simply wrap the records you find into a list and then treat all outputs the same.
let
Source = Folder.Files("<local file location with your json examples saved>"),
//parse the json, if a single record, wrap in list so they are all list of records
ParseJson =
List.Transform(
Source[Content] , //binaries are in "Content" column
each let
json = Json.Document( _ ),
valTypeIsRecord = Type.Is( Value.Type( json ), type record )
in
if valTypeIsRecord //if it's a record
then { json } //wrap in a list
else json //otherwise leave as is
),
//list of list of records --> list of records
CombineRecords = List.Combine( ParseJson ),
//uniform transformation - Table.FromRecords is designed for list of records
ToTable =
Table.FromRecords(
CombineRecords,
//provide exact types here so we don't have to later
type table [
datasourceType=text,
connectionDetails=[server=text,database=text],
datasourceId=text,
gatewayId=text
]
),
ExpandConnectionDetails =
Table.ExpandRecordColumn(
ToTable,
"connectionDetails",
{"server", "database"},
{"connectionDetails.server", "connectionDetails.database"}
)
in
ExpandConnectionDetails
Pics of steps so you can see better what's going on:
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.
Fabric Community Update - February 2025
Find out what's new and trending in the Fabric community.
