Check for empty table before expandRecordColumn

[bartus1]

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

---

[MarcelBeug]

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"

[bartus1]

Empty table for some ids.  Nothing to expand then.  Thanks.

[bartus1]

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...

[MarcelBeug · Accepted solution]

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"