Forum Discussion
Check for empty table before expandRecordColumn
It was not clear that you had an empty table, I understood you had empty nested records in Column1,
In any case, you can use function Table.IsEmpty to check if the table is empty.
Another wild shot: if the table is empty and you don't have any columns, you can still create a table with Column1 with the record structure you would have if there would be a record, but without data. This will still let you expand Column1 and end up with the 2 columns, but without data.
Just to illustrate how it works: the code below has 2 Source steps, of which 1 must be commented (currently the first line).
The first will create a table with 1 row; the second will create an empty table.
let // Source = #table(1,{{[EventItemId = 1, EventItemDescription = "Description"]}}), Source = Table.FromList({}), CheckEmpty = if Table.IsEmpty(Source) then #table(type table[Column1 = [EventItemId = any, EventItemDescription = any]],{}) else Source, #"Expanded Column1" = Table.ExpandRecordColumn(CheckEmpty, "Column1", {"EventItemId", "EventItemDescription"}, {"EventItemId", "EventItemDescription"}) in #"Expanded Column1"
You can remove rows with null in Column1 before expansion.
Alternatively you can replace nulls with an empty record before expansion.
I created some code to illustrate how that would look like:
let
Source = #table(1,{{[EventItemId = 1, EventItemDescription = "Description"]},{null}}),
NulRecord = Table.TransformColumns(Source,{{"Column1", each if _ = null then [] else _}}),
#"Expanded Column1" = Table.ExpandRecordColumn(NulRecord, "Column1", {"EventItemId", "EventItemDescription"}, {"EventItemId", "EventItemDescription"})
in
#"Expanded Column1"Thank you kindly.
"You can remove rows with null in Column1 before expansion." - Well, I don't think I have Column1 at all when I get empty record.
Non empty record does return table which gets expanded. My issue happens when empty record gets returned, so no Column1 exists to be expanded.
Sorry if I am lagging behind here...
It was not clear that you had an empty table, I understood you had empty nested records in Column1,
In any case, you can use function Table.IsEmpty to check if the table is empty.
Another wild shot: if the table is empty and you don't have any columns, you can still create a table with Column1 with the record structure you would have if there would be a record, but without data. This will still let you expand Column1 and end up with the 2 columns, but without data.
Just to illustrate how it works: the code below has 2 Source steps, of which 1 must be commented (currently the first line).
The first will create a table with 1 row; the second will create an empty table.
let // Source = #table(1,{{[EventItemId = 1, EventItemDescription = "Description"]}}), Source = Table.FromList({}), CheckEmpty = if Table.IsEmpty(Source) then #table(type table[Column1 = [EventItemId = any, EventItemDescription = any]],{}) else Source, #"Expanded Column1" = Table.ExpandRecordColumn(CheckEmpty, "Column1", {"EventItemId", "EventItemDescription"}, {"EventItemId", "EventItemDescription"}) in #"Expanded Column1"
Empty table for some ids. Nothing to expand then. Thanks.
