Forum Discussion
Check for empty table before expandRecordColumn
Greetings,
My function gets a list of records per pEventID. All works fine when a record is not empty, so it can expand the table - #"Expanded Column1"
Now, when this hits an empty record, I get an error - The column 'Column1' of the table wasn't found, which makes sense.
How do I prevent it and specify if record/table is empty, create EventsTable of null values???
Thanks.
Funciton below:
---
let getEventItems = (pEventID as text) =>
let
Source = Json.Document(Web.Contents("...link/" & pEventID & "/GetItems")),
#"EventItemsTable" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
#"Expanded Column1" = Table.ExpandRecordColumn(#"EventItemsTable", "Column1", {"EventItemId", "EventItemTitle"}, {"EventItemId", "EventItemTitle"})
in
#"Expanded Column1"
in getEventItems
---
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"
4 Replies
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.
