cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bartus1 Regular Visitor
Regular Visitor

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

---

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 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"

 

Specializing in Power Query Formula Language (M)
4 REPLIES 4
Super User
Super User

Re: Check for empty table before expandRecordColumn

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"
Specializing in Power Query Formula Language (M)
bartus1 Regular Visitor
Regular Visitor

Re: Check for empty table before expandRecordColumn

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

Super User
Super User

Re: 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"

 

Specializing in Power Query Formula Language (M)
bartus1 Regular Visitor
Regular Visitor

Re: Check for empty table before expandRecordColumn

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