Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bartus1
Helper I
Helper I

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

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)

View solution in original post

4 REPLIES 4
MarcelBeug
Community Champion
Community Champion

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)

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

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"

 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.